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?
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.
