精简版

SQL Server 正在使用 34 GB 的 RAM。但是当查询Memory Consumption Report、缓冲池大小和 ad-hoc 查询大小时，它加起来只有 2 GB 左右。其他 32 GB 的 RAM 在做什么？

先发制人：“您应该限制 SQL Server 可以使用的 RAM 量。” 可以说它的上限为x. 这只是将我的问题变成了“其他xGB 的 RAM 在做什么？”

长版

我有一个消耗 32 GB RAM 的 SQL Server 实例：

那不是 32 GB 的虚拟内存；它实际上消耗了 32 GB 的物理内存（在 RAM 芯片上）——称为“工作集”。

它不像是与其他进程共享的。基本上所有这些都是 SQL Sever 私有的：

• 私有工作集：33,896,700 字节

所有这些 RAM 都在做什么？！

数据库的缓冲池内存使用情况

因此，我们按数据库查询内存使用情况——因为缓冲池缓存了数据库中的页面：

--Memory usage server wide
;WITH src AS
(
    SELECT
        database_id,
        COUNT_BIG(*) AS db_buffer_pages
    FROM sys.dm_os_buffer_descriptors
    --WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
)
SELECT
    CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS [Database Name],
    db_buffer_pages AS BufferPages,
    db_buffer_pages /128.0 AS BufferMB
FROM src
ORDER BY db_buffer_pages DESC
OPTION(RECOMPILE, MAXDOP 1);

总共4.5 MB的 32 GB。

TempDB 使用最多（1.4 MB），其余的从那里开始：

5 MB 的 32 GB - 占不了多少

是的，这可能看起来很低——但这很可能是因为我DBCC DROPCLEANBUFFERS先打电话。

查询计划缓存

接下来我们查询查询计划缓存。所有这些 T-SQL 语句都必须编译成一个巨大的计划，并且这些计划缓存在 RAM 中。

--Server-wide memory usage of plan cache
SELECT
    [cacheobjtype], ObjType,
    COUNT(1) AS Plans,
    SUM(UseCounts) AS UseCounts,
    SUM(CAST(size_in_bytes AS real)) / 1024.0 / 1024 AS [SizeMB]
FROM sys.dm_exec_cached_plans
--where [cacheobjtype] = 'Compiled Plan' and [objtype] in ('Adhoc', 'Prepared')
GROUP BY CacheObjType, ObjType
ORDER BY SizeMB DESC
OPTION(RECOMPILE, MAXDOP 1)

现在我们可以看到有多少内存用于存储各种查询计划：

总共250 KB -远低于丢失的 32 GB。

注意：是的，这可能看起来很低——但这很可能是因为我先打了电话DBCC FREEPROCCACHE。

内存消耗报告

上面的查询向我显示了以下用户使用的 RAM：

• 缓冲池（从磁盘缓存到内存数据库页面）
• 查询计划缓存

这就是全部。但 SQL Server 确实提供了内存消耗报告：

此报告提供有关实例内组件的内存消耗的详细数据

旁白：“没有”

该报告有点难以阅读：

但最终的细分是：

• MEMORYCLERK_SOSNODE：131,832 KB
• MEMORYCLERK_SOSMEMMANAGER：71,464 KB
• USERSTORE_DBMETADATA：67,432 KB
• USERSTORE_SCHEMAMGR：55,784 KB
• MEMORYCLERK_SQLSTORENG：54,280 KB
• MEMORYCLERK_SQLBUFFERPOOL：30,576 KB
• 其他：145,056 KB

总计：556,424 KB → 544 MB

即使我们将其四舍五入到 1 GB：它仍然与 32 GB 相去甚远。

那么内存去哪了？

是的，我可以将 SQL Server 的内存限制为 25 GB。但这只会将我的问题改为：

SQL Server 使用 25 GB RAM 的目的是什么；记忆去哪儿了？

因为这听起来很像我的内存泄漏。

• 服务器：SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

服务器正常运行时间

可以查询服务器正常运行时间（创建 tempdb）：

--Use creation date of tempdb as server start time

SELECT SERVERPROPERTY('SERVERNAME') AS ServerName, create_date AS ServerStartedDate FROM sys.databases WHERE NAME='tempdb';

• 服务器开始日期：2021-12-21 15:46:26.730

CLR 程序集

SELECT * FROM sys.assemblies

链接服务器

select provider, provider_string from sys.servers

我在一台服务器上有一个查询，优化器估计其成本为 0.01。实际上，它最终运行得非常糟糕。

• 它最终执行聚簇索引扫描

注意：您可以在 Stackoverflow 上找到详细的 ddl、sql、表等。但是这些信息虽然很有趣，但在这里并不重要——这是一个不相关的问题。而且这道题连DDL都不需要。

如果我强制使用覆盖索引查找，它估计使用该索引的子树成本为 0.04。

• 聚簇索引扫描：0.01
• 覆盖指数扫描：0.04

因此，服务器选择使用以下计划也就不足为奇了：

• 实际上导致聚集索引的 147,000 次逻辑读取
• 而不是更快的 16 次覆盖索引读取

服务器A：

| Plan                                       | Cost      | I/O Cost    | CPU Cost  |
|--------------------------------------------|-----------|-------------|-----------|
| clustered index scan (optimizer preferred) | 0.0106035 | 116.574     | 5.01949   | Actually run extraordinarily terrible (147k logical reads, 27 seconds)
| covering index seek (force hint)           | 0.048894  |   0.0305324 | 0.0183616 | actually runs very fast (16 logical reads, instant)

这与使用 FULLSCAN 更新的统计数据一样。

在另一台服务器上试试

所以我尝试在另一台服务器上。我得到了相同查询的估计值，其中包含生产数据库的最新副本，以及最新的统计信息（WITH FULLSCAN）。

• 这另一台服务器也是 SQL Server 2014
• 但它正确地意识到聚簇索引扫描是不好的
• 它自然更喜欢覆盖索引查找（因为成本低 5 个数量级！）

服务器乙：

| Plan                                      | Cost        | I/O Cost   | CPU Cost  |
|-------------------------------------------|-------------|------------|-----------|
| Clustered index scan (force hint)         | 115.661     |   110.889  | 4.77115   | Runs extraordinarily terrible as server A (147k logical reads, 27 seconds)
| Covering index seek (optimizer preferred) |   0.0032831 |   0.003125 | 0.0001581 | Runs fast (16 logical reads, near instant)

我不明白的是，为什么这两台服务器的数据库副本几乎相同，都具有最新的统计信息，而且都是 SQL Server 2014：

• 可以如此正确地运行查询
• 另一个摔死了

我知道这似乎是统计数据过时的经典案例。或缓存的执行计划，或参数嗅探。但是这些测试查询都是用 发出的OPTION(RECOMPILE)，例如：

SELECT MIN(RowNumber) FROM Transactions
WITH (index=[IX_Transactions_TransactionDate]) WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

仔细一看，好像“运营商”的估计是错误的

聚簇索引扫描是一件坏事。其中一台服务器知道这一点。这是一个非常昂贵的操作，扫描操作应该告诉我这一点。

如果我强制执行聚簇索引扫描，并查看两台服务器上的估计扫描操作，我会突然想到：

| Cost                | Server A    | Server B   |
|---------------------|-------------|------------|
| I/O Cost            | 116.573     | 110.889    |
| CPU Cost            |   5.01945   |   4.77155  |
| Total Operator Cost |   0.0106035 | 115.661    |
                        mistakenly  | avoids it
                          uses it   |

服务器A上的操作员成本太低了。

• I /O成本合理
• CPU成本合理
• 但总的来说，运营商的总体成本低了 4 个数量级。

这就解释了为什么它错误地选择了糟糕的执行计划；它的运营成本很低。服务器 B 正确地找出了它，并避免了聚簇索引扫描。

不是operator = cpu + io吗？

在几乎每个执行计划节点上，您都会将鼠标悬停在上面，在 dba、stackoverflow 和每个博客上执行计划的每个屏幕截图上，您都会看到：

operatorCost >= max(cpuCost, ioCost)

事实上它通常是：

operatorCost = cpuCost + ioCost

那么这是怎么回事？

什么可以解释服务器决定 115 + 5 的成本几乎为零，而是决定成本的 1/10000？

我知道 SQL Server 有选项可以调整应用于 CPU 和 I/O 操作的内部权重：

DBCC    TRACEON (3604);     -- Show DBCC output
DBCC    SETCPUWEIGHT(1E0);  -- Default CPU weight
DBCC    SETIOWEIGHT(0.6E0); -- I/O multiplier = 0.6
DBCC    SHOWWEIGHTS;        -- Show the settings

当您这样做时，运算符成本最终可能会低于 CPU+I/O 成本：

但是没有人玩过这些。有没有可能 SQL Server 有一些基于环境的自动权重调整，或者基于与磁盘子系统的一些通信？

如果服务器是虚拟机，使用虚拟 SCSI 磁盘，通过光纤链路连接到存储区域网络 (SAN)，它可能会决定可以忽略 CPU 和 I/O 成本吗？

除了它不能是此服务器中的永久环境之外，因为我发现的所有其他查询都表现正常：

 I/O:       0.0112613
 CPU:      +0.0001
           =0.0113613 (theoretical)
 Operator:  0.0113613 (actual)

什么可以解释服务器不占用：

I/O Cost + Cpu Cost = Operator Cost

在这个例子中正确吗？

SQL 服务器 2014 SP2。

我一直在尝试诊断应用程序中的减速。为此，我记录了 SQL Server扩展事件。

• 对于这个问题，我正在研究一个特定的存储过程。
• 但是有十几个存储过程的核心集同样可以用作苹果对苹果的调查
• 每当我手动运行其中一个存储过程时，它总是运行得很快
• 如果用户再次尝试：它将运行得很快。

存储过程的执行时间差别很大。此存储过程的许多执行都在 < 1 秒内返回：

而对于那个“快速”的存储桶来说，它远远小于 1 秒。实际上大约是 90 毫秒：

但是有长尾的用户要等2s、3s、4s秒。有些人必须等待 12 秒、13 秒、14 秒。然后是真正可怜的灵魂，他们必须等待 22 秒、23 秒、24 秒。

30 秒后，客户端应用程序放弃，中止查询，用户不得不等待30 秒。

寻找因果关系的相关性

所以我试图关联：

• 持续时间与逻辑读取
• 持续时间与物理读取
• 持续时间与CPU 时间

似乎没有任何相关性。似乎没有一个原因

• 持续时间与逻辑读取：无论是少量还是大量逻辑读取，持续时间仍然波动很大：

  

• 持续时间与物理读取：即使查询不是从缓存中提供的，并且需要大量物理读取，它也不会影响持续时间：

  

• duration vs cpu time：无论查询占用了 0 秒的 CPU 时间，还是完整的 2.5 秒的 CPU 时间，持续时间都具有相同的可变性：

  

奖励：我注意到Duration v Physical Reads和Duration v CPU time看起来非常相似。如果我尝试将 CPU 时间与物理读取相关联，则可以证明这一点：

原来很多 CPU 使用来自 I/O。谁知道！

因此，如果执行查询的行为没有任何内容可以解释执行时间的差异，这是否意味着它与 CPU 或硬盘驱动器无关？

如果 CPU 或硬盘是瓶颈；不会是瓶颈吗？

如果我们假设 CPU 是瓶颈；该服务器的 CPU 供电不足：

• 那么使用更多CPU时间的执行不会花费更长的时间吗？
• 因为他们必须使用过载的 CPU 与其他人一起完成？

对于硬盘驱动器也是如此。如果我们假设硬盘驱动器是一个瓶颈；硬盘驱动器没有足够的随机吞吐量用于此服务器：

• 那么使用更多物理读取的执行不会花费更长的时间吗？
• 因为他们必须使用过载的硬盘驱动器 I/O 与其他人一起完成？

存储过程本身既不执行也不要求任何写入。

• 通常它返回 0 行 (90%)。
• 偶尔它会返回 1 行 (7%)。
• 很少会返回 2 行 (1.4%)。
• 在最坏的情况下，它返回超过 2 行（一次返回 12 行）

因此，它不会返回大量数据。

服务器 CPU 使用率

服务器的处理器使用率平均约为 1.8%，偶尔会飙升至 18%——因此 CPU 负载似乎不是问题：

所以服务器 CPU 似乎并没有超载。

但是服务器是虚拟的...

宇宙之外的东西？

我能想象的唯一剩下的东西是存在于服务器宇宙之外的东西。

• 如果不是逻辑读取
• 这不是物理读取
• 这不是CPU使用率
• 这不是 CPU 负载

而且它不像是存储过程的参数（因为手动发出相同的查询并且不需要 27 秒 - 它需要 ~0 秒）。

还有什么可以解释服务器有时需要 30 秒而不是 0 秒来运行相同的已编译存储过程。

• 检查站？

它是一个虚拟服务器

• 主机超载？
• 同一主机上的另一个虚拟机？

浏览服务器的扩展事件；当查询突然需要 20 秒时，没有什么特别的事情发生。它运行良好，然后决定不正常运行：

• 2 秒
• 1秒
• 30秒
• 3 秒
• 2 秒

而且我找不到其他特别费力的项目。它不是在每 2 小时的事务日志备份期间。

还能是什么？

除了“服务器”之外，我还能说什么吗？

编辑：按时间关联

我意识到我已经将持续时间与所有内容相关联：

• 逻辑读
• 物理读取
• CPU使用率

但我没有把它与一天中的时间联系起来的一件事。也许每 2 小时一次的事务日志备份是个问题。

或者，在检查站期间，卡盘中的减速确实发生了？

没有：

英特尔至强金牌四核 6142。

编辑 - 人们正在假设查询执行计划

人们假设查询执行计划在“快”和“慢”之间一定是不同的。他们不是。

我们可以从检查中立即看到这一点。

我们知道较长的问题持续时间不是因为“糟糕”的执行计划：

• 一个需要更多逻辑读取的
• 从更多的连接和键查找消耗更多的 CPU

因为如果读取的增加或 CPU 的增加是导致查询持续时间增加的原因，那么我们已经在上面看到了。没有相关性。

但是让我们尝试将持续时间与 CPU 读取区域产品指标相关联：

相关性变得更小了——这是一个悖论。

编辑：更新散点图以解决具有大量值的 Excel 散点图中的错误。

下一步

我的下一步将是让某人必须为被阻止的查询生成事件 - 5 秒后：

EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE

它不会解释查询是否被阻止4秒。但也许任何阻止查询 5 秒的东西也会阻止一些查询 4 ​​秒。

缓慢的计划

这是正在执行的两个存储过程的慢速计划：

• `EXECUTE FindFrob @CustomerID = 7383, @StartDate = '20190725 04:00:00.000', @EndDate = '20190726 04:00:00.000'
• `EXECUTE FindFrob @CustomerID = 7383, @StartDate = '20190725 04:00:00.000', @EndDate = '20190726 04:00:00.000'

The same stored procedure, with the same parameters, run back to back:

| Duration (us) | CPU time (us) | Logical reads | Physical reads | 
|---------------|---------------|---------------|----------------|
|    13,984,446 |        47,000 |         5,110 |            771 |
|     4,603,566 |        47,000 |         5,126 |            740 |

Call 1:

|--Nested Loops(Left Semi Join, OUTER REFERENCES:([Contoso2].[dbo].[Frobs].[FrobGUID]) OPTIMIZED)
    |--Nested Loops(Inner Join, OUTER REFERENCES:([Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]))
    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Contoso2].[dbo].[FrobTransactions].[RowNumber]) OPTIMIZED)
    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tpi].[TransactionGUID]) OPTIMIZED)
    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tpi].[TransactionGUID]) OPTIMIZED)
    |    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[TransactionPatronInfo].[IX_TransactionPatronInfo_CustomerID_TransactionGUID] AS [tpi]), SEEK:([tpi].[CustomerID]=[@CustomerID]) ORDERED FORWARD)
    |    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[Transactions].[IX_Transactions_TransactionGUIDTransactionDate]), SEEK:([Contoso2].[dbo].[Transactions].[TransactionGUID]=[Contoso2].[dbo
    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactions2_MoneyAppearsOncePerTransaction]), SEEK:([Contoso2].[dbo].[FrobTransactions].[TransactionGUID]=[Contos
    |    |    |--Clustered Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactions_RowNumber]), SEEK:([Contoso2].[dbo].[FrobTransactions].[RowNumber]=[Contoso2].[dbo].[Fin
    |    |--Clustered Index Seek(OBJECT:([Contoso2].[dbo].[Frobs].[PK_Frobs_FrobGUID]), SEEK:([Contoso2].[dbo].[Frobs].[FrobGUID]=[Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]),  WHERE:([Contos
    |--Filter(WHERE:([Expr1009]>(1)))
     |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1012],0)))
          |--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
           |--Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactins_OnFrobGUID]), SEEK:([Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]=[Contoso2].[dbo].[Frobs].[LC

Call 2

|--Nested Loops(Left Semi Join, OUTER REFERENCES:([Contoso2].[dbo].[Frobs].[FrobGUID]) OPTIMIZED)
    |--Nested Loops(Inner Join, OUTER REFERENCES:([Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]))
    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Contoso2].[dbo].[FrobTransactions].[RowNumber]) OPTIMIZED)
    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tpi].[TransactionGUID]) OPTIMIZED)
    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tpi].[TransactionGUID]) OPTIMIZED)
    |    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[TransactionPatronInfo].[IX_TransactionPatronInfo_CustomerID_TransactionGUID] AS [tpi]), SEEK:([tpi].[CustomerID]=[@CustomerID]) ORDERED FORWARD)
    |    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[Transactions].[IX_Transactions_TransactionGUIDTransactionDate]), SEEK:([Contoso2].[dbo].[Transactions].[TransactionGUID]=[Contoso2].[dbo
    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactions2_MoneyAppearsOncePerTransaction]), SEEK:([Contoso2].[dbo].[FrobTransactions].[TransactionGUID]=[Contos
    |    |    |--Clustered Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactions_RowNumber]), SEEK:([Contoso2].[dbo].[FrobTransactions].[RowNumber]=[Contoso2].[dbo].[Fin
    |    |--Clustered Index Seek(OBJECT:([Contoso2].[dbo].[Frobs].[PK_Frobs_FrobGUID]), SEEK:([Contoso2].[dbo].[Frobs].[FrobGUID]=[Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]),  WHERE:([Contos
    |--Filter(WHERE:([Expr1009]>(1)))
     |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1012],0)))
          |--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
           |--Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactins_OnFrobGUID]), SEEK:([Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]=[Contoso2].[dbo].[Frobs].[LC

It makes sense for the plans to be identical; it's executing the same stored procedure, with the same parameters.

给定一些带有主键的表，例如：

CREATE TABLE Customers (
   CustomerID int NOT NULL PRIMARY KEY,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   Address nvarchar(200),
   Email nvarchar(260)
   --...
)

我们有一个唯一的主键CustomerID。

传统上我可能需要一些额外的覆盖索引；例如，通过CustomerID或快速找到用户Email：

CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)

这些是我几十年来创建的索引类型。

它不需要是唯一的，但它实际上是

索引本身的存在是为了避免表扫描；它是一个覆盖索引以提高性能（该索引不作为强制唯一性的约束）。

今天我想起了一点信息——SQL Server 可以使用以下事实：

• 列具有外键约束
• 列具有唯一索引
• 一个约束是可信的

为了帮助它优化其查询执行。事实上，来自SQL Server Index Design Guide：

如果数据是唯一的并且您希望强制执行唯一性，则在相同的列组合上创建唯一索引而不是非唯一索引可为查询优化器提供额外信息，从而生成更有效的执行计划。在这种情况下，建议创建唯一索引（最好通过创建 UNIQUE 约束）。

鉴于我的多列索引包含主键，这个复合索引实际上是唯一的。这不是我特别需要 SQL Server 在每次插入或更新期间强制执行的约束；但事实是这个非聚集索引是唯一的。

将这个事实上的唯一索引标记为实际唯一有什么好处吗？

在客户上创建唯一索引 IX_Customers_CustomerIDEmail
(
   客户ID，
   电子邮件
)

在我看来，SQL Server可以足够聪明地意识到我的索引已经是唯一的，因为它包含主键这一事实。

• 但也许它不知道这一点，如果我将索引声明为唯一，优化器就有优势。
• 除了现在可能会导致插入和更新过程中的速度变慢，它必须执行唯一性检查——以前从来没有这样做过。
• 除非它知道索引已经保证是唯一的，因为它包含主键。

当复合索引包含主键时，我找不到来自 Microsoft 的任何指导。

唯一索引的好处包括：

• 确保已定义列的数据完整性。
• 提供了有助于查询优化器的附加信息。

如果复合索引已经包含主键，我应该将它标记为唯一吗？或者 SQL Server 可以自己解决这个问题吗？

在高并发期间，我们遇到了查询返回无意义结果的问题——结果违反了发出查询的逻辑。重现该问题需要一段时间。我已经设法将可重现的问题提炼为少量的 T-SQL。

注意：有问题的实时系统部分由 5 个表、4 个触发器、2 个存储过程和 2 个视图组成。我已将真实系统简化为更易于管理的已发布问题。事情已经减少，列被删除，存储过程被内联，视图变成了公用表表达式，列的值发生了变化。这就是说，虽然下面的内容重现了错误，但它可能更难以理解。您必须避免想知道为什么某些东西是这样构造的。我在这里试图弄清楚为什么错误情况会在这个玩具模型中重复发生。

/*
The idea in this system is that people are able to take days off. 
We create a table to hold these *"allocations"*, 
and declare sample data that only **1** production operator 
is allowed to take time off:
*/
IF OBJECT_ID('Allocations') IS NOT NULL DROP TABLE Allocations
CREATE TABLE [dbo].[Allocations](
    JobName varchar(50) PRIMARY KEY NOT NULL,
    Available int NOT NULL
)
--Sample allocation; there is 1 avaialable slot for this job
INSERT INTO Allocations(JobName, Available)
VALUES ('Production Operator', 1);

/*
Then we open up the system to the world, and everyone puts in for time. 
We store these requests for time off as *"transactions"*. 
Two production operators requested time off. 
We create sample data, and note that one of the users 
created their transaction first (by earlier CreatedDate):
*/
IF OBJECT_ID('Transactions') IS NOT NULL DROP TABLE Transactions;
CREATE TABLE [dbo].[Transactions](
    TransactionID int NOT NULL PRIMARY KEY CLUSTERED,
    JobName varchar(50) NOT NULL,
    ApprovalStatus varchar(50) NOT NULL,
    CreatedDate datetime NOT NULL
)
--Two sample transactions
INSERT INTO Transactions (TransactionID, JobName, ApprovalStatus, CreatedDate)
VALUES (52625, 'Production Operator', 'Booked', '20140125 12:00:40.820');
INSERT INTO Transactions (TransactionID, JobName, ApprovalStatus, CreatedDate)
VALUES (60981, 'Production Operator', 'WaitingList', '20150125 12:19:44.717');

/*
The allocation, and two sample transactions are now in the database:
*/
--Show the sample data
SELECT * FROM Allocations
SELECT * FROM Transactions

交易都插入为WaitingList. 接下来，我们有一个周期性任务运行，寻找空槽并将 WaitingList 上的任何人撞到 Booked 状态。

在一个单独的 SSMS 窗口中，我们有模拟的循环存储过程：

/*
    Simulate recurring task that looks for empty slots, 
    and bumps someone on the waiting list into that slot.
*/
SET NOCOUNT ON;

--Reset the faulty row so we can continue testing
UPDATE Transactions SET ApprovalStatus = 'WaitingList'
WHERE TransactionID = 60981

--DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

DECLARE @attempts int
SET @attempts = 0;

WHILE (@attempts < 1000000)
BEGIN
    SET @attempts = @attempts+1;

    /*
        The concept is that if someone is already "Booked", then they occupy an available slot.
        We compare the configured amount of allocations (e.g. 1) to how many slots are used.
        If there are any slots leftover, then find the **earliest** created transaction that 
        is currently on the WaitingList, and set them to Booked.
    */

    PRINT '=== Looking for someone to bump ==='
    WITH AvailableAllocations AS (
        SELECT 
            a.JobName,
            a.Available AS Allocations, 
            ISNULL(Booked.BookedCount, 0) AS BookedCount, 
            a.Available-ISNULL(Booked.BookedCount, 0) AS Available
        FROM Allocations a
            FULL OUTER JOIN (
                SELECT t.JobName, COUNT(*) AS BookedCount
                FROM Transactions t
                WHERE t.ApprovalStatus IN ('Booked') 
                GROUP BY t.JobName
            ) Booked
            ON a.JobName = Booked.JobName
        WHERE a.Available > 0
    )
    UPDATE Transactions SET ApprovalStatus = 'Booked'
    WHERE TransactionID = (
        SELECT TOP 1 t.TransactionID
        FROM AvailableAllocations aa
            INNER JOIN Transactions t
            ON aa.JobName = t.JobName
            AND t.ApprovalStatus = 'WaitingList'
        WHERE aa.Available > 0
        ORDER BY t.CreatedDate 
    )


    IF EXISTS(SELECT * FROM Transactions WHERE TransactionID = 60981 AND ApprovalStatus = 'Booked')
    begin
        --DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS
        RAISERROR('The later tranasction, that should never be booked, managed to get booked!', 16, 1)
        BREAK;
    END
END

最后在第三个 SSMS 连接窗口中运行它。这模拟了一个并发问题，即早期事务从占用一个槽到等待列表：

/*
    Toggle the earlier transaction back to "WaitingList".
    This means there are two possibilies:
       a) the transaction is "Booked", meaning no slots are available. 
          Therefore nobody should get bumped into "Booked"
       b) the transaction is "WaitingList", 
          meaning 1 slot is open and both tranasctions are "WaitingList"
          The earliest transaction should then get "Booked" into the slot.

    There is no time when there is an open slot where the 
    first transaction shouldn't be the one to get it - he got there first.
*/
SET NOCOUNT ON;

--Reset the faulty row so we can continue testing
UPDATE Transactions SET ApprovalStatus = 'WaitingList'
WHERE TransactionID = 60981

DECLARE @attempts int
SET @attempts = 0;

WHILE (@attempts < 100000)
BEGIN
    SET @attempts = @attempts+1

    /*Flip the earlier transaction from Booked back to WaitingList
        Because it's now on the waiting list -> there is a free slot.
        Because there is a free slot -> a transaction can be booked.
        Because this is the earlier transaction -> it should always be chosen to be booked
    */
    --DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

    PRINT '=== Putting the earlier created transaction on the waiting list ==='

    UPDATE Transactions
    SET ApprovalStatus = 'WaitingList'
    WHERE TransactionID = 52625

    --DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS

    IF EXISTS(SELECT * FROM Transactions WHERE TransactionID = 60981 AND ApprovalStatus = 'Booked')
    begin
        RAISERROR('The later tranasction, that should never be booked, managed to get booked!', 16, 1)
        BREAK;
    END
END

从概念上讲，碰撞过程一直在寻找任何空槽。如果它找到一个，它将获取最早的事务WaitingList并将其标记为Booked。

在没有并发的情况下进行测试时，逻辑有效。我们有两个交易：

• 12:00 pm：等候名单
• 12:20 pm：等候名单

有 1 个分配和 0 个已预订交易，因此我们将较早的交易标记为已预订：

• 12:00 pm：预订
• 12:20 pm：等候名单

下次任务运行时，现在有 1 个插槽被占用 - 所以没有什么可更新的。

如果我们然后更新第一个事务，并将其放入WaitingList：

UPDATE Transactions SET ApprovalStatus='WaitingList'
WHERE TransactionID = 60981

然后我们回到我们开始的地方：

• 12:00 pm：等候名单
• 12:20 pm：等候名单

注意：您可能想知道为什么我将交易放回等候名单。这是简化玩具模型的牺牲品。在实际系统中事务可以PendingApproval，其中也占用一个槽。PendingApproval 交易在被批准后被放入等待列表。没关系。别担心。

但是当我引入并发性时，通过第二个窗口不断地将第一笔交易在被预订后放回等待列表中，那么后来的交易成功地获得了预订：

• 12:00 pm：等候名单
• 12:20 pm：预订

玩具测试脚本捕捉到这一点，并停止迭代：

Msg 50000, Level 16, State 1, Line 41
The later tranasction, that should never be booked, managed to get booked!

为什么？

问题是，为什么在这个玩具模型中会触发这种救助条件？

第一笔交易的审批状态有两种可能的状态：

• Booked：这种情况下slot被占用，后面的事务不能拥有它
• WaitingList：在这种情况下，有一个空槽和两个需要它的事务。但由于我们总是select最旧的交易（即ORDER BY CreatedDate）第一个交易应该得到它。

我想可能是因为其他索引

我了解到，在 UPDATE 开始并修改数据后，可以读取旧值。在初始条件下：

• 聚集索引：Booked
• 非聚集索引：Booked

然后我进行更新，虽然聚集索引叶节点已被修改，但任何非聚集索引仍然包含原始值并且仍然可以读取：

• 聚集索引（排他锁）：Booked WaitingList
• 非聚集索引：（解锁）Booked

但这并不能解释观察到的问题。是的，交易不再是Booked，这意味着现在有一个空槽。但是该更改尚未提交，它仍然被排他地持有。如果碰撞程序运行，它会：

• block：如果快照隔离数据库选项关闭
• 读取旧值（例如Booked）：如果快照隔离开启

无论哪种方式，碰撞工作都不会知道有一个空槽。

所以我不知道

几天来，我们一直在努力弄清楚这些荒谬的结果是如何发生的。

您可能不了解原始系统，但有一套玩具可重现的脚本。当检测到无效案件时，他们会采取救助措施。为什么会被检测到？为什么会这样？

奖金问题

纳斯达克如何解决这个问题？cavirtex 怎么样？mtgox 怎么样？

tl;博士

共有三个脚本块。将它们放入 3 个单独的 SSMS 选项卡并运行它们。第二个和第三个脚本将引发错误。帮我弄清楚为什么会出现错误。

我正在尝试解决 SQL Server 2008 R2 的问题并且正在抓紧救命稻草。

鉴于数据位于 B 树的叶节点中，并且也存在于索引中，有人可以在单个UPDATE语句中读取多个值吗？

想象一个进程更新一行：

--Change status from Pending -> Waiting
BEGIN TRANSACTION
   UPDATE Transactions SET Status = 'Waiting'
   WHERE TransactionID = 12345
COMMIT TRANSACTION

是否有可能在此期间BEGIN TRANS; UPDATE; COMMIT另一个进程可以读取新值和旧值？

我想知道这一点，因为更新不会仅在一个地方更改值。该值将存在于多个地方：

• 聚集索引的叶节点
• IX_Transactions_1
• IX_Transactions_2
• IX_Transactions_3
• IX_Transctions_4
• IX_Transctions_5
• ...
• IX_Transactions_n

如果更新开始，它必须更新所有这些位置的值。如果另一个进程使用索引执行计划来查找值会发生什么：

• IX_Clustered：等待中
• IX_Transactions_1：等待中
• IX_Transactions_2：等待中
• IX_Transactions_3：待定
• IX_Transactions_4：待定
• IX_Transactions_5：待定
• ...
• IX_Transactions_n：待定

在那一刻，如果发出一个查看聚集索引中的值的查询，它将找到Waiting。

但是，如果查询使用IX_Transactions_4它，它将找到一个值Pending。

取消记录锁定机制

内部的锁定顺序没有记录，但我假设 SQL Server 在聚集和非聚集索引上采用共享锁：

• IX_Clustered：共享
• IX_Transactions_1：共享
• IX_Transactions_2：共享
• IX_Transactions_3：共享
• IX_Transactions_4：共享
• IX_Transactions_5：共享

然后将这些锁升级为更新锁：

• IX_Clustered：共享更新
• IX_Transactions_1：共享更新
• IX_Transactions_2：共享更新
• IX_Transactions_3：共享更新
• IX_Transactions_4：共享更新
• IX_Transactions_5：共享更新

此时，另一个进程仍然可以读取这 5 个索引中的值；因为选择与更新锁兼容。

然后更新继续。如果我们及时拍一张照片：

• IX_Clustered：共享 更新独占 -> 值已更改
• IX_Transactions_1：共享 更新独占 -> 值已更改
• IX_Transactions_2：共享 更新独占
• IX_Transactions_3：共享更新
• IX_Transactions_4：共享更新
• IX_Transactions_5：共享更新

另一个进程不能再从具有排他锁的项目中读取值，但它仍然可以从仍然具有更新锁的项目中读取值（共享锁与更新锁兼容）

当然除非那不是发生的事情

这仅在 SQL 仅根据需要提升为独占时才有效。相反，如果它是这样的：

• IX_Clustered：S U IX X -> 值已更改
• IX_Transactions_1：S U IX X -> 值已更改
• IX_Transactions_2：S U IX X
• IX_Transactions_3：S U IX
• IX_Transactions_4：S U IX
• IX_Transactions_5：S U IX

我不能再打字了；我继续下去的意志被粉碎了。就像我说的，我正在抓住救命稻草。