表分区的阴暗面（执行计划）

CareSon 发表了一片博文http://www.cnblogs.com/CareySon/archive/2012/10/30/2745918.html其实我碰到过类似的事情，但是没有仔细研究为什么。借着CareSon的Demo，仔细的观察了执行计划。

测试数据

　　当然第一步根据CareSon的Demo建立一份测试数据。

　　第二步为了做比较的需要，建一个非分区的非聚集索引，Key 和 分区对齐的非聚集索引一样。

　　第三步建议一个非分区表，数据和分区表一样，我在测试的时候叫Order2

　　第四步在Order2建3个索引，1 聚集索引非分区，2.非聚集索引分区，3非聚集索引非分区

疑问

　　这样测试数据就准备全了。CareSon的博文给我几个问题：1.为啥不走非聚集索引，2.走了索引会怎么样，3如果索引是非分区索引呢，4如果表是非分区表，中的分区索引会怎么样

测试结果

　　根据问题我写了一下几个sql和对应执行计划和结果（性能差距很大所以单看执行计划就能猜出那个性能比较好）：

　　1.使用索引提示，强行走分区索引

SELECT TOP 100 id,OrderDate,DateModified,Placeholder 
FROM dbo.Orders WITH (INDEX = IDX_Data_DateModified_Id_OrderDate)
WHERE DateModified >='20120625'
ORDER BY DateModified,Id

　　

　　

(100 行受影响)
表 'Orders'。扫描计数 4，逻辑读取 286265 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 1514 毫秒，占用时间 = 1132 毫秒。

　　2.在分区表中不走索引（在这里表结构要稍微的变动一下，把非聚集索引删除）

SELECT TOP 100 id,OrderDate,DateModified,Placeholder 
FROM dbo.Orders 
WHERE DateModified >='20120625'                          
ORDER BY DateModified,Id

　　

(100 行受影响)
表 'Orders'。扫描计数 6，逻辑读取 25229 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 266 毫秒，占用时间 = 513 毫秒。

　　3.使用索引提示，强行走非分区索引

SELECT TOP 100 id,OrderDate,DateModified,Placeholder FROM 
dbo.Orders WITH (INDEX = idx_DateModified)
WHERE DateModified >='20120625' 
ORDER BY DateModified,Id

　　

　　

(100 行受影响)
表 'Orders'。扫描计数 1，逻辑读取 318 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 122 毫秒。

　　4.在非分区表中，使用索引提示走分区索引

SELECT TOP 100 id,OrderDate,DateModified,Placeholder 
FROM dbo.Orders2 with (INDEX = IDX_Data2p_DateModified_Id_OrderDate)
WHERE DateModified >='20120625'
ORDER BY DateModified,Id

　　

(100 行受影响)
表 'orders2'。扫描计数 4，逻辑读取 286265 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 1701 毫秒，占用时间 = 1152 毫秒。

　　5.非分区表正常运行

SELECT TOP 100 id,OrderDate,DateModified,Placeholder 
FROM dbo.Orders2 
WHERE DateModified >='20120625'
ORDER BY DateModified,Id

(100 行受影响)
表 'orders2'。扫描计数 1，逻辑读取 318 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 140 毫秒。

解决问题

　　现在结果有了，那么就来解答先前的几个问题1.为啥不走非聚集索引，2.走了索引会怎么样，3如果索引是非分区索引呢，4如果表是非分区表，中的分区索引会怎么样

　　1.为啥不走索引呢？

　　　　1和2 对比一下，1中逻辑读大概是2中的10倍，就算SQL Server 再傻也会用2这个执行计划。

　　2.走了索引会怎么样？

　　　　看1的执行时间和执行计划，走了索引性能很烂

　　3.如果索引是非分区索引呢？

　　　　请对比1和3，2和3性能差距不是一点点，那么就引出另外一个话题，为什么非分区索引性能比分区的好呢？这个下面再讨论

　　4.表如果是非分区表，中的分区索引会怎么样？

　　　　对比4和5非常负责任的告诉你，性能很烂。

总结

　　现在来说说为什么非分区索引性能在这里比分区的好，分区索引是根据分区方案，分别存放，如前面的例子直接使用某个值来过滤，索引查找会扫描所有分区，查询那些数据，那么它读取的页远远大于直接在非聚集索引中seek，自己运行demo，查看非聚集索引seek左边的排序，是根据orderdate，id排序，视乎在为了给嵌套循环做准备，之后还要根据datemodified，id排序。非分区索引严格按照排序的字段排序，因此看执行计划的时候看实际相应行数为100，但是看走分区索引的实际相应行数则为170081也就意味着走非分区索引加大了IO，和多余的计算。恰恰造成查询偏慢的原因就是因为分区的阴暗面。不恰当的使用分区。CareSon的博文让我对分区表和索引对齐有了更加深入的认识。