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

最新推荐文章于 2022-06-07 14:22:18 发布

you_tube

最新推荐文章于 2022-06-07 14:22:18 发布

阅读量627

点赞数

分类专栏： SQL Server 文章标签： sql server 性能优化 sql cache 测试 insert

本文链接：https://blog.youkuaiyun.com/you_tube/article/details/4388406

版权

SQL Server 专栏收录该内容

60 篇文章

订阅专栏

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》