为什么SSMS在分区列上显示Missing Index的消息？
我有一个按列 X 分区的表。但是，SSMS 显示Missing index (Impact 80.23): Create Nonclustered index [] on [dbo].[Table] ([X]) for the following query
select count(*) from table where X = 'xxx'
是否仍然需要在 [dbo].[Table] ([X]) 上创建索引分区列 X？而且在大桌子上密度会很低。
编辑：
我尝试从表中选择 max(x)。与在 X 上有索引的非分区表上运行类似的 SQL 相比，它需要更长的时间。启用show stats IO on后，显示对分区表（X上没有索引）的查询有更多的扫描计数(170)与带索引的非分区表相比，逻辑读取(600K)（扫描计数：1，逻辑读取：4）。
I have a table partitioned by column X. However, SSMS displays Missing index (Impact 80.23): Create Nonclustered index [] on [dbo].[Table] ([X]) for the following query
select count(*) from table where X = 'xxx'
Is it still necessary to create an index on the partitioned column X? And it will be low density on a big table.
Edit:
I tried to select max(x) from table. It takes much longer time than running the similar SQL on a non-partitioned table which has a index on X. After enabled show statistics IO on, it shows that the query on partitioned table (without index on X) has much more scan count(170) and logical reads(600K) than the non-partitioned table with an index (scan count:1, logical reads:4).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是 SSMS 的一项功能建议有利于查询的索引。请注意此处记录的限制。以下是有关索引和分区的一些文档。
SQL Server 使用分区消除来加速对分区对象的查询，仅访问包含结果所需数据的分区。为了进行分区消除，SQL Server 需要知道必须访问多少个分区。在 SQL Server 2005 中，此枚举是使用每个分区上的嵌套循环连接和扫描/查找来完成的。在 SQL Server 2008 中，访问分区表的执行计划几乎与非分区表的执行计划相匹配。
在我的测试中，SQL Server 2005 执行计划显示出在分区列上拥有索引（聚集或非聚集）的好处。 SQL Server 2008 执行计划更改为显示聚集索引查找，但无论分区列是否建立索引，成本都是相同的。但是，这个 分区查找一文讨论了跳过扫描，当分区列和where 子句中引用的列相同时，它可以加快数据检索速度（并降低成本）。
这些文章中的更多信息： SQL Server 2005 分区，SQL Server 2008 分区< /a> 和 SQL Server 2008 分区查找。
This is a feature of SSMS that recommends indexes that would benefit the query. Beware of limitations documented here. Here's some documentation on indexes and partitioning.
SQL Server uses partition elimination to speed up queries against partitioned objects, only accessing partitions containing data needed for the results. In order for partition elimination to occur, SQL Server needs to know how many partitions must be accessed. In SQL Server 2005, this enumeration is done using nested loop joins and scans/seeks on each partition. In SQL Server 2008 the execution plan for accessing a partitioned table will pretty much match that of a non-partitioned table.
In my testing, SQL Server 2005 execution plan showed a benefit from having an index (clustered or nonclustered) on the partitioned column. SQL Server 2008 execution plan changed to show the clustered index seek, but cost was the same whether the partitioned column was indexed or not. However, this partitioning seeks article discusses the skip scan which speeds up (and lowers cost of) data retrieval when the partitioned column and column referenced in the where clause are the same.
More info in these articles: SQL Server 2005 partitioning, SQL Server 2008 partitioning and SQL Server 2008 partitioning seeks.