主页 / dba / 问题 / 241805
Accepted
Triynko
Asked: 2019-07-02 08:54:12 +0800 CST

配置和维护具有许多平均空的插入和删除的表的最佳方法？

  • 772

存在一个“批量查询”表，它有两列（BatchID、RecordID），它们都是 TSQL 类型的“唯一标识符”。唯一的聚簇键在（BatchID，RecordID）上，并且没有二级索引。

我们使用 SqlBulkCopy 将数千或数百万个 id 快速插入到单个批处理 id 下的表中，以便各种查询可以对其进行连接，以使用与该批处理关联的记录 id 集执行过滤。这种方法比尝试在查询字符串中发送所有 id 列表的多个查询要快得多。一旦查询完成，这批记录 id 就会被删除。当没有查询运行时，该表平均为空，但可能有数百个活动批次，因为我们每秒处理数千个可能使用该表的 API 请求。

我的问题是，如果有的话，DBA 需要什么样的维护步骤来维护这些表。例如，它是否会从定期索引重建中受益，或者 SQL Server 是否会自行处理已删除行的清理（释放页面块等）。此外，是否有任何特殊的表或聚集索引配置会有所帮助，特别是考虑到标识符是随机 Guid（唯一标识符）。

一般我们使用最新版本的SQL Server，企业版。有些服务器在 Azure 中，有些在 AWS 中。

我也有兴趣听到替代方案。例如，我假设这种方法最适合永久表，但我也可以创建一个会话本地临时表作为事务的一部分，该表在完成时被删除，而不是从永久表中插入和删除行。我只是不知道相比之下会如何。

sql-server performance

2 个回答

  1. Best Answer

    根据我使用 SQL Server 2016 企业版的经验，在类似的过程中，永久表用作消息队列 - 因此它具有非常高的插入和删除率 - 我们发现每 5 到 10 分钟重建一次索引有利于强制删除记录清理。这极大地提高了表的读取性能。

    在我们的例子中，数据库隔离级别是读取提交的快照，出列/删除过程删除记录的速度比幽灵清理过程清除记录的速度要快。对于高重影记录数，选择一条记录有时需要 1-3 秒而不是 5-10 毫秒，因为 SQL 必须读取所有重影记录，这由高扫描计数（带有set statistics io on）表示。

    检查幽灵记录的数量：

    SELECT version_ghost_record_count, * 
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('[transport].[BatchTable]'), NULL, NULL, 'SAMPLED')

    https://learn.microsoft.com/en-us/sql/relational-databases/ghost-record-cleanup-process-guide?view=sql-server-2017#ghost-record-cleanup-task

    幽灵清理过程会按一定时间间隔自动运行（SQL Server 2012+ 每 5 秒一次，SQL Server 2008/2008R2 每 10 秒一次）并检查是否有任何页面被标记了幽灵记录。如果找到任何内容，它就会删除标记为删除或重影的记录，每次执行最多触及 10 页。

    在具有许多删除的高负载系统上，ghost 清理过程可能会导致性能问题，因为将页面保留在缓冲池中并生成 IO

    我认为“最多 10 页”部分是我们的限制因素，因为高删除率超过了清理过程每次运行可能触及的页面数量。

    • 6

  2. 我建议使用Memory-Optimized Tables。据我了解，您不需要在服务器重新启动时保留内容，因此请在非持久模式下使用。它们通常比临​​时表或表变量快 5-10 倍。-彼得

    • 1

相关问题