January 28, 2011 at 2:07 pm
[font="Verdana"]We had witness slow performance like problems if migrated from SQL 80 to 100, while with just simple restore and changing compatibility, rebuild index, update statistics etc ... ++ !
There may be any other alternative, to rather to just simple restore, should try to migrate DATA into new SQL 100 DB on new server, so database objects should be up to mark according to SQL 100 standards!
Please any expertise over this!
Thanks![/font]
January 28, 2011 at 2:39 pm
If you mean SQL 2000 to SQL 2008, it's a backup/restore. You do need to test your app thoroughly, as lots changed.
All the system tables are changed during the restore, as the DB is upgraded to a 2008 DB at that point.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2011 at 4:20 pm
run the upgrade advisor.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
January 30, 2011 at 10:50 pm
GilaMonster (1/28/2011)
If you mean SQL 2000 to SQL 2008, it's a backup/restore. You do need to test your app thoroughly, as lots changed.
[font="Verdana"]
Yes agreed that there may be some/more syntax changes, but the point in question, is just the Performance Impact!
I have feeling that the data tables, which are with just Non-clustered Primary Key constraint, and there are many other non-clustered indexes too on such tables. (i may confuse them as heap)
May be handled less efficiently by the SQL 2008 Engine!
Similarly, Adoc Queries performance is also changed. :rolleyes:
[/font]
January 31, 2011 at 12:44 am
Not in general. There are some cases where queries perform worse on 2008 than on 2000, they're generally a small minority and can often be rewritten. Make sure that you update all stats in the database WITH FULLSCAN after the upgrade.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 1:10 am
GilaMonster (1/31/2011)
Not in general. There are some cases where queries perform worse on 2008 than on 2000, they're generally a small minority and can often be rewritten. Make sure that you update all stats in the database WITH FULLSCAN after the upgrade.
[font="Verdana"]
Thanks Gila,
but "There are some cases where queries perform worse on 2008 than on 2000", i may sense sub-query or co-related sub queries u may be pointing out. but if u can get into some more details of such queries. :w00t:
Secondly, what u suggest if it is devised to manually create statistics on all table columns after migration/up-gradation, and surely to update Statistics with Full Scan, as problem sql 80 DB was set OFF to auto update/create statistics for a long period. 😎
Hows impact on performance ?
[/font]
January 31, 2011 at 1:41 am
Abrar Ahmad_ (1/31/2011)
but "There are some cases where queries perform worse on 2008 than on 2000", i may sense sub-query or co-related sub queries u may be pointing out. but if u can get into some more details of such queries. :w00t:
Sorry, I don't have specific examples. It's not an entire class of queries, just certain specific cases.
Secondly, what u suggest if it is devised to manually create statistics on all table columns after migration/up-gradation, and surely to update Statistics with Full Scan, as problem sql 80 DB was set OFF to auto update/create statistics for a long period. 😎
I don't understand what you're asking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 2:15 am
Secondly, what u suggest if it is devised to manually create statistics on all table columns after migration/up-gradation, and surely to update Statistics with Full Scan, as problem sql 80 DB was set OFF to auto update/create statistics for a long period. 😎
[font="Verdana"]
The old sql 80 DB was not configured to update/create statistics automatically, that's why one solution in mind is to Create Statistics on all table all columns, and SET auto create/update statistics ON on new DB 100!
Do this new huge mess of statistics would help us performance gain, or degrade, as due to again and again recompilation!
Thank you
[/font]
January 31, 2011 at 2:18 am
Abrar Ahmad_ (1/31/2011)
The old sql 80 DB was not configured to update/create statistics automatically, that's why one solution in mind is to Create Statistics on all table all columns, and SET auto create/update statistics ON on new DB 100!Do this new huge mess of statistics would help us performance gain, or degrade, as due to again and again recompilation!
Errr, no, bad idea. How were you managing statistics on SQL 200 if you had auto create and auto update off?
Update all statisics with full scan. Turn auto_create on. Then either turn auto_update on or implement a custom statistics maintenance routine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 2:42 am
Update all statisics with full scan. Turn auto_create on. Then either turn auto_update on or implement a custom statistics maintenance routine.
[font="Verdana"]
1. Do creating statistics on each column is feasible?
2. It seems that auto create and update statistics is always not feasible in sql 100. Although there are significant improvements statistics handling mechanism. and unfortunately, our DB fit in criteria where same happened. and client said i need to rollback to sql 80 again. 😀 (but not serious :hehe:)
Thanks for your valuable comments!
[/font]
January 31, 2011 at 2:57 am
Abrar Ahmad_ (1/31/2011)
1. Do creating statistics on each column is feasible?
No. Turn autocreate on and let SQL decide which columns it wants stats on. There are few good reasons for turning autocreate off. It's more common (but still not the normal situation) to leave autocreate on, turn autoupdate off and manually update stats as necessary. Doing that does mean more work for the admins though.
2. It seems that auto create and update statistics is always not feasible in sql 100.
Why not? There are edge cases where they should be turned off and their job done manually, but they are not the common situation
Although there are significant improvements statistics handling mechanism. and unfortunately, our DB fit in
criteria where same happened. and client said i need to rollback to sql 80 again.
What reason did he give for going back to an 11 year old, unsupported product?
I hope you have a backup from before the upgrade, otherwise going back will be difficult.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 3:14 am
What reason did he give for going back to an 11 year old, unsupported product?
I hope you have a backup from before the upgrade, otherwise going back will be difficult.
[font="Verdana"]
Yes, it was not serious enough thought and was on UAT (testing) environment only.
The reason quoted: "My application was running good enough on sql 80 version and by now; even superb infrastructure upgrade, the response time is down..." :hehe:
Means the same same thing which we carving about statistics is yet the only identified victim for such comments.
Thanks Gila!
[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply