SQL SErver Query Plans
-
Hello,
I am having an issue where I am getting multiple(two) plans for the same Stored Procedure.
I dont know the reason for the duplication. I can manually re-create it, but I was wondering if there is a way to extract and compare the two plans?
Thank you,
Mishka
SELECT
*
FROM sys.dm_exec_cached_plans as cache_plan
outer apply sys.dm_exec_sql_text (cache_plan.plan_handle) as sql_text
-
Cross apply to sys.dm_exec_query_plan to get the XML. Could be set options were different.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks, that worked.
What is the easiest way to compare between to plans created for the same object? Looking at them side by side they look the same.
Thanks,
Mishka
-
Save as xml, open in a good text editor and compare?
p.s. the set options won't be in the plan. Check the sys.dm_exec_plan_attributes DMV
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
mishka-723908 (8/6/2010)
Hello,I am having an issue where I am getting multiple(two) plans for the same Stored Procedure.
I dont know the reason for the duplication. I can manually re-create it, but I was wondering if there is a way to extract and compare the two plans?
So far as multiple plan for a same stored procedure is concerned it is very much possible that sql server database engine might produce and cache two compiled plans for a same stored procedure.
one for serial processing where it uses a single thread to execute the procedure (typically where MAXDOP is set to 1) and the other one is used for parellel processing of the same stored procedure.
I don't think it should be a problem as far as the performance of your stored procedure is concerned.
please go through the below link for more details of query plan and execution context.
http://technet.microsoft.com/en-us/library/cc966425.aspx
Regards,
Sachin Sharma
-
Thank you for the replies.
A few months ago I had an issue with the cache getting loaded by adhoc junk due to several badly designed systems.
I created a job that would delete all the adhoc plans on a regular basis, but now that is not helping anymore.
There are a few procedures\functions that still need to be worked on, but it does not make sense to me because when I review all the cached plans there are a lot less then it used to be in the past and also they take up less space as a whole.
True I am seeing some of these duplicated plans, but performance is bad for everything else as well.
any ideas will help.
Thank you,
mishka
-
You can also get multiple copies if the sproc does conditional logic within. Depending on the branch taken, different plans result.
-
JRoughgarden (8/12/2010)
You can also get multiple copies if the sproc does conditional logic within. Depending on the branch taken, different plans result.When a plan is compiled and the proc contains conditional logic, all branches are compiled and an exec plan generated for the entire thing. This can be bad, if the branches depend on parameters passed.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Or:
CREATE PROCEDURE ConditionalLogic @Param1 INT, @Param2 INTAS
IF @Param1 IS NOT NULL AND @Param2 IS NULL
SELECT * FROM sys.objects WHERE object_id = @Param1
IF @Param1 IS NULL AND @Param2 IS NOT NULL
SELECT * FROM sys.procedures WHERE object_id = @Param2
IF @Param1 IS NOT NULL AND @Param2 IS NOT NULL
SELECT * FROM sys.indexes WHERE object_id = @Param1 AND index_id = @Param2
GO
EXEC ConditionalLogic 442484655, NULL
EXEC ConditionalLogic NULL, 699149536
EXEC ConditionalLogic 450100644, 1
One plan, used 3 times
If you look at the plan pulled from cache (also attached) all three branches are part of the one plan.
Just looking at the actual execution plan can lead to this conclusion, as it only shows the plan of things that were run. For seeing what really is cached and what's not, it's best to extract the plan from the plan cache and examine that.
(More blog posts I need to write...)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail,
What you wrote was my prior understanding. I opened a case with MS which went on for weeks. Our procedure cache was being filled with different plans for the same sproc. Some were clearly different in size if nothing else. I had heard the cases for which this was supposed to occur (different set options, schema-less references, serial/parallel variants) and had run them all down as not being sources. Anyway finally the MS responder told me that it was due to conditional logic causing different temp tables to be created and different dynamic sql to be generated and executed within the sproc. (I didn't write this stuff; I inherited it.) The 2000 line sprocs are so complex that it certainly does not surprise me that SQL generates different plans.
-
JRoughgarden (8/13/2010)
Anyway finally the MS responder told me that it was due to conditional logic causing different temp tables to be created and different dynamic sql to be generated and executed within the sproc.That still doesn't generally cause multiple plans for a procedure. Maybe there was something strange going on with the large stored procs, but it's not normal behaviour.
Dynamic SQL is treated as ad-hoc code and gets a separate plan, not as part of the procedure's plan.
Again, a simple example:
CREATE PROCEDURE ConditionalLogic2 (@Param1 INT, @Param2 INT)AS
SELECT * FROM sys.procedures WHERE is_schema_published = 1
IF @Param1 IS NULL
BEGIN
CREATE TABLE #Temp1 (
ID INT,
SomeName VARCHAR(20)
)
INSERT INTO #Temp1 (ID, SomeName)
SELECT index_id, LEFT(NAME,10) FROM sys.indexes WHERE index_id>1
SELECT * FROM #Temp1 WHERE id = @Param1
END
IF @Param2 IS NULL
BEGIN
CREATE TABLE #Temp2 (
ID INT,
dt DATETIME,
ArbStr VARCHAR(20)
)
INSERT INTO #Temp2 (ID, dt, ArbStr)
SELECT object_id, create_date, LEFT(NAME,20) FROM sys.tables WHERE modify_date > '2010/06/01'
SELECT * FROM #Temp2 WHERE id = @Param2
EXEC ('Select count(*) FROM #Temp2')
END
GO
DBCC FREEPROCCACHE
GO
EXEC ConditionalLogic2 @Param1 = 2, @Param2 = NULL
GO
EXEC ConditionalLogic2 @Param1 = NULL, @Param2 = 629577281
GO
SELECT st.text, usecounts, qp.objectid
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE text NOT LIKE '%sys.dm_exec_cached_plans%'
Two entries in the cache, one for the proc with two uses, one for the ad-hoc SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you for the replys and the links.
The server that is slow has about 0.76 GB and 5000 plans in the cache. I have another system 1ith over 120000 plans and 3,5 GB in the cache. the latter performs better.
Any ideas?
thank you
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply