SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reduction Of Performance after migration SQL 2000 to SQL 2008


Reduction Of Performance after migration SQL 2000 to SQL 2008

Author
Message
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
Hello,

we just migrated a SQL 2000 Database to MS SQL 2008 R2 with 80% compatibility.
Unfortunately, this produces massive lost of perfomance.

What information do you need further to help me ?

It's all about one special index:

PK_Produktion_NBE_Posten (clustered)
I have enclosed some screenshots of the properties.

What's striking to me, is the high grade of defragmentation (>58 %)

Any suggestions ???

Please !! Smile

Thank you so much, in advance !

Stephan
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
Here some screenshots...

thank you....
Attachments
nbe_1.jpg (21 views, 88.00 KB)
nb2_2.jpg (16 views, 76.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228501 Visits: 46342
Did you update all statistics with full scan 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
Hehew00t:-P:-D

No words. Wink
Before your optimization (updating statistics): Query Execution Time: >>600 s
After updating statistics: Query Execution Time approx. 65 s

I am thinking about updating statistics regularly now.

What would you recommend for a time table if the query is used very often ?

What about rebuiding indexes or reorganizing indexes ?

Thank you !
You are brilliant. Wink
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228501 Visits: 46342
StephenNL (6/12/2014)
I am thinking about updating statistics regularly now.


Don't just think about it. :-D

The reason it helped here is that the 2005+ statistics have a different structure than the 2000 ones. While the newer optimiser can use the older stats, it doesn't do so efficiently, so updating all stats is near-essential when upgrading from 2000.

What would you recommend for a time table if the query is used very often ?


A what?

What about rebuiding indexes or reorganizing indexes ?


I think it's a good idea.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Rudyx - the Doctor
Rudyx - the Doctor
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11114 Visits: 2503
We have performed a multitude of upgrades from SQL 2000 to SQL 2008 R2 (and still habe many more instances to go).

The issue you are experiencing performance wise we uncovered in testing upgrades. This type of performance degredation was uncovered in less than 5% of our upgrades.

Our solution was to drop all 'system generated' statistics right after the upgrade prior to:
- 'update statistics' (with full scan)
- defragment all non-clustered indexes
- defragment all clustered indexes

and we added:
- 'refresh' all views (basically sp_refreshview for every view in the database)

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
Basically, updating the statistics was a pretty good idea.

The execution time of a certain query could be reduced from more than 10 mins down to 1 minute.

For some reason, I don't know and still try to figure out, our good old server with just 1 processor 8 GB RAM performed better than our SQL 2008R2 with 16 GB RAM and 4 processors. Maybe, is results from the fact that it is running on a virtual machine (Hyper V).

Another reason maybe that the SQL Server is running with 80% compatibility.

I will be back soon and keep you updated.

Thank you all for your very informative tips and hints.

When the migration finally succeeds, I am going drinking a beer or a soda. Wink
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96465 Visits: 38981
StephenNL (6/14/2014)
...

Another reason maybe that the SQL Server is running with 80% compatibility.

...



Do you mean that you are still running in SQL Server 2000 Compatibility mode (Compatibility Mode 80)?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
@Lynn Yes Smile
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
SadSorry, again... I have been looking forward too early. I am now up to a certain point...
Did the following:

Copying the original database for test purposes, upgraded it to 90% comp....
Basically I am now able to simulate productive mode.

I did what GilaMonster said:
After updating statistics and defragmentating indexes, I ran the same script again, and again whithout no significant success.

How is it possible that the same script has better perfomances running under SQL 2000 ????

The goal is to achieve a query time less than 30 seconds.
Within the productive database, the query time is about at 3-5 mins.

BTW:
Refreshing views does not make much sense, because the certain script does not refer to one.

Any other idea ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search