Provide a cold cache when starting a request

Question

Is there a hint that I can use to ensure that when running a particular query, the optimizer will not use a cached query plan?

I found the MSDN page to force the use of specific plans - but I need the opposite. I tried to add 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 hint to force a new plan to be created at each execution. It will 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 at the stored procedure level, you can use WITH RECOMPILE :

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

If you want to cancel recompiling the stored procedure (i.e. just not use the cached plan the next time you start), you can use SP_RECOMPILE :

 EXECUTE sp_recompile 'dbo.ProcedureName'; 

---

I did not know about the complications that Martin Smith was talking about, I tried to recreate them, but I could not (although I have no doubt that his second doubts, I just think that my test scenario is too simple), but I thought I would add the results anyway.

I created this circuit:

 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); 

By deliberately weighing the table to choose where column1 = 1 , you should use clustered index scanning, but in all other conditions, a non-clustered index should be used. Control Case:

 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; 

This gave two identical plans:

The following 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 bookmark search for @ID = 2, which is a more efficient plan for extracting one row.

NB if I performed with @ID = 2 first without recompiling, both plans would still be the same, but both would use the key search shown above for @ID = 2

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

 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 (for example, control register) 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 search plan is used for @ID = 2;

So, if OPTION (RECOMPILE) does not work as expected, you can use the query text plan descriptor to clear the cache for this particular query.