Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Reduction Of Performance after migration SQL 2000 to SQL 2008 Expand / Collapse
Author
Message
Posted Thursday, June 12, 2014 6:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
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 !! :)

Thank you so much, in advance !

Stephan




Post #1580006
Posted Thursday, June 12, 2014 6:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
Here some screenshots...

thank you....


  Post Attachments 
nbe_1.jpg (13 views, 88.27 KB)
nb2_2.jpg (10 views, 76.64 KB)
Post #1580007
Posted Thursday, June 12, 2014 6:40 AM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Did you update all statistics with full scan after the upgrade?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1580010
Posted Thursday, June 12, 2014 11:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78


No words. ;)
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. ;)
Post #1580204
Posted Thursday, June 12, 2014 1:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
StephenNL (6/12/2014)
I am thinking about updating statistics regularly now.


Don't just think about it.

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 2008, MVP
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

Post #1580247
Posted Friday, June 13, 2014 11:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:26 PM
Points: 3,214, Visits: 2,336
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."
Post #1580672
Posted Saturday, June 14, 2014 4:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
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. ;)
Post #1580813
Posted Saturday, June 14, 2014 11:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:40 PM
Points: 20,687, Visits: 32,295
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)?



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)
Post #1580850
Posted Saturday, June 14, 2014 12:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
@Lynn Yes :)
Post #1580864
Posted Saturday, June 14, 2014 1:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
Sorry, 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 ?
Post #1580865
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse