Ensure cold cache when running query

Question

Is there a hint I can use to ensure that when I run a specific query the optimizer will not use a cached query plan?

I have found the MSDN page for forcing the use of specific plans - but I require the opposite. I tried adding the following hint:

OPTION (USE NO PLAN);

Or will I need to actually clear parts of the cache?

Answer 1

For individual queries you can use the OPTION RECOMPILE query hint to force a new plan with each execution. It would be something like:

SELECT  T.Column1, T2.Column2
FROM    T
        INNER JOIN T2
            ON T.ID = T2.ID
WHERE   T.Column2 = @SomeParameter
OPTION (RECOMPILE);

Or on a stored procedure level you can use WITH RECOMPILE:

CREATE PROCEDURE dbo.TestRecompile @Param INT
WITH RECOMPILE
AS
    SELECT  *
    FROM    dbo.T;

If as a one off you want to mark a stored procedure from recompilation (i.e. just not use a cached plan the next time it is run) you can use SP_RECOMPILE:

EXECUTE sp_recompile 'dbo.ProcedureName';

---

I was not aware of the complications Martin Smith mentioned, I tried to recreate them but couldn't (although I don't for a second doubt him, I just think my testing scenario was too simple), but, I thought I'd add the results anyway.

I created this schema:

IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL
        DROP TABLE dbo.T;
GO
CREATE TABLE dbo.T 
(   ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
    Column1 INT NOT NULL, 
    Column2 INT NULL
);
INSERT dbo.T (Column1, Column2)
SELECT  TOP 9999 1, Number
FROM    Master..spt_values
UNION ALL
SELECT  TOP 1001 Number, Number
FROM    Master..spt_values
WHERE   Type ='P';

CREATE NONCLUSTERED INDEX IX_T_Column1 ON dbo.T (Column1 ASC);

Deliberately weighting the table so a select where column1 = 1 should use a clustered index scan, but all other conditions should use the nonclustered index. The control case was:

DBCC FREEPROCCACHE;
DECLARE @SQL NVARCHAR(MAX) = 'SELECT COUNT(T.Column2) FROM dbo.T WHERE T.Column1 = @ID';
DECLARE @ParamDef NVARCHAR(MAX) =  N'@ID INT';

EXECUTE SP_EXECUTESQL @SQL, @ParamDef, @ID = 1;
EXECUTE SP_EXECUTESQL @SQL, @ParamDef, @ID = 2;

Which yielded two identical plans:

The next scenario was to add OPTION (RECOMPILE) to the query:

DBCC FREEPROCCACHE;
DECLARE @SQL NVARCHAR(MAX) = '  SELECT  COUNT(T.Column2) 
                                FROM    dbo.T 
                                WHERE   T.Column1 = @ID 
                                OPTION (RECOMPILE);';

DECLARE @ParamDef NVARCHAR(MAX) =  N'@ID INT';

EXECUTE SP_EXECUTESQL @SQL, @ParamDef, @ID = 1;
EXECUTE SP_EXECUTESQL @SQL, @ParamDef, @ID = 2;

This gives the same execution plan as the first two for @ID = 1, but now uses a bookmark lookup for @ID = 2, which is the more efficient plan when retrieving a single row.

N.B. if I had executed with @ID = 2 first with no recompile both plans would still be the same but both would use the key lookup shown above for @ID = 2

Another option instead of OPTION (RECOMPILE) is to clear the cache for a specific query:

DBCC FREEPROCCACHE;
DECLARE @SQL NVARCHAR(MAX) = '  SELECT  COUNT(T.Column2)
                                FROM    dbo.T 
                                WHERE   T.Column1 = @ID';
DECLARE @ParamDef NVARCHAR(MAX) =  N'@ID INT';

EXECUTE SP_EXECUTESQL @SQL, @ParamDef, @ID = 1;
EXECUTE SP_EXECUTESQL @SQL, @ParamDef, @ID = 2;

DECLARE @PlanHandle VARBINARY(64) = 
                    (   SELECT  TOP 1 PLAN_HANDLE
                        FROM    SYS.DM_EXEC_CACHED_PLANS
                                CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST
                        WHERE   ST.TEXT = '(' + @ParamDef + ')' + @SQL
                    );

DBCC FREEPROCCACHE (@PlanHandle);

EXECUTE SP_EXECUTESQL @SQL, @ParamDef, @ID = 2;

Initially (like the control case), the same plan is used for all parameter values, however, you can clear the cache for a specific query definition, once this has been done the key lookup plan is used for @ID = 2;

So, if OPTION (RECOMPILE) is not working as expected then you can use the plan handle of the query text to clear the cache for that specific query.