主页 / dba / 问题 / 129169
Accepted
Aasim Abdullah
Asked: 2016-02-13 23:00:33 +0800 CST

临时数据库争用

  • 772

我们在 SQL Server 2014 SP1 上有一个活动的 OLTP 40GB 数据库。查询速度变慢，等待 IO_Completion，Disk Queue Length 上升到 900，SQL Server 停止响应。我们尝试了什么：

  1. 重新启动实例，一分钟后它开始以同样的方式运行。

  2. 第二次重启后，我们更改了每个 tempdb 数据文件的初始大小（创建了 16 个数据文件），它开始正常工作。

注意：我们正在为中间结果集使用表变量。这些结果集非常小。

它在一个月内发生了两次。每次我手动向数据文件添加一点空间时，它就会开始正常工作。更有趣的是，我们在 SQL Server 2008 R2 和 SQL Server 2012 上的相同设置（相同的硬件、相同的文件夹和文件设置、相同的工作负载）运行良好。

请帮助我们找到一个永久的解决方案。

所有数据文件的初始大小都是相同的 1000MB，Current 每个都是 1500MB。都是一样的。每个自动增长为 100MB。在此之前，我们面临 PFS 和 GAM 页面争用，我们增加到 16 个并解决了问题。跟踪标志 1117 和 1118 均已启用。2 个 NUMA 节点上的 24 个内核。所有数据文件都在同一个卷上。简单的磁盘，没有 SAN。

实例在物理机上。使用表变量的查询和使用散列连接的查询最常产生 IO_Completion 等待。

wBob 的详细回答促使我们进行更详细的搜索。我们之前是怎么错过的：

数据库“tempdb”中文件“templog”的自动增长被用户取消或在 7704 毫秒后超时。使用 ALTER DATABASE 为此文件设置较小的 FILEGROWTH 值或明确设置新的文件大小。

每当发生此类问题时，我们都会在日志中找到这一点。我们正在将 TempDB 移动到单独的快速驱动器。

sql-server sql-server-2014

1 个回答

  1. Best Answer

    我认为您的 tempdb 碎片过多并且服务器 CPU 和磁盘设置不匹配，但让我们收集更多信息：

    问题/需要更多信息

    • 请确认处理器名称和类型（我主要是想确定它是否是 2 x hex-core with HT）。使用系统信息（例如 Windows Server 2012 R2 上的控制面板 > 系统和安全 > 系统）和/或 sysinternals 工具CoreInfo进行确认。
    • 请确认服务器 maxdop（例如EXEC sp_configure 'max degree of parallelism'）。如果 CPU 是六核，则服务器 maxdop 最多应为 6（根据此处），或者可以说在 OLTP 系统上更低。我通常将我的 tempdb 文件与我的服务器 DOP 保持一致，最多 8 个，但我们会继续讨论。
    • 请确认包装盒上的服务器总内存和 SQL Server 内存上限（例如EXEC sp_configure 'max server memory (MB)'）。
    • 请确认盒子上是否正在运行任何其他服务（例如 SSIS、SSAS、SSRS、应用程序、iTunes 等）
    • 请确认为 SQL Server 服务帐户启用了即时文件初始化。（在这里测试它的方法）。
    • 为什么 CPU（强大的 2 节点 NUMA 设置）与一个磁盘（家用 PC）之间存在如此巨大的差异？考虑为 tempdb 添加磁盘、条带化和 SSD（但要避免反应过度 :)。
    • 请为其中一个问题查询添加一个实际的执行计划。如果您愿意，可以使用 SQL Sentry Plan Explorer匿名化。

    • 散列与 OLTP 系统中的表变量连接？这表明缺少对表变量、主表或两者的索引。您是否像这样声明表变量（没有索引）？

      DECLARE @t TABLE ( x INT )

    • 不要吝啬表变量定义，即使它包含的结果集很小。最好为优化器提供尽可能多的信息，以便明确说明可空性、唯一性、索引是否聚集/非聚集，例如

      DECLARE @t TABLE ( x INT PRIMARY KEY )
DECLARE @u TABLE ( x INT PRIMARY KEY NONCLUSTERED, u INT NOT NULL UNIQUE CLUSTERED, z INT NOT NULL UNIQUE, a CHAR(1) NULL ) -- not sure why you would do this but you can
DECLARE @v TABLE ( x INT NOT NULL, y INT NOT NULL, PRIMARY KEY ( x, y ) )   -- multi-column primary key

    • 发布执行计划将有助于对此进行诊断。

    • 根据此处和此处检查防止表变量缓存的代码。我认为动态 SQL 和 proc executed WITH RECOMPILE 是唯一影响表变量的。

      DECLARE @u TABLE ( x INT )

INSERT @u
EXEC('DECLARE @t TABLE ( x INT ); INSERT INTO @t VALUES ( 1 ); SELECT x FROM @t;' )

SELECT *
FROM @u

    • 检查 SQL Server 日志（对象资源管理器 > 管理 > SQL Server 日志）以获取消息，例如 IO 警告。

    • 检查 Windows 事件查看器
    • 自 SP1 以来，已经发布了许多版本。查看自 SP1 以来放置的 CU 修复程序。后续 CU 中可能修复了 SP1 中的错误，例如修复：当估计的行数和行大小正确时，排序运算符会溢出到 SQL Server 2012 或 SQL Server 2014 中的 tempdb https://support.microsoft.com/en-美国/kb/3088480
    • 在应用任何修补程序之前确定这是您的原因，尽管由于新功能（内存中 OLTP、集群列存储）的数量，与 SQL Server 2014 的 CU 保持同步更为重要。
    • 最后，每个核心需要一个 tempdb 文件是一个神话，看看你的磁盘设置，我的猜测是 tempdb 过于分散。我有一种挥之不去的感觉，你有一个磁头，tempdb 有一个文件组，很多文件。

    但是忘记我们认为我们知道的；创建一个重现您的问题的测试装置，并尝试减少临时文件的数量......从 1、2、4、6 等开始收集信息，以做出基于证据的决定。现在这是更难的一点，因为您的问题似乎是间歇性的，您可能无法弄乱您的 tempdb 设置，但这就是我处理这个问题的方式。

    祝你好运。让我们知道您的身体情况如何。

    • 6

相关问题