How to Tell if Execution Plans are Reused

October 4, 2010 at 8:00 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , , )

I try to watch the search phrases that point people to the blog because sometimes, you get a sense of what problems people are running into. The latest question or phrase I’ve seen a lot lately is along the lines of “how do you know if an execution plan is being reused.”

Since compiling an execution plan can be an extremely expensive operation, it’s worth your time to understand how well a given plan is getting reused. If you’ve seen me present, I’ll frequently talk about the application that had a query with an 86 table join. Recompiles on that thing were frequent and extremely costly. The only good news was, they were recompiles. If we weren’t getting plan reuse it would have been an even worse system than it was.

There are a number of ways you can see if a plan is being reused. One of the easiest is to hit the DMOs that look into the plan cache. You can look at either sys.dm_exec_procedure_stats or sys.dm_exec_query_stats. These DMOs are somewhat different and somewhat the same. Actually, they sort of cross each other. For sys.dm_exec_procedure_stats, you get aggregate information about stored procedures. For sys.dm_exec_query_stats you get aggregate information about queries, which, may be run within stored procedures. So either or both could be useful depending on what you’re attempting to measure. However, both return a very useful counter, execution_count. Quite simply, that tells you that the plan, whether for the statement or the procedure, is being reused. To get maximum gain out using either of these, you’ll want to combine them with other DMOs. Something along these lines can show you the plans for procedures that have been running against a system, ordered by the number of times they’ve been reused:

SELECT deps.execution_count ,
OBJECT_NAME(deps.object_id, deps.database_id) 'Procedure' ,
deqp.query_plan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
ORDER BY deps.execution_count DESC

The only thing wrong with using the DMO like this is that you can only see what’s currently in cache. This means no history, depending on how volatile the cache is on your system.

Another way to tell if a plan is being reused, is to set up a server side trace and capture the event for the SP:CacheMiss or SP:CacheHit. This is pretty straight forward. If the query was not in, it generates a miss event. If it was there, it generates a hit. But, this has to be running in order for you to know if you had a hit or a miss. The good news is, if it’s running, you’ve got historical information since this captures the event as it occurs.

You can also catch recompile events using SQL:StmtRecompile. Yes, you can get SP:Recompile if you’re only ever dealing with procedures, but if you’ve got any kind of ad-hoc querying going on in the system or triggers, you’ll miss recompile events. Basically, because recompiles are at the statement level starting in 2005, BOL recommends only using SQL:StmtRcompile.

That’s pretty much it. Using these methods in the appropriate place will let you know if the plan is being reused or not.

Permalink 1 Comment

Recompiles and Constant Learning

May 11, 2010 at 3:31 pm (SQLServerPedia Syndication, TSQL) (, , )

When faced with a procedure that looks like this:

CREATE PROCEDURE dbo.TestProc (@TestValue INT)
AS
BEGIN
IF @TestValue = 1
BEGIN
SELECT *
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = @TestValue
END
ELSE
BEGIN
SELECT *
FROM Production.Product AS p
JOIN Production.ProductDocument AS pd
ON p.ProductID = pd.ProductID
WHERE p.ProductID = @TestValue
END
END

I used to suggest creating a wrapper procedure in order to avoid the recompiles that occur when the different paths through the IF statement are taken by the optimizer. I mentioned that recently on a post over at SQL Server Central. Gail Shaw (blog | twitter) asked me why I thought there would be a recompile. She said that the optimizer took the query as a whole and created plans for it. I never seem to learn my lesson, so I suggested that she might be wrong about that. Gail being who she is, immediately went and made up a quick little test with simple queries. Sure enough, no recompiles. Ah, but I figured she was benefiting from trivial plans or something, so I created the procedure above to test the theory out. Each query, while relatively simple, goes through a full optimization process, so no trivial plans involved…

Short answer, Gail’s right and I was wrong. I don’t know where I got the idea that this type of query caused recompiles. I have now tested it on 2000, 2005 and 2008, no recompiles anywhere. I also cleared the cache, ran the procedure once, and then checked the cache using this query:

DBCC freeproccache ;

EXEC dbo.TestProc

@TestValue = 1 ;

SELECT deqp.query_plan

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

This is what I saw:

 

I swear, I’d never seen a plan like this before. I guess partly because I tend to only look at the actual execution plan rather than the estimated plan. It’s pretty clear that the optimizer just walked through and determined that there was more than one query involved and built a plan for them, including the conditional IF statement. If I’d just bothered to look at the estimated plan one time, I could have avoided my error of understanding.

To all those I’ve suggested wrapper procs in order to avoid recompiles… oops, sorry.

Permalink 10 Comments