☰

数据库上的 MSSQL 速度很慢（~2TB）-索引/碎片？

tag-icon • tag-icon sql-server indexes
数据库上的 MSSQL 速度很慢（~2TB）-索引/碎片？

我有一个相当大的 MS SQL 数据库 (~2TB)。大多数数据都在一个表中 (~60 亿行)。

两周前，我删除了大型表上的两个非聚集索引，并将数据迁移到单个 6TB RAID SSD 阵列上。然后我重新创建了这两个索引，这花了相当长的时间（假设因为我目前拥有数据（用于表和索引）和日志在同一个阵列上，而且似乎使用 RAID 我无法同时实现快速顺序和随机读写）。

无论如何，在重新创建索引后，它运行得很好，大约一个星期。在这一周里，我一直在慢慢地清理大表，只是删除旧的不需要的行。到目前为止，我已经从 60 亿中删除了大约 3 亿，我猜我还有很多事情要做。

现在，经过大约一周的运行，它现在运行得非常慢，我不知道最好的办法是什么。

现在的情况：

  • 双 Xeon
  • 192GB 内存
  • 带有 SQL Server 2012 的 Windows Server 2012
  • CPU 使用率达到 100%（16 核）——在速度变慢之前仅使用了约 50%
  • IO 似乎没有工作太辛苦（没有队列）

大表目前有（在此之前我没有任何碎片信息）：

  • 1x 聚集索引：48% 碎片
  • 1x 非聚集索引：36% 碎片
  • 1x 非聚集索引：10% 碎片
  • 我以前在这个表上还有两个索引，但是不久前就删除了它们

你认为什么可以最好地解决我的问题

  • 在同一数组上重建非聚集索引（假设这应该可以解决问题，但像以前一样需要很长时间。由于我仍在清理表格，因此在不久的将来可能会出现同样的问题）
  • 在新的 RAID 阵列上重建非聚集索引（应该按上述方法修复，但可能更快）
  • 将非聚集索引移动到新的 RAID 阵列（最快的选项）
  • 在新的 RAID 阵列上重新创建两个旧索引（不确定这是否可以减轻 CPU 或 IO 压力）

碎片索引是否会导致更高的 CPU 使用率？

还有什么是我可能遗漏的吗？

短暂性脑缺血发作

答案1

根据索引中的碎片量，您应该继续重建它们。任何碎片化程度超过 30% 的索引都应重建。我还会确保您定期更新表上的统计信息（重建索引将自动为您执行此操作）。

如果你这样做了仍然看到 CPU 使用率真的很高，并且您已确认它是 SQLServr.exe 进程，那么您将需要缩小哪些查询使用了如此多的 CPU，并从那里对它们进行故障排除。

您可以运行类似以下查询的程序来获取有关使用最多 CPU 的查询的一些汇总数据：

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
total_logical_writes as [Total Writes],
total_logical_reads as [Total Reads],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

对于实时数据，您还可以运行如下操作：

SELECT er.session_id, er.cpu_time, er.reads, er.writes, 
SUBSTRING(st.text, (er.statement_start_offset/2)+1, 
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
WHERE session_id > 50
AND status = 'runnable'
ORDER BY cpu_time desc

您还可以同时运行两者，以比较实时数据和汇总数据。两者都应该让您了解是什么占用了这么多 CPU。从那里，您会想找出它们运行这么长时间的原因。它们是在执行大量读取还是大量写入？如果它们执行大量读取，则可能意味着它们缺少一些索引。大量写入可能意味着索引实际上是问题所在。

无论如何，关注这些声明可以为您提供一个起点。

相关内容