SQLServer性能调优之执行计划（ExecutionPlan）调优 - 数据库编程

理，那么矮子里拔高个，使用 SET STATISTICS IO ON 来比较一下：

SET STATISTICS IO ON
GO

SELECT
  ModifiedDate,
  SalesOrderID,
  SalesOrderDetailID,
  ProductID,
  UnitPrice
FROM SalesOrdDetailDemo
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO

SELECT
  ModifiedDate,
  SalesOrderID,
  SalesOrderDetailID,
  ProductID,
  UnitPrice
FROM SalesOrdDetailDemo WITH(INDEX=idx_SalesDetail_SalesOrderlID)
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO

SELECT
  ModifiedDate,
  SalesOrderID,
  SalesOrderDetailID,
  ProductID,
  UnitPrice
FROM SalesOrdDetailDemo WITH(INDEX=idx_non_clust_SalesOrdDetailDemo_ModifiedDate)
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO

比较下来，采用了 clustered index 的查询表现最差，另外 SET STATISTICS IO 输出的数据中clustered index 的查询在 logical reads 上花费了更多的时间。

看起来采用 non-clustered index + Key Lookup 执行计划表现还不错，但如果能回避 Key Lookup 就完美了，我们来把 non-clustered index 修改一下，用 INCLUDE 关键字在索引中包含其他的字段：

DROP INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo
GO
CREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)
INCLUDE
(
  ProductID,
  UnitPrice
)
GO

-- 清下缓存，仅用于开发环境！
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

再次执行之前的查询：

SELECT
  ModifiedDate,
  SalesOrderID,
  SalesOrderDetailID,
  ProductID,
  UnitPrice
FROM SalesOrdDetailDemo
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO

这下完美了，因为我们的查询字段都包含在索引中，所以执行计划最终被优化为 Index Seek。

SQLServer性能调优之执行计划（ExecutionPlan）调优(二)