Frederik Vanderhaegen's questions

我们有一个 DIFF 备份失败的 SQL Server 2014 Enterprise。
这是我们得到的错误信息：

消息 3035，级别 16，状态 1，服务器 sqltest，第 1 行
无法为数据库“database1”执行差异备份，因为当前数据库备份不存在。通过重新发出 BACKUP DATABASE 执行完整的数据库备份，省略 WITH DIFFERENTIAL 选项。

在分析以下查询的输出后，我们注意到第三方工具正在进行快照备份。

    select top 20 bs.type,bs.database_backup_lsn,bs.checkpoint_lsn,bs.backup_start_date,bs.is_snapshot,
    bs.is_copy_only,bs.user_name
    from dbo.backupset bs
    where bs.database_name = 'database1'
    order by backup_start_date desc

根据Pinal Dave的说法，这些工具使用 VSS 进行备份，这不是正常的完整备份。

我不明白为什么 LOG 备份会成功？据我所知，它们也是基于上次的完整备份。
有人可以向我解释这种差异吗？

昨天我收到一封来自我们的供应商的邮件，提到他们的应用程序存在性能问题。

我还不知道问题是什么，但他们提出的解决方案是缩小数据库（因为有很多可用空间）。据我所知，这不会导致性能提升，但显然他们在应用到其他客户时使用这种方法取得了成功。

我认为缩小发生的方式是将页面从文件的后部移动到文件的前部，就像这样压缩数据库并导致大量碎片。

范围扫描是否有可能利用压缩数据库？
或者还有其他一些可以从缩小数据库中受益的边缘案例吗？

我们的 5 个主数据库在单个可用性组中的物理（2 * 8 核，512GB，超线程）SQL Server 2016 SP2 Enterprise 上运行，有时我们会收到租约超时已过期的错误。我的理解是，如果租约无法更新，则存在系统范围的问题。

当我sp_server_diagnostics在主副本的日志文件夹中检查（*SQLDIAG*.xel 文件）的输出时，在超时时间前后，我总是发现挂起的 IO 操作。

<ioSubsystem ioLatchTimeouts="0" intervalLongIos="0" totalLongIos="1">
<longestPendingRequests>
<pendingRequest duration="26566" filePath="\?\F:\SqlLogs\db1.ldf" offset="80824832" handle= "0x8d10" /> <pendingRequest duration="1987" filePath="\?\O:\SqlLogs\db2.ldf" offset="3880740352" handle="0x1330" /> <pendingRequest duration="1093" filePath="\ ?\O:\SqlLogs\db3.ldf" offset="288143360" handle="0x132c" /> <pendingRequest duration="974" filePath="\?\O:\SqlLogs\db3.ldf" offset="288145408" handle="0x132c" /> <pendingRequest duration="937" filePath="\?\O:\SqlLogs\db3.ldf"offset="288146944" handle="0x132c" />
</longestPendingRequests>
</ioSubsystem>

这是我在主副本的集群日志中找到的：

WARN [RES] SQL Server 可用性组：[hadrag] 无法检索数据列。返回代码 -1
ERR [RES] SQL Server 可用性组：[hadrag] 检测到故障，诊断检测信号丢失
ERR [RES] SQL Server 可用性组 <AG_Name>：[hadrag] 可用性组在给定的 HealthCheckTimeout 和 FailureConditionLevel
ERR [ RES] SQL Server 可用性组 <AG_Name>：[hadrag] 资源活动结果 0。
ERR [RES] SQL Server 可用性组：[hadrag] 检测到故障，诊断心跳丢失
ERR [RES] SQL Server 可用性组 <AG_Name>：[ hadrag] 可用性组在给定的 HealthCheckTimeout 和 FailureConditionLevel 情况下不健康
ERR [RES] SQL Server 可用性组 <AG_Name>：[hadrag] Resource Alive 结果 0。WARN
[RHS] Resource AG_Name IsAlive 指示失败。

这是 SQL Server 错误日志中的错误：

错误：19407，严重性：16，状态：1
SQL Server 托管可用性组“AG_Name”在租用超时期限内未收到来自 Windows Server 故障转移群集的进程事件信号。

错误：19407，严重性：16，状态：1
可用性组“AG_Name”和 Windows Server 故障转移群集之间的租约已过期。SQL Server 实例与 Windows Server 故障转移群集之间出现连接问题。若要确定可用性组是否正确进行故障转移，请检查 Windows Server 故障转移群集中相应的可用性组资源。

Always On：可用性组“AG_Name”的本地副本脱机，因为租约过期或租约续订失败。这只是一条信息性消息。无需用户操作。

这是来自的输出SELECT @@version：

Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) Sep 17 2020 22:12:45 版权所有 (c) Microsoft Corporation Enterprise Edition：Windows Server 2012 R2 上基于内核的许可（64 位）标准 6.3（内部版本 9600：）

在我们的监控中，没有高 CPU 使用率的迹象。出现问题时也不会创建内存转储。

由于此超时，WSFC 服务重新启动集群资源“AG_Name”。之后，此资源重新启动，一切都再次完美运行。
我不明白的是：缓慢的 IO 请求如何导致租约超时？许多待处理的 IO 请求会导致租约超时吗？

我们有一个带有最新 CU 的 SQL Server 2016 SP2 Enterprise，数据库文件分布在不同的磁盘上。
所以我们有数据、日志、临时数据库和系统数据库，它们都有自己的驱动器。日期和日志只包含一个文件。
这些驱动器在全闪存 SAN 上都有自己的 LUN。

为了监控延迟，我每 15 分钟捕获sys.dm_io_virtual_file_stats一次，然后使用之前的快照计算延迟。

对于写入延迟，我使用以下计算：

(io_stall_write_ms - lag (io_stall_write_ms,1,0) over (order by checkdate))/(num_of_writes - lag (num_of_writes,1,0) over (order by checkdate)) write_latency 

我的平均写入延迟为 10 毫秒，但是当我启动 perfmon（持续时间设置为 900 秒）并监控平均值时。同一驱动器在同一时间段内的磁盘秒/写入平均写入延迟仅为 3 毫秒。

我还在捕获同一时期的等待统计信息，当我查看 PAGEIOLATCH_EX 等待并计算每次等待所用的时间时，我也得到了大约 3 毫秒的值。

我认为 io_stall_write_ms 表示与 avg 相同。磁盘秒/写还是我错过了什么？
有人可以解释这种行为吗？

我们在 SQL Server 2016 SP2 CU12 Enterprise 上有一个查询，其中查询优化器估计只有 1 行会来自嵌套循环连接运算符，实际上有 108501 行返回。这导致Sort操作员溢出到 TempDB。

对 Nested Loops Join 的内部（索引搜索）和外部输入（索引搜索）的估计是正确的。

我添加了跟踪标志 2363（选择性计算）和 3604（将输出重定向到消息窗口），在这里我发现有一个格式错误的直方图：

Plan for computation:

  CSelCalcExpressionComparedToExpression( QCOL: [Object1].Column1 x_cmpEq QCOL: [Object3].Column18 )

Loaded histogram for column QCOL: [Object1].Column1 from stats with id 1  *** WARNING: badly-formed histogram ***

Loaded histogram for column QCOL: [Object3].Column18 from stats with id 9

Selectivity: 1.07973e-009

Stats collection generated: 

  CStCollJoin(ID=4, CARD=1 x_jtLeftSemi)

      CStCollBaseTable(ID=1, CARD=5.01133e+007 TBL: Schema1.Table2 AS TBL: AA)

      CStCollFilter(ID=3, CARD=108210)

          CStCollBaseTable(ID=2, CARD=2.00511e+006 TBL: Schema1.Table1 AS TBL: A)

End selectivity computation

以上只是部分输出，全文可以看这里

当我用全扫描更新格式错误的直方图时，估计是正确的（没有全扫描，这个问题没有解决）。

但是，只要在表中插入一条记录，直方图就会再次形成错误。

查询计划（带有错误直方图）可以在这里找到，在这里您可以找到更新统计信息后的查询计划。

未启用查询优化器修复。当我为此查询启用原始基数估计器时，使用跟踪标志 9481，我得到与更新统计信息后相同的查询计划。

什么会导致直方图格式不正确？

有没有办法解决这个问题？

我尝试了该PERSIST_SAMPLE_PERCENT选项，但没有任何区别，直方图的格式也很糟糕。

我正在努力调整我们生产 SQL Server 2016 SP2 上的一个查询。由于安全策略，我需要匿名化查询。查询是这样的：

SELECT TOP 1000 Object1.Column1,Object1.Column2,Object1.Column3,Object1.Column4,Object1.Column5,Object1.Column6,Object1.Column7,Object1.Column8,Object1.Column9,Object1.Column10,Object1.Column11,Object1.Column12,Object1.Column13,Object1.Column14,Object1.Column15,Object1.Column16,Object1.Column17,Object1.Column18,Object1.Column19,Object1.Column20,Object1.Column21,Object1.Column22,Object1.Column23,Object1.Column24,Object1.Column25,Object1.Column26,Object1.Column27,Object1.Column28,Object1.Column29,Object1.Column30,Object1.Column31,Object1.Column32,Object1.Column33,Object1.Column34,Object1.Column35,Object1.Column36,Object1.Column37,Object1.Column38,Object1.Column39,Object1.Column40,Object1.Column41,Object1.Column42,Object1.Column43,Object1.Column44,Object1.Column45,Object1.Column46,Object1.Column47,Object1.Column48,Object1.Column49,Object1.Column50,Object1.Column51,Object1.Column52
FROM Schema1.Object2 Object1
LEFT OUTER JOIN Schema1.Object3 Object4 ON Object4.Column3 = Object1.Column3
WHERE ((Object1.Column43 IS NULL OR Object1.Column43='') AND (Object4.Column54 = 0 OR Object4.Column54 IS NULL) AND Object1.Column3 = N'SomeValue')
ORDER BY Object1.Column3 ASC,Object1.Column8 ASC,Object1.Column7 ASC,Object1.Column12 ASC,Object1.Column13 ASC,Object1.Column1 ASC

可以在此处
找到实际的查询计划 正如您所见，在 Object2.Index1.Object1 上完成了索引搜索，但估计很糟糕。SQL Server 估计将返回 194907 行，但仅返回 710 行。

当我删除该ORDER BY子句时，估计要好得多，SQL Server 预计只有 1181 行返回。该计划可以在这里找到。

我不明白为什么 anORDER BY会影响索引搜索的估计。两个查询都使用相同的 column3 值运行。
有人可以向我解释一下吗？

我有一个运行 SQL Server 2016（4 个 vcore）的虚拟服务器，其中大多数时候处理器队列长度为 4（有时它会上升到 15），但 CPU 平均使用 25%。大约有3000批次/秒。

使用SQLSkills.com 上 Glenn Berry 的一篇文章中的查询，我发现有avg_task_count15 个和avg_runnable_task_count2 个（但不是经常）：

SELECT AVG(current_tasks_count) AS [Avg Task Count], 
AVG(work_queue_count) AS [Avg Work Queue Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);

说处理器队列长度和avg_task_count表示有很多请求并且服务器在处理它们时遇到问题是否正确？但我不明白的是为什么cpu使用率不高？

物理主机有 25% 的 CPU 使用率，没有内存预留，但主机上没有内存过度使用。

我们有两个 SQL Server 2016 SP2 CU6 Enterprise 在物理硬件（32 核）上运行，其中一个 AG 包含 5 个 db。二级不可读。今天我们进行了计划中的手动故障转移，因此我们可以在另一台服务器上进行维护工作。我们在没有重负载的时候进行了故障转移。

故障转移是通过 SSMS 的 GUI 执行的，没有出现任何错误。但几分钟后，我们接到了很多用户无法登录的电话。我们第一次尝试进行故障排除是与 SSMS 建立连接，但这给出了我们无法连接的错误，我不记得确切的错误消息。

接下来我们查看了记事本中的错误日志文件，就像 SQL Server 引擎挂了一样。故障转移后没有新条目添加到日志中。

由于问题的紧迫性，我们在主服务器上重新启动了 SQL Server 服务，问题就消失了。
我们本可以尝试建立 DAC 连接以查看问题所在，但目标是尽快使服务器恢复联机。

在那之后一切都重新上线，我们开始分析日志文件。
在旧主节点的错误日志中，我们发现了几个错误 35278。故障转移时没有长时间运行的事务正在运行。

接下来是 AlwaysON_health 事件文件，这里的条目在故障转移后刚刚停止。

接下来我们看一下*_*_SQLDIAG_*_*.xel文件。在这里我们很幸运。

我们注意到的第一件事是：

<queryProcessing maxWorkers="960" workersCreated="1064"
 workersIdle="23" tasksCompletedWithinInterval="19" pendingTasks="34"
 oldestPendingTaskWaitingTime="1316776"

出于某种原因，超过了工作人员的最大数量，这可以解释为什么没有人可以连接。

这是待处理的任务：

<pendingTasks><br>
   <entryPoint name="Process Command" count="14" /><br>
   <entryPoint name="SNI New Connection" count="2" /><br>
   <entryPoint name="SNI Accept Done" count="3" /><br>
   <entryPoint moduleName="sqlmin.dll" imageBase="0x7ff827e80000" size="0x251e000" address="0x7ff8286622e0" count="6" /><br>
   <entryPoint moduleName="sqlmin.dll" imageBase="0x7ff827e80000" size="0x251e000" address="0x7ff8292b1190" count="2" /><br>
   <entryPoint moduleName="sqldk.dll" imageBase="0x7ff827120000" size="0x4c9000" address="0x7ff827125e60" count="7" />  <br>
</pendingTasks>

该文件中还有几个被阻止的进程。他们都有一个 waitresource 像DATABASE: 5:5, DATABASE: 5:15, DATABASE: 5:26, ...
所有这些进程都有lastbatchstarted并lastbatchcompleted设置为 1900-01-01。等待时间等于故障转移发生的时间。
在我们的监控中，我们看到总共有 950 个被阻止的进程。

这是一个阻塞进程报告的示例：

<blocked-process-report monitorLoop="0">
 <blocked-process>
  <process id="process13f08032ca8" taskpriority="0" logused="10000" waitresource="DATABASE: 5:3 " waittime="1334205" schedulerid="4" kpid="11752" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="our_app" hostname="host2021" hostpid="14196" loginname="user1" isolationlevel="read committed (2)" xactid="0" currentdb="1" currentdbname="master" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="DATABASE: 5:3 " waittime="1335893" spid="70" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="our_app" hostname="host1" hostpid="1324" loginname="user1" isolationlevel="read committed (2)" xactid="0" currentdb="1" currentdbname="master" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>
<blocked-process-report monitorLoop="0">
 <blocked-process>
  <process id="process13f08033848" taskpriority="0" logused="10000" waitresource="DATABASE: 5:3 " waittime="1335893" schedulerid="4" kpid="12004" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="our_app" hostname="host1" hostpid="1324" loginname="user1" isolationlevel="read committed (2)" xactid="0" currentdb="1" currentdbname="master" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="background" waittime="1139955" spid="141" sbid="0" ecid="0" priority="0" trancount="0">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

其中一个阻塞进程以 user 身份执行SA并具有 command UNKNOWN TOKEN。此last_wait_type会话的时间PARALLEL_REDO_WORKER_WAIT_WORK与last_request_start_time我们进行故障转移的时间相同。
我没想到会在主节点上找到这种等待类型。
说不PARALLEL_REDO_WORKER_WAIT_WORK应该在初选上预期是否正确？

同样在我们的监控中，我们发现了大约 1000 个会话，这些会话的状态SLEEPING为last_request_start_time01/01/1900。
这似乎一点也不正常。

有人可以解释那些待处理的任务是什么吗？
我对根本原因的猜测是 1000 个具有状态的会话SLEEPING，它们都使用了一个工人。这导致了挂起的任务，并且由于阻塞过程，没有工作人员可以做任何工作。这导致某处的故障转移卡住了。
这可能是正确的吗？

最近我在 mssqltips.com 上阅读了一篇关于 SQL Server 内存瓶颈的博文。在本文中，我阅读了以下内容：

SQL Server 上的以下性能计数器：缓冲区管理器对象也可以指示内存压力：

• 大量检查点页面/秒
• 大量惰性写入/秒
• 大量页面读取/秒
• 低缓冲区缓存命中率
• 低页面预期寿命

引起我注意的是大量的“检查点页面/秒”可能表示内存压力。

我的理解是检查点将“脏”页面写入磁盘。这可以通过不同的方式触发。

• 自动（保持恢复间隔）
• 间接（维持目标恢复时间）
• 手动的
• 内部的

如此多的检查点表明系统非常繁忙（在自动和间接检查点的情况下）。

因为检查点永远不会从缓冲区高速缓存中删除页面，所以我不太明白检查点/秒的数量有多高可以指示内存压力。如果存在内存压力，我会看到大量“延迟写入/秒”。懒惰的作者从内存中删除“冷页”，为新页面腾出空间。

大量检查点页面/秒如何指示内存压力？

今天我正在使用扩展事件解决 SQL Server 2016 SP2 上 TempDB 的问题，并将 mixed_extent_allocation 和 transaction_log 事件添加到我的跟踪中。

我预计 mixed_extent_allocation 不会出现在我的结果中，因为以下查询返回 0 作为结果：

select is_mixed_page_allocation_on  
from sys.databases 
where database_id=2

但令我惊讶的是，这个事件出现了好几次。这之前是一个以 SGAM 作为上下文和操作 LOP_SET_BITS 的 transaction_log 事件。

这让我很好奇，于是查看了TempdB第一个SGAM页面的内容：

DBCC TRACEON(3604)
dbcc page(tempdb,1,3,3)
DBCC TRACEOff(3604)

这是结果的一个片段：

╔═════════════════════════════════════════════╗
║ (1:0)        - (1:176)      = NOT ALLOCATED ║
║ (1:184)      -              =     ALLOCATED ║
║ (1:192)      -              = NOT ALLOCATED ║
║ (1:200)      - (1:208)      =     ALLOCATED ║
║ (1:216)      - (1:256)      = NOT ALLOCATED ║
║ (1:264)      -              =     ALLOCATED ║
║ (1:272)      -              = NOT ALLOCATED ║
║ (1:280)      -              =     ALLOCATED ║
║ (1:288)      - (1:296)      = NOT ALLOCATED ║
║ (1:304)      -              =     ALLOCATED ║
║ (1:312)      -              = NOT ALLOCATED ║
║ (1:320)      -              =     ALLOCATED ║
║ (1:328)      - (1:336)      = NOT ALLOCATED ║
║ (1:344)      -              =     ALLOCATED ║
║ (1:352)      - (1:65528)    = NOT ALLOCATED ║
╚═════════════════════════════════════════════╝

这让我得出结论，仍然使用混合范围。我以为SQL Server 2016 只使用统一范围（master、msdb 和 model 除外）。还是我的结论有误？

为什么我仍然看到 mixed_extent_allocation 事件弹出？

在最大内存设置为 25GB 的 SQL Server 2016 SP2 上，我们有一个查询在一分钟内执行大约 80 次。该查询将大约 4000 页溢出到 tempdb。这会导致 tempdb 磁盘上出现大量 IO。

当您查看查询计划（简单查询）时，您会看到估计的行数等于实际行数，但仍然会发生溢出。所以过时的统计数据不可能是问题的原因。

我做了一些测试并将查询溢出到 Tempdb：

select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

但是，如果我选择不同的列，则不会发生溢出：

select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc 
option (maxdop 1)

所以我试图“扩大” id 列的大小：

select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc 
option (maxdop 1)

然后也不会发生溢出。

为什么 uniqueidentifier 溢出到 tempdb 而不是数据时间列？当我删除大约 20000 条记录时，当我选择 id 列时也不会发生溢出。

使用以下脚本可以重现问题：

CREATE TABLE SortProblem
  (
     id             UNIQUEIDENTIFIER,
     startdate      DATETIME,
     sequencenumber BIGINT,
     status         VARCHAR(50),
     PRIMARY KEY CLUSTERED(id)
  )

SET nocount ON;

WITH nums(num)
     AS (SELECT TOP 103000 ROW_NUMBER()
                             OVER (
                               ORDER BY 1/0)
         FROM   sys.all_objects o1,
                sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
       DATEADD(millisecond, num, GETDATE()),
       num,
       CASE
         WHEN num <= 100000 THEN 'A'
         WHEN num <= 101000 THEN 'B'
         WHEN num <= 102000 THEN 'C'
         WHEN num <= 103000 THEN 'D'
       END
FROM   nums

CREATE NONCLUSTERED INDEX [IX_Status]
  ON [dbo].[SortProblem]([status] ASC)
  INCLUDE ([sequencenumber]) 

在 SQL Server 2016 SP2 上，我们有一个对嵌套循环运算符的估计非常低的查询。由于估计值较低，此查询也会溢出到 tempdb。

如果我是正确的，SQL Server 2014+ 使用粗略直方图估计来计算连接上的估计行数。
但是当我执行查询时，SQL Server 使用密度向量来计算估计的行数。如果没有子句
，SQL Server 是否仅使用粗略直方图估计？where

通常，当我有一个包含倾斜数据的表时，我会使用过滤统计来改进估计。但在这种情况下，这似乎不起作用。

有没有办法改进嵌套循环的估计？

使用以下代码，您可以重现数据：

create table MyTable
(
    id int identity,
    field varchar(50),
    constraint  pk_id primary  key clustered (id)
)
go

create table SkewedTable
(
    id int identity,
    startdate datetime,
    myTableId int,
    remark varchar(50),
    constraint  pk_id primary  key clustered (id)
)

set nocount on

insert into MyTable select top 1000 [name] from master..spt_values
go

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 1000

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 

CREATE NONCLUSTERED INDEX [ix_field] ON [dbo].[MyTable]([field] ASC)
go

CREATE NONCLUSTERED INDEX [ix_mytableid] ON [dbo].[SkewedTable]([myTableId] ASC)
go

--95=varchar in sys.messages
set nocount off

;with cte as
( 
    select GETDATE() as startdate ,95 as myTableId, REPLICATE(N'B',FLOOR(RAND()*(40))+1) as remark
    union all
    select * from cte
)
insert into skewedtable select top 40000 * from cte
option(maxrecursion 0)
go

update statistics mytable with fullscan
go

update statistics skewedtable with fullscan
go

在 32GB 的服务器上，我们运行 SQL Server 2014 SP2，最大内存为 25GB，我们有两个表，在这里您可以找到两个表的简化结构：

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceId] [int] NULL,
    [typeID] [int] NULL,
    [remark] [varchar](max) NULL,
    CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Resources](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceUID] [int] NULL,
 CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

具有以下非聚集索引：

CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[Resources]
(
    [resourceUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Settings]
(
    [resourceId] ASC,
    [typeID] ASC
)

数据库配置为compatibility level120。

当我运行此查询时，有溢出到tempdb. 这就是我执行查询的方式：

exec sp_executesql N'
select r.id,remark
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

如果不选择该[remark]字段，则不会发生溢出。我的第一反应是，溢出是由于嵌套循环运算符上的估计行数少所致。

所以我将 5 个日期时间和 5 个整数列添加到设置表并将它们添加到我的选择语句中。当我执行查询时，没有发生溢出。

为什么溢出仅在[remark]选择时发生？这可能与这是一个varchar(max). 我该怎么做才能避免溢出到tempdb？

添加OPTION (RECOMPILE)到查询没有区别。

在BOL上，我阅读了以下有关MultiSubnetFailover=True 的内容：

即使可用性组仅跨越单个子网，MultiSubnetFailover连接选项也应设置为True。这使您可以预配置新客户端以支持未来的子网跨越，而无需更改未来的客户端连接字符串，还可以优化单个子网故障转移的故障转移性能。

据我了解 的工作原理MultiSubnetFailover，设置此选项后，客户端驱动程序会为与侦听器关联的每个 IP 地址设置一个套接字。它们都被并行检查以加快查找在线 IP 的过程，第一个响应的 IP 将用于连接。在这里，我看到了性能提升。

但是单个子网的性能增益在哪里呢？只有 IP 与侦听器相关联。

我正在研究如何解决本地内存压力问题。如果我错了，请纠正我，但据我了解，SQL Server 对缓存和用户存储使用 CLOCK 算法。每个时钟都有两根指针，一根是外部的，一根是内部的。

我的兴趣是内部时钟指针。如果访问缓存的工作人员注意到缓存大于内存的特定百分比，则内部时钟指针开始为该缓存释放内存。

使用 dmvdm_os_memory_cache_clock_hands我可以看到有关时钟指针的信息。该列rounds_count表示时钟指针扫描整个缓存的次数。last_tick_time是手最后移动的时间。round_start_time是最后一次扫描的时间。

last_tick_time和 和有什么不一样round_start_time？是不是手一动就没有清理缓存？

时钟的每个滴答声会删除多少条目？

有人可以帮我理解 SQL Server 中这个时钟算法的工作原理吗？

我们有一个最大内存设置为 24GB 的 SQL Server 2016 SP1。
此服务器有大量编译，这些编译中只有 10% 来自 Ad-Hoc 查询。所以新编译的计划应该存储在计划缓存中，但计划缓存的大小没有增加（大约 3.72GB）。

我怀疑存在导致从缓存中删除计划的本地内存压力。计划缓存压力限制为 5GB。（75% 的可见目标内存从 0-4GB + 10% 的可见目标内存从 4GB-64GB + 5% 的可见目标内存>64GB）。当缓存存储达到压力限制的 75% 时，应从缓存中删除计划。在我的例子中，5 GB 的 75% 是 3.75 GB。所以这似乎是高编译的原因。

有没有办法衡量（perfmon，扩展事件，...）从缓存中删除计划？所以我可以确定本地内存压力真的是高编译的原因吗？

我有一台运行 SQL Server Enterprise 2016 SP2 的服务器，我注意到每分钟都有数百个查询计划失效。我运行sp_BlitzCache @expertmode=1检查“创建于”列以查看计划何时生成。
每分钟大约有 500 个计划被重新创建，并且这种情况会持续一整天，并且总是会重新编译不同的计划。

当我使用 DMVsys.dm_db_index_usage_stats检查 user_updates 时，我发现索引上发生了 10.000 到 1.000.000 次更新。有些表有超过 10.000.000 条记录，但大多数表有 100.000 到 2.000.000 条记录。

据我所知，查询计划在以下情况下会失效：

• 通过 ALTER TABLE/ALTER VIEW 对表/视图定义所做的修改
• 对执行计划使用的任何索引所做的更改
• 更新执行计划使用的统计信息（手动或自动）
• 删除查询计划使用的索引
• sp_recompile / 重新编译
• 表的大量更改
• 在执行计划使用的表上添加/删除触发器
• 内存压力

所有查询都使用KEEP PLANand KEEPFIXED PLAN，所以我认为自动或手动更新统计信息不会使计划无效。这是第三方应用程序，因此我无法更改查询。当我运行时，UPDATE STATISTICS [TableTest] WITH FULLSCAN我没有看到使用该表的查询的重新编译。

接下来，我使用了Jonathan Kehayias 的脚本来检查是否存在内存压力，但脚本只返回了RESOURCE_MEMPHYSICAL_HIGH，所以我认为这不是问题所在。

没有修改表定义，没有发生索引，也没有使用 sp_recompile / with recompile 执行查询

是否还有其他原因会导致查询计划失效？

更新：
经过更多研究后，我认为重新编译是由 plancache 上的内存压力触发的。本文提供了大量有关计划缓存内部结构的信息。这是关于 SQL Server 2005 但希望这仍然适用于 SQL Server 2016。
根据这篇文章，您可以根据可见目标内存总量计算压力限制。

我的研究进展缓慢，因为很难找到有关计划缓存压力的信息。
我会及时通知您进展情况

我们有一个 SQL Server 2016 SP1 CU7 Enterprise 服务器，我们在其中启用了数据库上的查询存储。这是一个高度使用的数据库。
已Max Size设置为 200MB，Stale Query Threshold为 1 天。
我曾经sys.database_query_store_options检查过查询存储的属性，只要the current_storage_size_mb保持在 200MB 以下，一切都会完美无缺。
我已设置Size Based Cleanup Mode为自动，但没有进行清理，通常当查询存储已满 90% 时应触发清理。接下来发生的是查询存储进入只读模式，因为没有剩余可用空间。
我试过更改Query Store Capture Mode为自动但没有任何变化。
如果我随后更改Stale Query Threshold为 30 天，则actual_state_desc对 READ_WRITE 的更改和查询存储将再次开始捕获。这current_storage_size_mb只是变得比 高max_storage_size_mb，过了一会儿actual_state_desc又回到 READ_ONLY。

谁能解释这种行为？

我正在运行一些测试来找出查询计划在更新统计信息后何时失效。我用于测试的机器是 SQL Server Developer 2016 SP1 CU7。

我在BrentOzar.com上找到了一篇关于如何跟踪重新编译的文章，但我从未获得重新编译。 Auto Update Statistics并且Auto Create Statistics都已启用

这是我的测试：

/* Create a table and put over 1k rows in it (to get past the 500 row stats threshold) */
CREATE TABLE dbo.MyTable (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StringField VARCHAR(50));
GO
INSERT INTO dbo.MyTable(StringField) 
SELECT 'Stuff' FROM sys.all_objects;
GO

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

UPDATE STATISTICS WITH FULLSCAN据我了解，当您致电并且行已更改时，该计划应该会失效。或者我错过了什么？

我们有一个在 SQL Server 2016 SP1 Enterprise 上运行并且Allow Snapshot Isolation已启用的数据库。在这种情况下，读者不会阻止作者，而作者也不会阻止读者。

今天我们看到一个SELECT-query 阻塞了一个UPDATE-query。这些是有问题的查询：

第一个查询：

IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT TOP 10 Field1, Field2, Field3 FROM [dbo].[TABLE1] 
WHERE ([Field1] = @SV1 AND (1=1))
ORDER BY A.[Field1] ASC,A.[Field2] ASC
OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

第二个查询：

UPDATE [dbo].[Table1] SET [Field4]= @V128, [Field5]= @V140, [Field6]= @V141 WHERE [Field3] = @ID0

如果正确理解版本控制，SELECT查询不使用任何锁，因为它在数据快照上工作（在事务开始时获取）。-query怎么可能SELECT阻塞UPDATE？