SQL SErver Query Plans

Question

Post reply

SQL SErver Query Plans

mishka-723908

SSCommitted

Points: 1675
More actions
August 6, 2010 at 1:59 pm

#144598

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mishka-723908 SSCommitted Points: 1675 More actions · Answer 2

mishka-723908

SSCommitted

Points: 1675

August 9, 2010 at 4:05 pm

#1204441

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sachnam SSCrazy Points: 2317 More actions · Answer 4

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

mishka-723908 SSCommitted Points: 1675 More actions · Answer 5

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

JRoughgarden Ten Centuries Points: 1119 More actions · Answer 6

JRoughgarden

Ten Centuries

Points: 1119

August 12, 2010 at 11:28 pm

#1206402

You can also get multiple copies if the sproc does conditional logic within. Depending on the branch taken, different plans result.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

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 INT



AS

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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JRoughgarden Ten Centuries Points: 1119 More actions · Answer 8

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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mishka-723908 SSCommitted Points: 1675 More actions · Answer 10

mishka-723908

SSCommitted

Points: 1675

August 13, 2010 at 3:21 pm

#1206949

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