在发现我的 SQL Server 上几乎所有数据库的碎片都超过 40% 后,我决定使用 80 的填充因子对所有表进行索引重建。
重建所有索引后,某些查询似乎永远需要至少两个查询/表。
这是其中一个缓慢的查询:
SELECT a.FileID,
a.EventID,
MAX(b.cyNumber) AS cyNumber,
MAX(b.skNumber) AS skNumber,
MAX(b.cyFormat) AS cyFormat,
MAX(b.Cost) AS Cost,
MAX(b.PackageRef) AS PackageRef,
MAX(CASE WHEN b.BMUpdatedON = '1900-01-01 00:00:00.000' THEN NULL ELSE b.BMUpdatedON END) AS BMUpdatedON,
MAX(b.RunID) AS RunID
FROM DB.dbo.[File] a
INNER JOIN DB.dbo.bicy b ON a.InnerFileID = b.InnerFileID
WHERE a.FileID NOT IN (SELECT FileID FROM DB.dbo.Event_bicy)
GROUP BY a.FileID,
a.EventID
OPTION (MAXDOP 1);
我总是OPTION (MAXDOP 1)
在所有查询上使用,因为当我不使用它时,查询会运行得非常慢。现在,在索引重建之后,似乎发生了相反的情况。如果我删除MAXDOP 1
或删除where
子句,查询运行速度很快,我觉得这很奇怪。
解决方案:当我创建非聚集索引时DB.dbo.bicy.[InnerFileID]
,查询运行速度很快。
问题:为什么在重建索引之前查询运行良好时,我需要为查询创建一个非聚集索引以快速运行?
碎片本身并不一定是它被制造出来的邪恶恶魔。如果您的数据库文件存储在 SAN 或某些类型的 RAID 阵列或 SSD 上,那么索引碎片整理将几乎没有什么区别,因为无论它们是否碎片化,页面都将分布在整个磁盘上。有关该问题的讨论,请参见此答案: 在 SAN 环境中对 SQL 索引进行碎片整理有什么好处?
重建索引时,不应随意设置
FILL_FACTOR = 80
- 该设置需要逐表评估。假设这些表在FILL_FACTOR = 100
您重建之前,您已经有效地将每个表所需的存储空间增加了 20%。这可能是现在事情变慢的一个很好的部分原因。在所有查询上使用
OPTION (MAXDOP 1)
也是一个坏主意。您应该允许查询引擎决定是否执行并行查询。使用其中 X 是用于并行查询的最大内核数MAXDOP
在服务器级别设置适当的选项。EXEC sp_configure 'max degree of parallelism', X
有关 MAXDOP 的讨论,请参阅此问题: 在 SQL Server 上计算 MAXDOP 的良好、可重复的方法是什么?如果数据库引擎决定使用索引,在一个(或多个)字段上添加非聚集索引肯定会使查询运行得更快。
您可能希望确保
statistics
受影响表上的 是最新的。请参阅http://technet.microsoft.com/en-us/library/ms187348.aspx以获取有关如何执行此操作的联机丛书说明。SQLSkills 也有一篇关于统计的优秀文章,在这里:http ://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/