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»»»

Real World Query Plans Expand / Collapse
Author
Message
Posted Wednesday, June 08, 2005 4:18 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 9:29 AM
Points: 32,793, Visits: 14,945
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
Post #188810
Posted Wednesday, June 08, 2005 4:25 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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?
Post #188812
Posted Wednesday, June 08, 2005 5:37 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, January 31, 2014 7:46 AM
Points: 1,599, 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
Post #188831
Posted Thursday, June 09, 2005 2:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, 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

Post #188913
Posted Thursday, June 09, 2005 2:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 24, 2006 1:08 AM
Points: 110, 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!

Post #188923
Posted Thursday, June 09, 2005 2:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 19, 2007 4:14 AM
Points: 1, 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.

Post #188925
Posted Thursday, June 09, 2005 3:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 3:55 AM
Points: 329, Visits: 102

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

Post #188948
Posted Thursday, June 09, 2005 3:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 05, 2013 2:59 AM
Points: 22, 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

Post #188949
Posted Thursday, June 09, 2005 5:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 19, 2007 6:32 AM
Points: 23, 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
Post #188963
Posted Thursday, June 09, 2005 5:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:08 AM
Points: 2,674, Visits: 695

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/
Post #188976
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse