August 18, 2011 at 3:41 am
Hi folks,
Last weekend I upgraded a key database from SQL2k to SQL2k8R2. There's a view on the DB which is used by a large number of queries and so the performance is important. All the queries that use the view have slowed considerably since the upgrade - they used to be sub 1 second but are now averaging over six seconds.
I've analysed query plans and statistics and there is one glaringly obvious difference - one of the tables is reporting 14k logical reads on the SQL2k database but 554k logical reads on the SQL2k8 database. No other changes were made to the database during the upgrade (schema \ data) although the database now resides on a 64 core \ 64Gb memory \ 4Tb san hardware. Despite that, it's still much slower.
I have rebuilt the indexes and have updated the statistics with fullscan across the board.
I'm sure it's down to the huge increase in logical reads on that one table but why would the engine do such a poor job when it is supposed to be so much better? Am I just unlucky in that the SQL2k8 engine is doing a poor optimisation job on my particular query?
It is pointless posting the query here - the view has 42 joins and many of those are other views (I know - don't ask - it's the worst kind of nightmare).
If anybody could point out some high-level things that I could check out, I would be most grateful! I'm hoping to find a solution that doesn't involve rewriting that nightmare view.
Many thanks,
Martin
August 18, 2011 at 5:08 am
It sounds like you've hit a regression. These are edge cases where a query that, honestly, should have run slow in 2000, didn't. But now, it does. Every edge case I've seen involved a number of poor query writing choices that could have been improved on in 2000, but now will have to be in 2008. Yeah, you're going to have to rewrite. On one or two instances I have seen where running in a compatibility mode makes a difference, but not usually.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 18, 2011 at 9:16 am
I was afraid that might be the case. Many thanks for the reply and the link to those articles, Grant. I better go give the good news to the business now :ermm:
August 18, 2011 at 10:57 pm
Just for grins, drop the view, recreate it as it is, and try again. It frequently doesn't do a bloody thing but it can save a huge amount of headaches if it does. It has worked a couple of times for me.
--Jeff Moden
August 19, 2011 at 4:00 am
Jeff Moden (8/18/2011)
Just for grins, drop the view, recreate it as it is, and try again. It frequently doesn't do a bloody thing but it can save a huge amount of headaches if it does. It has worked a couple of times for me.
Other people may advice praying to almighty IT-Lord... Yeah, try to recreate as adviced by Jeff(it does some time work the trick, but I'm not sure if the effect is permanent). Anyway, I'm totally in agreement with previous posters - your query shouldn't work better in 2000, so you most likely need to rewrite it into something more sensible...
One more thought: try to turn parallelism off and see if this affects it.
EXEC sp_configure 'max degree of parallelism', '1';
RECONFIGURE WITH OVERRIDE;
It's may be the sort of "high-level" setting you are looking, but - in reallity it would be stupid to have it this way, so use only for testing of the behaviour of your view, than turn it back to appropriate one. Lookup sys.configurations before applying change, so you know what is the current settings are.
August 19, 2011 at 5:00 am
Eugene Elutin (8/19/2011)
Jeff Moden (8/18/2011)
Just for grins, drop the view, recreate it as it is, and try again. It frequently doesn't do a bloody thing but it can save a huge amount of headaches if it does. It has worked a couple of times for me.Other people may advice praying to almighty IT-Lord... Yeah, try to recreate as adviced by Jeff(it does some time work the trick, but I'm not sure if the effect is permanent). Anyway, I'm totally in agreement with previous posters - your query shouldn't work better in 2000, so you most likely need to rewrite it into something more sensible...
One more thought: try to turn parallelism off and see if this affects it.
EXEC sp_configure 'max degree of parallelism', '1';
RECONFIGURE WITH OVERRIDE;
It's may be the sort of "high-level" setting you are looking, but - in reallity it would be stupid to have it this way, so use only for testing of the behaviour of your view, than turn it back to appropriate one. Lookup sys.configurations before applying change, so you know what is the current settings are.
I would only turn off parallelism if I saw parallel operations in the execution plan... and then only if I thought it warranted it based on the rest of the behavior, the cost of the plan etc.
There are edge cases where 2000 does run faster for certain queries. But they're almost always problematic code in the first place.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 20, 2011 at 11:50 am
As it was mention that "It is pointless posting the query here - the view has 42 joins and many of those are other views (I know - don't ask - it's the worst kind of nightmare)."
it can be for sure placed to the "problematic code" classification :hehe:
August 20, 2011 at 1:50 pm
As it was mention that "It is pointless posting the query here - the view has 42 joins and many of those are other views (I know - don't ask - it's the worst kind of nightmare).
It is a starting point.
Keep in mind, you don't need to post the full query, fwiw it might just be "select * from BoldView", but in SSMS you can save the graphical query plan (as .sqlplan). That can be posted as attachment and others can view the full plan without reveling "secured" info.
A major enhancement since enterprise manager :w00t:
I've seen great advise being given based on only the execution plan contained info.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 20, 2011 at 3:13 pm
Well, I will definitely try the drop & create suggestion and I will also consider the other points. Right now, I am on holiday in Cornwall for a week but will pick up on this as soon ask I get back to the office.
Thanks for the suggestions so far!
Martin
August 22, 2011 at 8:05 am
One more for when you get back to the office. Another thing you might consider trying (and boy, oh boy, would I test this one to a fare thee well) it to turn on the full effect of the new optimizer. Breaking changes are disabled by default, so not all optimizer tricks are enabled. But you can turn them on with a single trace flag. Test, test, test this.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 22, 2011 at 8:14 am
Grant Fritchey (8/22/2011)
One more for when you get back to the office. Another thing you might consider trying (and boy, oh boy, would I test this one to a fare thee well) it to turn on the full effect of the new optimizer. Breaking changes are disabled by default, so not all optimizer tricks are enabled. But you can turn them on with a single trace flag. Test, test, test this.
Holly crap, I smell a big test of all my reports coming up REAL soon! :-D.
Good thing are cpus rarely go above 10%. Even a screw up would probably go unnoticed! 😉
August 31, 2011 at 8:53 am
I'm back off my hols, and raring to go again.
Out of the three suggestions here, I've tried two of them. Sadly, the 'drop & recreate' idea failed to make any difference - such a shame - that would have been great. Turning off parallelism, which I did using the OPTION hint as opposed to making the global change, only succeeded in making the query around 5 times slower (unsurprisingly; it was using 5 processors).
Grant - despite being the author of 'the scary DBA', your suggestion of turning on the beta query processing engine is just too scary for me. I'm not *quite* that brave enough. Oh, and I'm leaving this job at the end of next week and will need referrals! 😛
So, it does look like this is one of those pesky 'edge cases' where the only way out is to re-factor. And I wish my replacement the very best of luck with that one! :laugh:
August 31, 2011 at 12:01 pm
webtekkie (8/31/2011)
I'm back off my hols, and raring to go again.Out of the three suggestions here, I've tried two of them. Sadly, the 'drop & recreate' idea failed to make any difference - such a shame - that would have been great. Turning off parallelism, which I did using the OPTION hint as opposed to making the global change, only succeeded in making the query around 5 times slower (unsurprisingly; it was using 5 processors).
Grant - despite being the author of 'the scary DBA', your suggestion of turning on the beta query processing engine is just too scary for me. I'm not *quite* that brave enough. Oh, and I'm leaving this job at the end of next week and will need referrals! 😛
So, it does look like this is one of those pesky 'edge cases' where the only way out is to re-factor. And I wish my replacement the very best of luck with that one! :laugh:
BWAA-HAAA!!!! Reminds me of the joke that ends with the punch line "Get 3 envelopes." 😀
--Jeff Moden
September 1, 2011 at 5:15 am
I didn't see anything about patching up this new server. IIRC there have been lots of perf regressions fixed in SP/CUs. Try that first if you can patch. Next is to evaluate the query plans and review estimated/actual rows differences between old and new server. When you find the diffs (if any), manually do the update stats with full scan again. Speaking of which, did you use something like sp_updatestats or did you FORCE EVERY statistic to be updated WITH FULL SCAN??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 1, 2011 at 6:29 am
Definitely agree with Kevin to patch up if it isn't already.
Other than that, there's no "generically make everything run faster" option.
C'mon, let's see the execution plan! 😀
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply