SQL SERVER性能优化--计划缓存和重编译

SQL SERVER将编译过的执行计划放在内存的计划高速缓存区（Plan Cache）中；当SQL SERVER执行时，会先到该高速缓存区寻找是否有对应的计划；

1、计划高速缓存不同方面

Ad-hoc高速缓存

当应用程序在批处理内传递一句以上的UPDATE、DELETE或SELECT等T-SQL语句到SQL SERVER时，称之为“Ad-hoc查询”，SQL SERVER针对这类语句的高速缓存与重用，需要格式完全相同，包括大小写、分行或空白差异；SQL SERVER 2008中已经避免这样情况出现；

测试：

DBCC FREEPROCCACHE；

 

DBCC freeproccache
SELECT * FROM northwind.dbo.customers
SELECT * FROM northwind.dbo.orders
go
SELECT * FROM northwind.dbo.customers
SELECT * FROM northwind.dbo.orders

 

通过系统视图查看已高速缓存的执行计划；

SELECT cacheobjtype,objtype,usecounts,sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
AND sql NOT LIKE '%sql.%'

 

cacheobjtype      objtype              usecounts   sql
----------------- -------------------- ----------- ----------------------------------------------------------------

Compiled Plan    Adhoc    1    select * from northwind.dbo.customers  select * from northwind.dbo.orders

 

可以知道，当前缓存是以Adhoc形式存储的，如果再次执行当前SQL，usecounts会自动增加；

 

2、自动参数化高速缓存

自动参数化高速缓存是针对SQL语句中有WHERE条件子句，搭配常量来限制符合的记录。SQL SERVER当产生执行计划时，将常量部分以变量取代。

测试：

SELECT * FROM northwind.dbo.customers WHERE customerid = 'alfki'
go
SELECT * FROM northwind.dbo.customers WHERE customerid = 'anatr'

SELECT cacheobjtype,objtype,usecounts,sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
AND sql NOT LIKE '%sql.%'

 

cacheobjtype      objtype              usecounts   sql
----------------- -------------------- ----------- ----------------------------------------------------------------

Compiled Plan    Prepared    2    (@1 varchar(8000))SELECT * FROM [northwind].[dbo].[customers] WHERE [customerid]=@1

 

相应地，过段时间再执行，结果如下：

cacheobjtype      objtype              usecounts   sql
----------------- -------------------- ----------- ----------------------------------------------------------------

Compiled Plan    Prepared    3    (@1 varchar(8000))SELECT * FROM [northwind].[dbo].[customers] WHERE [customerid]=@1

 

可以由上面的列USERCOUNTS可以看出，实际查询时，两句语句都用到了相同的参数化执行计划；

 

3、sp_executesql

上面自参数据化高速缓存是针对SQL语句中有WHERE条件并搭配常量，而sp_executesql 系统存储过程自行将语句和参数分开；

测试：

EXEC sp_executesql N'select * from northwind.dbo.customers where customerid = @custid',
N'@custid nvarchar(5)','alfki'
go
EXEC sp_executesql N'select * from northwind.dbo.customers where customerid = @custid',
N'@custid nvarchar(5)','anatr'

SELECT cacheobjtype,objtype,usecounts,sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
AND sql NOT LIKE '%sql.%'

 

cacheobjtype      objtype              usecounts   sql
----------------- -------------------- ----------- ----------------------------------------------------------------

Compiled Plan    Prepared    8    (@custid nvarchar(5))select * from northwind.dbo.customers where customerid = @custid

 

再次执行

cacheobjtype      objtype              usecounts   sql
----------------- -------------------- ----------- ----------------------------------------------------------------

Compiled Plan    Prepared    10    (@custid nvarchar(5))select * from northwind.dbo.customers where customerid = @custid

 

由上可以看出，SQL SERVER针对查询语句的本体建立执行计划，与参数内容无关；

 

4、存储过程

 

建立一个不平均数据范例:

CREATE TABLE t1
(
IDKey INT IDENTITY(1,1) PRIMARY KEY,
key1 INT NOT NULL,
key2 INT NOT NULL,
key3 VARCHAR(15)
)
go
DECLARE @key1 INT,@key2 INT
SET @key1 = 1
WHILE @key1 < 100
BEGIN
    SET @key2 = 1
    WHILE @key2 <= 20
    BEGIN
        INSERT INTO t1(key1,key2,key3)
        VALUES(@key1,@key2,'Date' + CONVERT(VARCHAR,@key1) + ',' + CONVERT(VARCHAR,@key2))
        SET @key2 = @key2 + 1
    END
    SET @key1 = @key1 + 1
END

INSERT INTO t1 VALUES (10000,10000,'10000,10000')
CREATE INDEX idxkey1 ON t1(key1)

 

测试：

exec sp 10000 with recompile

从执行计划可以看出，为聚集索引扫描；

 

exec sp 10000 with recompile

从执行计划可以看出，为聚集索引查找；

 

注：为了避免大批量的数据查询，使用单条数据测试；

 

5、动态SQL

动态SQL使用了先前Ad-hoc查询与自动参数化高速缓存的技巧，高速缓存EXEC 执行动态组织SQL语句所产生的计划；

 

6、观察哪些情形会导致重新编译计划；

 

SELECT
    v.SubClass_Name, V.SubClass_value
FROM
    sys.trace_events e
JOIN
    sys.trace_subclass_values v
    ON e.trace_event_id = v.trace_event_id
WHERE
    e.name = 'sp:Recompile'
    AND v.subClass_Value < 100

 

参考：胡百敬 《SQL SERVER 2005 Performance Tuning》