Tip Regarding Cached Plans

When I need to troubleshoot execution plan reuse behavior, I find the part of identifying the right entries in sys.dm_exec_cached_plans or sys.syscacheobjects problematic. You will find recommendations to first clear the procedure cache, and then run your code, and then query the cache. However, this is of course a bit excessive, and sometimes clearing the procedure cache is not really an option.

Another option is to filter the attribute holding the SQL text in the aforementioned DMVs, looking for something that is unique enough to identify only the plans of interest. This part can be tricky, though, because there may be different queries against the same objects, with very similar sections like the queried objects, attributes, filters and so on.

I found a trick that solved the problem for me when it was acceptable to add a comment to the code. I simply planted a GUID as a comment in the query of interest. Then, when querying the aforementioned DMVs, I simply filtered entries where the attribute holding the SQL text contained that GUID.

As an example, suppose that you want to troubleshoot plan reuse behavior for the query in the following procedure:

USE AdventureWorks;

IF OBJECT_ID('dbo.Proc1', 'P') IS NOT NULL DROP PROC dbo.Proc1;

GO

CREATE PROC dbo.Proc1

  @orderid AS INT

AS

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID

FROM Sales.SalesOrderHeader

WHERE SalesOrderID >= @orderid;

GO

Invoke the NEWID() function manually to obtain a new GUID. Suppose you got the value 6344153D-C3FD-4912-952E-477306EA19C3. Now alter the procedure, planting this GUID as a comment in the query, like so:

ALTER PROC dbo.Proc1

  @orderid AS INT

AS

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID

/* 6344153D-C3FD-4912-952E-477306EA19C3 */

FROM Sales.SalesOrderHeader

WHERE SalesOrderID >= @orderid;

GO

Run the code that is supposed to test the executions of the procedure:

EXEC dbo.Proc1 @orderid = 75119;

EXEC dbo.Proc1 @orderid = 75120;

And when you want to analyze plan reuse behavior in the aforementioned DMVs, look for the specific GUID you planted in the comment, like so:

SELECT objtype, cacheobjtype, usecounts, text

FROM sys.dm_exec_cached_plans AS ECP

  CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST

WHERE EST.text LIKE '%6344153D-C3FD-4912-952E-477306EA19C3%'

  AND EST.text NOT LIKE '%sys%';

For example, I got the following output from this query after executing the procedure twice:

objtype  cacheobjtype   usecounts   text

-------- -------------- ----------- -------------------------------------------------

Proc     Compiled Plan  2           .../* 6344153D-C3FD-4912-952E-477306EA19C3 */ ...

Cheers,

BG