sql - 运行查询时确保冷缓存

Question

是否有提示可以确保当我运行特定查询时优化器不会使用缓存的查询计划？

我找到了 MSDN page强制使用特定计划 - 但我要求相反。我尝试添加以下提示:

选项(不使用计划)；

或者我是否需要实际清除部分缓存？

Answer 1

对于个别查询，您可以使用OPTION RECOMPILE query hint每次执行都强制执行一个新计划。它会是这样的:

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

或者在存储过程级别，您可以使用 WITH RECOMPILE :

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

如果一次性将存储过程标记为免于重新编译(即下次运行时不使用缓存计划)，您可以使用 SP_RECOMPILE :

EXECUTE sp_recompile 'dbo.ProcedureName';

---

我不知道 Martin Smith 提到的并发症，我试图重新创建它们但不能(虽然我没有再怀疑他，我只是觉得我的测试场景太简单了)，但是，我认为无论如何我都会添加结果。

我创建了这个模式:

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

故意对表进行加权，以便 select where column1 = 1 应该使用聚簇索引扫描，但所有其他条件都应该使用非聚簇索引。控制案例是:

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;

这产生了两个相同的计划:

下一个场景是向查询添加OPTION (RECOMPILE):

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;

这给出了与 @ID = 1 的前两个执行计划相同的执行计划，但现在使用 @ID = 2 的书签查找，这是检索单行时更有效的计划。

注意如果我先使用 @ID = 2 执行而不重新编译，那么两个计划仍然相同，但都将使用上面显示的 @ID = 2 键查找

替代 OPTION (RECOMPILE) 的另一个选项是清除特定查询的缓存:

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;

最初(如控制案例)，相同的计划用于所有参数值，但是，您可以清除特定查询定义的缓存，一旦完成，键查找计划用于 @ID = 2 ;

因此，如果 OPTION (RECOMPILE) 没有按预期工作，那么您可以使用查询文本的计划句柄来清除该特定查询的缓存。