December 11, 2007 at 5:26 am
Does any know how I get the execution plan that a stored procedure is using? I can paste the query into QA and get an execution plan but that is not necessarily the same one that the compiled proc is using.
I am having problems (again) with a compiled procedure taking an hour to run when the individual code in QA takes a few seconds.
Thanks
Jez
December 11, 2007 at 5:44 am
Free plan cache with
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
N 56°04'39.16"
E 12°55'05.25"
December 11, 2007 at 5:46 am
Very nice but it's not the answer to the question.
Jez
December 12, 2007 at 12:07 am
Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M
December 12, 2007 at 12:30 am
Paste the call to the proc in QA and run it with the exec plan on. It should give you back the same plan as from whatever app its called from
That said, I have had one case on SQL 2000 of a proc using one plan when called from an ASP page and a different one when called from QA.
If you paste the contents of the proc and call that, it's a different piece of code and gets compiled as ad-hoc SQL.
In SQL 2005 there are ways to query the cache to get the plan that was used, but there's no way to do it on SQL 2000.
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
December 12, 2007 at 2:14 am
Gail,
Yes running the proc in QA would give me the execution plan - would that be the same displaying the estimated execution plan (CTRL L)? Running the proc might take an hour rather than a couple of minutes and I was hoping that there was a way of getting the execution plan without re-running the proc.
Jez
December 12, 2007 at 2:20 am
In theory, and providing there are no temp tables involved, yes.
I know 2005 when you do an estimated plan, fetched the plan from the cache it it's there. I haven't satisfied myself that that is always the case on SQL 2000.
If you've run into a parameter sniffing problem (which it sounds like you may have) the estimated plan won't give you the difference between estimated and actual rows, which may offer some hint as to why there's a problem.
If you do take the estimated plan, also look at the actual plan of the contents of the proc, when run in QA (you said it ran in just a few seconds) and look for differences in the plans.
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
December 12, 2007 at 2:31 am
Thanks.
I suspect I am running into parameter sniffing - I seem to have overcome this by having variables defined within the procedure which are set to the same as the parameters passed into the procedure and using the local procedure variables in queries.
I say seem to have fixed because the revised procedure seems to run fine - but then again so did the original procedure for the first few times and then started taking a long time. The are lots of variables (reboots, restores, freeproccache recompiles of stored proce) so it is difficult to get a precise handle on what has gone on to cause a proc to give this strange performace.
Jez
December 12, 2007 at 2:40 am
As an alternative to the variables, may I suggest creating the proc WITH RECOMPILE so that each execution gets the optimal plan. It will completely eliminate parameter sniffiing.
With variables, while you'll never get a really bad plan, you'll likely also not get a very good one.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply