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


Real World Query Plans


Real World Query Plans

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: Administrators
Points: 143602 Visits: 19424
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/realworldqueryplans.asp

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66057 Visits: 9671
Thanx for the quick reply to our requests about the end of that story .

Has anyone used this technic before of running the proc every X min to ensure the right query plan?
David Scotland-132255
David Scotland-132255
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2199 Visits: 134
I saw a good solution to this problem on Ken henderson blog

http://blogs.msdn.com/khen1234/default.aspx

which suggest a way of ensuring the correct plan is kept and avoids having to run it every minute.

regards

David
Mike Metcalf
Mike  Metcalf
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2099 Visits: 53

A very interesting topic all round, especially for me as it's what I'm currently trying to look at doing on our system.

As they say 'Every little helps' and I for one am glad to add this nugget to my list


Robert van Kooten
Robert van Kooten
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 1

Good article, good to see combined wisdom from an expert and people from MS .

The solution doesn't sound too great, but at least it's a solution!


Dave Moller
Dave Moller
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 1

I've had similar issues to this where data growth on the system caused a previously high performing query to become very slow. On investigation I found that it was doing table scans of some large tables in a situation where it should have been doing nested loops as only a very small number of rows were needed.

To fix it I specified optimizer hints forcing the query to use specific indexes which forced the query plan back to the prefered nested loops. Not a perfect solution as often we don't have time to rewrite or hint our queries to get a specific query plan but it does work.


~nano
~nano
SSC-Addicted
SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)

Group: General Forum Members
Points: 428 Visits: 113

I also had a similar problem at work.

I found out, the Procs were being run with totally unnormal (extreme) parameters at first to test the proc. When finally the proc was used, the query plan was optimized for the extreme parameters and not for the day to day standard things that it normally received.

After the tests were rebuilt, to first execute the procs with *standard* params everything worked fine, except for the odd occasion, where the proc actually receives real extreme data (of course the query plan is then optimized for the normal data and the queries take longer) but as that is once in a blue moon I can live with that. I don't know if running a proc by default with normal params every minute would really help ... if the proc is THAT important it's plan shouldn't be chucked out too often anyway should it???

If the difference in execution is sooo extreme, then I would rather have 2 stored procs - one for extreme data and one for normal, that way each would be optimized as far as can be for the data they receive - of course there would have to be a switch on the front end ... but then again, if it makes the users happy

nano


Sim Lever
Sim Lever
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 11

This drives me mad - previously some stored procs run in 100 ms or less - then suddenly jump to 2000-5000 ms without a by or leave.

We have tried using index hints in the past but data changes over time and the index hints start to slow the system down too. There's got to be a way of forcing the server to use the good plan permanently. We run the same stored proc millions of times a day (real time data collection) to insert data. I am sure it is when the size of the tables reaches a threshold that SQl decides to change the plan.

If you can identify which tables are the main culprits - I would run an update stats task (autoupdate doesn't hack it for me) - I also run an index rebuild on some of the smaller tables which are mainly varchars to bring them back in line. This improves things but SQl still manages to throw a curve ball every month or so.

Sim

Manchester UK




Cheers,

Sim Lever

Greg Walker
Greg Walker
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 20

I've run into this same problem many times, myself.

Remember: the plan that gets cached is the result of the first call to the stored proc. In our system, we have both large and small customers (data wise). If a smaller customer is the first one to call the proc, it will be optimized to handle the smaller data returns, which trashes performance for the larger customers.

I've seen procs that run in <1 sec jump to 8 secs if the plan is a bad one, but query analyzer will still run it in 1 sec. Not sure why you end up with 2 different plans based on login, but somehow it does.

In our case, I just added the WITH RECOMPILE option to the stored proc in question (only 1 was really causing this problem), and it has since prevented it from showing back up again.

IMHO, the smaller hit for the recompile based on the current parameters is much less than relying on the initial call to generate the best plan for both large and small data sets.

Just my $0.02 input on this sometimes very annoying issue.




Greg Walker
DBA, ExpenseWatch.com
colin.Leversuch-Roberts
colin.Leversuch-Roberts
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: 11855 Visits: 715

I have a similar problem with a simple 3 table join in a proc which table scans ( to return 1 row ) but if the query is put in QA uses the indexes 12k i/o vs 12 i/o . I did loads to the proc and it refused to use the indexes, in the end I dropped the proc and put it back and everthing worked fine - as I say this is a simple query taking 2 int params to return a single row result.

I did all the stuff with the stats including 100% sampling but to no avail - strange!



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
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