SQL SERVER 2008 开发系列（二）

计划指南应用

本章节目的：了解SQL执行计划；

我们都知道SQL SERVER中的存储过程性能比较好，原因是存储过程进行编译，SQL SERVER 保存了存储过程的执行计划； 而一般的T-SQL查询语句是没有预定义的执行计划，因此，T-SQL执行前，数据库引擎需要先生成执行计划，然后优化执行计划，最后执行T-SQL语句；

--如果数据分布均匀，则以不同的参数调用相同的存储过程，SQL Server直接取出旧有的执行计划
--如果数据分布不均匀，则不同的参数并不一定都会使用相同的执行计划

USE Blog
GO

--要比较执行后的计划，看 SQL Server 真实用什么执行计划，
--事前比统计信息时，执行计划会都相同

SET NOCOUNT ON

--创建执行计划测试表
Create Table ExecutePlan
(
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 ExecutePlan ( Key1, key2, Key3)
        Values(@Key1, @key2, 'Data '+Convert(varchar, @Key1) +', '+Convert(varchar, @Key2) )
        Set @Key2 =@Key2+1
    END
    Set @key1= @Key1+1
END

select * from ExecutePlan

--插入远在一般数据范围之外的少数记录
INSERT ExecutePlan VALUES(10000,10000,'10000,10000')

-- 建立符合查询的索引
Create Index idxKey1 On ExecutePlan(Key1)

--创建存储过程
CREATE PROC sp_querybykey1
@key1 INT =0
AS
SELECT * FROM ExecutePlan WHERE Key1=@key1
GO

--调用存储过程，包括实际的执行计划
--聚集索引扫描比较有效
set statistics io on
EXEC sp_querybykey1 1

--获取的统计信息如下：查询出20条记录

表 'ExecutePlan'。扫描计数 1，逻辑读取 11 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

--执行计划如下图所示，使用的是聚集索引

--SQL Server 存储过程会使用用先前产生的计划，
--但是非聚集索引扫描其实比较有效，怎么办？
EXEC sp_querybykey1 10000

--获取统计信息如下：查询出1条记录

表 'ExecutePlan'。扫描计数 1，逻辑读取 11 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

--执行计划如下图所示，使用的是聚集索引，与参数为1的时候的执行计划完全一样；

--然后此时，执行计划如果能够使用非聚集索引，效果会更好；

--我们可以通过以下系统存储过程来设计存储过程的执行计划；

--要求查询最佳化工具在查询进行编译和最佳化时,针对特定的参数值作最佳化
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT * FROM ExecutePlan WHERE Key1=@key1',
@type = N'OBJECT',
@module_or_batch = N'sp_querybykey1',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@key1= 10000))'

--指定查询计划后,我们在执行以下存储过程

EXEC sp_querybykey1 10000

--从统计信息我们也可以看出来,这时的执行计划明显的逻辑读次数减少

表 'ExecutePlan'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

--执行计划如下图所示:使用了非聚集索引和聚集索引,这就是一个最优的执行计划

总结

SQL SERVER 性能优化时,我们可以将存储过程执行最佳的执行计划,以得到更高的性能;