April 7, 2011 at 10:11 am
Hello All
A few days ago I upgrated my server from 2005 to 2008 R2, I have notice some interesting behaviors, for example the use of tempdb is quite extreme even for simple queries, they tend to consume several gb, when I force the most likely index to the query then everything is fine, for some reason the optimizer us not working fine, Is there anything that I can do to solve this? any idea will be really appreciated
Thanks In Advance
April 7, 2011 at 10:52 am
After the upgrade did you manually update all the statistics? If not, those are slowly being recreated and you should see better performance eventually. If you did that, then it's hard to know what the problem is without seeing the queries and the execution plans. There weren't major and fundamental changes in the optimizer between 2005 and 2008. Did you change to snapshot isolation or anything like that?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2011 at 11:01 am
Hi
I am almost sure that I updated the statistics, just in case I am doing it since a couple hours ago,
I could post a query, but really you won't get many info from them, for example i have a table with about 1.5 billion records, a clustered index by an unique column and a secondary index by other several columns, on the query i am explicitly using the column with the clustered index and none of the columns from the other index and for some reason the optimizer is using the other with a full scan!, I know that i can force the index but really, there should be a limit at how dumb the optimizar can be
I'll let you know if the statistics did the trick
Thanks!
April 7, 2011 at 11:05 am
Well, the optimizer isn't dumb, so it makes me wonder if something in your code is causing the problem. That's usually the issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2011 at 8:46 pm
If updating the statistics doesn't help you might look at installing one of the newer CUs for 2008 R2, I know that we had to go to at least CU4 to get similar performance out of 2008 R2 after we upgraded from 2005. (They are up to CU6.)
Note: You have to enable trace flag 4199 after installing a CU to enable the query optimizer fixes.
April 7, 2011 at 8:47 pm
I think it is intersting that we have recently had a rash, at least 3, people reporting large tempdb usage after upgrading to 2008 R2 from 2005...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply