June 12, 2014 at 6:32 am
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
June 12, 2014 at 6:37 am
Here some screenshots...
thank you....
June 12, 2014 at 6:40 am
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
June 12, 2014 at 11:48 am
:hehe::w00t::-P:-D
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. 😉
June 12, 2014 at 1:48 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2014 at 11:36 am
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)
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 14, 2014 at 4:24 am
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. 😉
June 14, 2014 at 11:12 am
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)?
June 14, 2014 at 1:37 pm
:(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 ?
June 14, 2014 at 2:27 pm
StephenNL (6/14/2014)
:(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 ?
Two things. First, drop the %. Compatibility mode 90 is SQL Server 2005 compatibility.
Second, to really help you we need to see the query, the actual execution plan, and the DDL for the tables and indexes involved.
June 28, 2014 at 10:25 am
Hello,
I made some test series with the combination of the db showing apparently slow query execution times, sql server 2008 r2, windows server 2008 r2 running a vm under hyper v as well as under the host itself.
The database size is approx. 6 GB.
The test-server (host) has 32 GB Ram and a 8 core cpu.
Interestingly the lack of perfomance is appearing only under the virtual machine.
This shows quite impressively that the reason for the bad performance is not the db it self.
June 28, 2014 at 10:56 am
It may have nothing to do with SQL but with how the VM is configured and/or built.
June 28, 2014 at 5:53 pm
Yes, I think so, too...
I guess, this is another topic, isn't it.
July 8, 2014 at 2:12 am
How many cores does your VM have - what's their use %? If it's low (you're over provisioned) you need to drop some cores, overprovisioning VCPUs on a SQL Server VM can be a significant perfrmance hit - ideally you want your VCores to be as few as possible to cope wit the workload so they're busy. Also, is your RAM 'yours'? Are you getting ballooned?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply