主页 / dba / 问题 / 17238
Accepted
MiMo
Asked: 2012-05-01 03:45:05 +0800 CST

SQL Server 2008 中的慢速插入

  • 772

我们产品的数据库包含一个审计跟踪表：

CREATE TABLE gn_AuditTable(
  gn_ObjectId int NULL,
  gn_Action smallint NULL,
  gn_Time datetime NULL,
  gn_UserId int NULL,
  gn_Login varbinary(16) NULL,
  gn_ExtraObjectId int NULL,
  gn_ExtraInfo int NULL
) ON [PRIMARY]

我们用一个简单的语句向这个表添加行insert- 没有存储过程：

insert into gn_AuditTable (
  gn_ObjectId,gn_Time,gn_UserId,gn_Login,gn_Action,gn_ExtraObjectId,gn_ExtraInfo
) values (
  ?,?,?,?,?,?,?
)

将列的值作为参数传递给查询；也没有触发器。

问题是有时这个插入非常慢——在某些情况下需要超过 30 秒并且超时。

我们多次安装了同一产品，我们在其中一些（但不是全部）中遇到了问题。

审计表可能非常大——多达几百万行。非常慢的插入发生在具有大表的数据库（很自然） - 但是我们有其他数据库具有类似数量的运行良好的行，甚至遇到问题的数据库也会间歇性地出现它，在大多数情况下插入相当快（亚秒）。

该表有 6 个非聚集、非唯一索引：

  • gn_Login;

  • gn_ObjectId;

  • gn_Action和gn_ExtraInfo;

  • gn_ExtraObjectId;

  • gn_Time;

  • gn_UserId.

拥有所有这些索引可能会导致问题，但同样，我们到处都有相同的索引，而问题只是在某个地方/有时。

我们在只有一个用户的测试数据库上至少有一次插入超时，因此问题似乎与整体负载无关，而是与插入和表本身有关。

我们曾经有一个聚集索引gn_ObjectId，但我们为了解决这个问题而放弃了它。gn_ObjectId不是顺序的，所以我们认为它对于聚集索引来说是一个糟糕的选择，迫使 SQL Server 在插入时重新排序行。可能gn_Time- 这总是在增加 - 将是一个更好的选择。

没有主键，因为没有“自然”唯一的列或列组合——我们必须添加一个 id 列，这似乎只是开销。

有没有人对我们可以用来诊断问题的方法有什么建议？什么是insert间歇性地花费这么多时间的事情？

sql-server-2008 performance

3 个回答

  1. 为什么没有聚集索引？为什么没有主键？

    这很可能是您的问题：您插入到堆中的表没有任何顺序（例如 IDENTITY 列）

    看

    编辑，更新后。

    拥有非唯一聚集索引需要每个重复的 gn_ObjectId 条目都有一个唯一标识符（DBA.se 链接） 。这不是一个好的聚类键，最好是

    • 数字
    • 单调递增
    • 狭窄

    我还假设您还没有尝试过 IDENTITY 列......？试试看，回来报告。。

    编辑 2，正如@JNK 所说，在这种情况下，ID 列不是开销，因为您的写入将如何在磁盘上进行管理

    • 11

  2. 你的数据库够大吗？您确定您的 INSERT 不会触发自动增长吗？如果您的部署没有启用即时文件初始化，那么这正是触发数据库文件增长时所期望的行为：在文件增长和初始化期间随机阻止写入。您也可能正在经历日志文件增长（这至少可以通过查看Log Growths性能计数器来快速识别）但要发生日志增长，您需要有一个非简单的恢复模型和一个基本上不存在的备份策略（即不会发生日志截断）。

    至于表的设计：时间序列通常按时间值聚集，因为大多数查询都涵盖时间范围（例如，“选择过去 3 天内的所有事件”），因此gn_Time您可能会成为非唯一的聚集键候选者.

    • 6
  3. Best Answer

    我们发现了问题：

    • 有一个维护程序可以从数据库中删除数据，包括来自的行gn_AuditTable；

    • 与delete gn_AuditTable . .其他语句一起处于事务中（坏）

    • 可能会一次性删除很多行——在这种情况下，SQL Server 会锁定整个表——然后insert gn_AuditTable超时等待事务完成

    我们发现这一点是因为我们能够在超时时查看锁，并且我们注意到了表锁。我们正在将delete gn_AuditTable . . .事务移出事务，并可能将其拆分为更小的批次，而不是触发表锁。

    我们正在实现一个聚集索引gn_Time和即时文件初始化 - 两者都独立于这个特定问题 - 谢谢！

    • 1

相关问题