SEarle1986's questions

假设以下数据库中有两个表，它们之间存在外键关系：

CREATE DATABASE FKLocksTest
GO
ALTER DATABASE FKLocksTest SET READ_COMMITTED_SNAPSHOT OFF

USE FkLocksTest
GO

CREATE TABLE dbo.Department
(
    DeptID INT PRIMARY KEY,
    Deptname NVARCHAR(10)
)
INSERT INTO dbo.Department VALUES (1,'IT'),(2,'HR')


CREATE TABLE dbo.Person
(
    PersonID INT PRIMARY KEY,
    PersonName NVARCHAR(10),
    DepartmentId INT FOREIGN KEY REFERENCES dbo.Department(DeptId)
)
INSERT INTO dbo.Person VALUES (1,'JohnSmith',2) 

如果我运行一些更新，我可以使用跟踪标志 1200 来验证哪些锁被占用。首先，我们可以验证对象 ID：

SELECT  OBJECT_ID('dbo.Department') AS Department,
        OBJECT_ID('dbo.Person') AS Person

现在我启用 TF1200 并运行一些更新，我可以在消息选项卡中看到已获取的锁。我还会确保使用已提交读：

DBCC TRACEON(1200,-1)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

并运行我的第一次更新

UPDATE dbo.Person SET PersonName = 'Jim'

该计划没有提及部门，并且 Traceflag 输出没有提及对象 ID 581577110，这是有道理的

接下来，如果我跑

UPDATE dbo.Person SET DepartmentId = 1 WHERE PersonID = 1

在这种情况下，执行计划引用了部门表，并且 Traceflag 输出显示在 581577110 上进行了锁定。这也是有道理的，因为 SQL Server 必须检查我们设置的 DepartmentId 是否存在于 Department 表中

这次，如果我更新父表并更新主键列

UPDATE dbo.Department SET DeptID = 10 WHERE DeptID = 1

执行计划显示了对 Person 表的引用，并且我可以看到在 613577224 上被锁定。这是合理的，因为 SQL Server 需要检查是否存在与我们正在更新的旧 DepartmentID 关联的 Person 记录。我还遇到了错误，因为有一个 Person 的 DeptId 为 1。

最后，如果我跑

UPDATE dbo.Department SET Deptname = 'aaa' WHERE DeptID = 1

我可以在 Traceflag 输出中看到对 person 表 (613577224) 进行了锁定，但计划中没有对 person 表的引用

TF1200 的简要输出显示了 Person 表上的锁

    ......

Process 59 acquiring Sch-S lock on OBJECT: 9:613577224:0  (class bit0 ref1) result: OK

......

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

......

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring X lock on OBJECT: 9:613577224:0 [UPDSTATS] (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on OBJECT: 9:613577224:0 [UPDSTATS]

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

......

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

......

Process 59 releasing lock on OBJECT: 9:613577224:0 

......

Process 59 acquiring IS lock on OBJECT: 9:613577224:0  (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

......

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "FK__Person__Departme__267ABA7A". The conflict occurred in database "FKLocksTest", table "dbo.Person", column 'DepartmentId'.
The statement has been terminated.

Person 表上的锁可能看起来是编译过程的一部分，如果我多次运行 UPDATE，我最终会得到一个更短的锁列表（下面是完整的），尽管这个计划很简单

Process 59 acquiring IX lock on OBJECT: 9:581577110:0  (class bit2000000 ref1) result: OK

Process 59 acquiring IX lock on PAGE: 9:1:280  (class bit2000000 ref1) result: OK

Process 59 acquiring X lock on KEY: 9:72057594043170816 (8194443284a0) (class bit2000000 ref1) result: OK

Process 59 releasing lock reference on KEY: 9:72057594043170816 (8194443284a0)

Process 59 releasing lock reference on PAGE: 9:1:280 

我的问题是，当我们更新 Department 表中的非关键列时：

• 为什么需要对 Person 表进行锁定
• 为什么只在编译时才锁定

如果我在 Stack Overflow2010 数据库中有以下查询

UPDATE  dbo.Posts
SET     Title =
        CASE 
            WHEN CreationDate <= '2008-01-01T00:00:00'
            THEN 'A'
            ELSE 'B'
        END
FROM    dbo.Posts

简要的STATISTICS IO 输出如下

Table 'Posts'. Scan count 1, logical reads 445699, physical reads 375822, page server reads 0, read-ahead reads 445521, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1878895 rows affected)

执行计划如下

如果我创建一个表来存储我想要在比较中使用的值：

CREATE TABLE dbo.Canary
(
    TheDate DATETIME
)

INSERT INTO dbo.Canary VALUES ('2008-01-01T00:00:00')

然后如果我将查询更改如下：

UPDATE  dbo.Posts
SET     Title =
        CASE 
            WHEN CreationDate <= Canary.TheDate
            THEN 'A'
            ELSE 'B'
        END
FROM    dbo.Posts
        CROSS JOIN dbo.Canary

STATISTICS IO 输出是

Table 'Canary'. Scan count 1, logical reads 1, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 16787757, physical reads 3127, page server reads 0, read-ahead reads 784795, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 6291, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1878895 rows affected)

执行计划如下

我们可以看到逻辑读取的数量大幅增加，从 445k 增加到 16m。我花了一些时间才在执行计划中找到这个问题的根源，但我使用实际 I/O 统计信息/实际逻辑读取属性对其进行了跟踪，并可以看到额外的读取是在聚集索引更新运算符上，我还可以看到此运算符现在在所有执行中都有一个实际行数值，而第一个查询的计划没有。

这里发生了什么？聚集索引更新中发生了什么，导致读取次数增加？

如果要使用这种“配置表中的值”模式，我知道查询应该使用一个变量，但是，这是一个来自供应商应用程序的查询，所以我想反馈查询以这种方式编写后发生的情况。

我在 SQL Server 2019 实例上有一个数据库，该数据库已启用查询存储，并且已运行多年

突然，查询存储状态返回

SELECT actual_state_desc FROM sys.database_query_store_options

去了

错误

所以我重新启用了它

ALTER DATABASE [BOB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

一切正常。然而，几个小时后，同样的事情发生了，我在同一时间在 SQL Server 日志中注意到以下消息

内部 FILESTREAM 错误：无法访问垃圾收集表。

看起来这两者是有关联的，尽管我不确定具体是如何关联的。

我们的数据库没有任何文件流文件：

SELECT  COUNT(*)
FROM    sys.database_files
WHERE   type_desc = 'FILESTREAM'

返回 0

我再次重新启用了查询存储，但想了解这里发生了什么，以防止将来再次发生这种情况。

有人能告诉我发生了什么事以及如何解决吗？

假设以下 SQL Server 代理作业

USE [msdb]
GO


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Do Nothing', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'Me', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Wait', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'WAITFOR DELAY ''00:00:10''', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

以及以下调用代码

EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- waits for 10 seconds
WAITFOR DELAY '00:00:05'
EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- fails, job still running
WAITFOR DELAY '00:00:10'
EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- should succeed

我收到以下错误：

Job 'Do nothing' started successfully. Msg 22022, Level 16, State 1, Line 25 SQLServerAgent Error: Request to run job Do Nothing (from User Me) refused because the job is already running from a request by User Me. Job 'Do nothing' started successfully.

因此我们可以看到第二次sp_start_job调用失败，因为作业仍在运行，然后 SQL Server 继续执行

故障级别为 16，当我执行以下操作时

PRINT 'hello'
    RAISERROR ('error',16,1)
PRINT 'hello'

我得到了相同的“失败并继续”行为

hello
Msg 50000, Level 16, State 1, Line 50
error
hello

但是，如果我执行以下操作，也会引发 16 级错误，则不会运行第二次打印

PRINT 'hello'
    ;THROW 51000, 'error', 1;
PRINT 'hello'

hello
Msg 51000, Level 16, State 1, Line 50
error

THROW状态的文档

THROW 语句中发生的任何错误都会导致语句批处理终止。

RAISERROR状态的文档

如果运行 RAISERROR，则会将错误返回给调用者：超出任何 TRY 块的范围。在 TRY 块中严重性为 10 或更低。严重性为 20 或更高，终止数据库连接。

我的问题是，是否sp_start_job使用 RAISERROR 而不是 throw - 我已经研究过定义，但找不到任何地方

假设我有以下两个数据库

USE [master]
CREATE DATABASE Jack
GO

CREATE DATABASE Jill
GO

USE Jack
GO
CREATE TABLE JacksTable
(
    Id INT
)
INSERT INTO JacksTable VALUES (1)

如果我在 Jack 数据库上下文中创建以下临时存储过程：

USE Jack
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable
GO

然后在Jack数据库中执行：

USE Jack
GO
EXEC #Temp

我得到了结果1

如果我在 Jill 数据库上下文中运行：

USE Jill
GO
EXEC #Temp

我明白了1

现在，如果我放弃这个程序

DROP PROC #Temp

并在 Jill 数据库上下文中再次创建该过程：

USE Jill
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable 
GO

然后运行它

USE Jill
GO    
EXEC #Temp

我收到错误

Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 74]
Invalid object name 'JacksTable'.

如果我尝试从 Jack 数据库执行它：

USE Jack
GO
EXEC #Temp

我明白了

Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 74]
Invalid object name 'JacksTable'.

如果我放弃这个程序

DROP PROC #Temp

再次在 Jill 数据库上下文中重新创建

USE Jill
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable 
GO

这次从 Jack 数据库运行

USE Jack
GO
EXEC #Temp

我得到了同样的绑定错误

Msg 208, Level 16, State 1, Procedure #Temp, Line 4 [Batch Start Line 74]
Invalid object name 'JacksTable'.

这告诉我 SQL Server 尝试将临时过程中的对象绑定到创建临时存储过程时的上下文中的数据库。

这篇文章建议

首次执行存储过程时，查询处理器从 sys.sql_modules 目录视图中读取存储过程的文本，并检查该过程使用的对象名称是否存在。

这项检查针对哪个数据库执行？我认为是 tempdb，因为临时存储过程定义保存在该数据库中？如果是这样，那么该解决方案在所有情况下都会失败（除非我们在 tempdb 中创建了表），因此不可能是那样。

根据我上面看到的内容，它似乎是在创建临时存储过程时处于范围内的数据库（而不是对象定义持久化的数据库 - tempdb）。但是，如果确实如此，SQL Server 如何知道它是哪个数据库以及它存储在哪里？我检查了sys.sql_expression_dependencies、和sys.sql_modules，但似乎没有一个有这些信息sys.objectssys.procedures

给定 StackOverflow2010 数据库，我创建了一些外键以探索连接消除：

外键 1 - 所有评论都必须有帖子

删除未发布评论

DELETE  c
FROM    Comments c
        LEFT JOIN Posts p
            ON c.PostId = p.Id
WHERE   p.Id IS NULL

创建外键

ALTER TABLE Comments 
    ADD CONSTRAINT fk_Comments_PostId 
    FOREIGN KEY (PostId) REFERENCES Posts(Id)

我们现在可以运行下面的查询并查看连接消除：

SELECT  c.Id
FROM    Comments c
        JOIN Posts p
            ON p.Id = c.PostId

计划在这里，正如我所料，只读取了评论表

外键 2 - 所有帖子必须有一个所有者用户

与上述相反，如果我重置并尝试使用以下外键，则看不到连接消除：

删除没有 OwnerUser 的帖子

DELETE  p
FROM    Posts p
        LEFT JOIN Users u
            ON p.Owneruserid = u.id
WHERE   u.Id IS NULL

创建外键

ALTER TABLE Posts 
    ADD CONSTRAINT fk_Posts_UserId 
    FOREIGN KEY (OwnerUserId) REFERENCES Users(Id)

运行我的查询

SELECT  p.Id
FROM    Posts p
        JOIN Users u
            ON p.OwnerUserId = u.Id

该计划显示 SQL Server 访问两个表。

为什么外键 2 不会从连接消除中受益，而外键 1 却可以呢？

我正在调整一个很慢的查询，我将问题的根源缩小到执行计划的最开始，其中 SQL Server 对支持左反连接的 WHERE IS NULL 过滤器做出了错误的估计 - SQL Server 估计 1 行并倾向于通过嵌套循环进行一些索引扫描，认为它只会执行一次，而事实上它发生了几千次：

我已设法创建 MCVE 来复制该问题。

设置测试环境

/* INSERT 35000 dinstinct random numbers into a table */
CREATE TABLE #TableA
(
    ID BIGINT NULL
)

INSERT INTO #TableA
SELECT  DISTINCT
        TOP 35000
        a.Random
FROM    (
            SELECT  TOP 50000
                    ABS(CHECKSUM(NewId())) % 20000000 AS Random
            FROM    sys.messages
        ) a
GO

/* add a further 15000 that already exist in the table. Use a loop to increase the possibility of duplicates */
INSERT INTO #TableA
SELECT  TOP 1000 
        ID
FROM    #TableA a
ORDER BY NEWID()
GO 15


/* Insert 10000 numbers into another table, that are in the first table  */
CREATE TABLE #TableB
(
    ID BIGINT NOT NULL
)

INSERT INTO #TableB
SELECT  TOP 10000
        *
FROM    #TableA

/* insert 80000 distinct random numbers that are not in the first table */
INSERT INTO #TableB
SELECT  DISTINCT
        TOP 80000
        a.Random
FROM    (
            SELECT  TOP 100000
                    ABS(CHECKSUM(NewId())) % 2000000 AS Random
            FROM    sys.messages
        ) a
        LEFT JOIN #TableA b
            ON a.Random = b.ID
WHERE   b.ID IS NULL

那么，出现问题的查询是

SELECT  a.ID
FROM    #TableA a
        LEFT JOIN #TableB b
            ON a.ID = b.ID
WHERE   b.ID IS NULL

这是一个相当简单的“显示 TableA 中所有不在 TableB 中的 ID”

我的测试环境的执行计划如下

我们可以看到与上面的计划非常相似的事情，就过滤运算符而言 - SQL Server 将两个表连接在一起，然后过滤出左表中但不在右表中的记录，它大大低估了与该谓词匹配的行数

如果我强制使用遗留估计，我会对操作员得到更好的估计

我认为旧估计量和新估计量之间的一个主要区别是它们对两个谓词之间相关性的假设有何不同——旧估计量假设两个谓词之间相关性很小，而新估计量则更为乐观，假设相关性更高？

我的问题是

• 什么原因导致对较新的基数估计量的低估？
• 除了强制使用旧的兼容模型之外，还有其他方法可以解决这个问题吗？

给出下表：

CREATE TABLE #a
(
    MyInt INT
)

INSERT INTO #a VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

CREATE TABLE #b
(
    MyVarchar VARCHAR(10)
)

INSERT INTO #b VALUES('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('ten')

如果我运行以下查询；

SELECT  *
FROM    #a
        LEFT JOIN #b
            ON #a.MyInt = #b.MyVarchar

SQL Server 必须执行隐式转换，因为#a.MyInt和#b.MyVarchar是不匹配的数据类型。由于数据类型优先级，具有最低类型优先级 (#b.MyVarchar) 的列将转换为较高优先级 (INT) 的类型

这意味着上面的查询等效于

SELECT  *
FROM    #a
        LEFT JOIN #b
            ON #a.MyInt = CONVERT(INT,#b.MyVarchar)

两者都失败，因为其中某个值#b.MyVarchar对于列来说是无效值INT。

我的问题是为什么 的VARCHAR优先级低于INT？如果是相反的情况，隐式转换将会成功，但我们会得到一个错误的查询。为什么错误比成功执行更可取？我的猜测是，这对于 SQL Server 来说更像是一种“防御”机制 - 它更喜欢错误，因此需要用户明确决定他们想要做什么，而不是在用户不知情的情况下给出可能意外的查询结果。意识到？

PROD我们在和之间设置了数据库镜像，SECONDARY并且已故障转移到辅助副本 ( SECONDARY)。现在，我们在服务器日志中看到许多几乎持续失败的登录PROD。

应用程序使用连接字符串的故障转移伙伴属性：

Data Source=PROD;Failover Partner=SECONDARY;Initial Catalog=myDataBase;

我们已经确认它们正在按预期工作。

这是有道理的，显式指定的数据库PROD无法打开，因为它由于故障转移而处于恢复状态，但应用程序已成功连接到SECONDARY。此错误消息是否是故障转移数据库镜像主副本上的预期行为？

下面使用一个简单的、基于 ODBC 的场景复制了该问题

设置镜像数据库：

在主服务器上创建登录名

USE Mirroring
CREATE LOGIN MirrorUser WITH PASSWORD = 'MyPassHere', DEFAULT_DATABASE = 'Mirroring'
CREATE USER MirrorUser
ALTER ROLE db_datareader ADD MEMBER MirrorUser

和次要的：

CREATE LOGIN MirrorUser WITH PASSWORD = 'MyPassHere',SID = <Sid from primary here>

设置 DSN：

在 Access 数据库中设置链接表

将数据库故障转移到辅助数据库：

ALTER DATABASE Mirroring SET PARTNER FAILOVER

打开Access链接表并验证我们可以看到故障转移后的数据：

观察 .\PROD 上的错误

我可以在 AdventureWorks 表 Person.Person 上创建以下约束：

ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan CHECK (LastName <> N'Doesn''t Exist')

这告诉 SQL Server LastName 的值不能为Doesn't Exist

优化器在以下简单查询中利用了这一点：

SELECT  *
FROM    Person.Person
WHERE   LastName = N'Doesn''t Exist'

由于约束告诉优化器列中没有任何内容可以等于我们正在平等搜索的值（假设有可信约束），因此优化器仅执行持续扫描并且“不执行任何操作”

如果我放弃上面的约束并创建一个稍微不同的约束：

ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan2 CHECK (LastName <> N'Doesn''t Exist' AND FirstName <> N'Doesn''t Exist')

并使用谓词运行查询，其结果将违反检查约束：

SELECT  *
FROM    Person.Person
WHERE   FirstName = N'Doesn''t Exist' AND
        LastName = N'Doesn''t Exist' 

我们通过键查找进行索引查找

但是，如果我跑

SELECT  *
FROM    Person.Person
WHERE   FirstName = N'Doesn''t Exist' AND
        LastName = N'Doesn''t Exist' 

仅保留原始约束：

ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan CHECK (LastName <> N'Doesn''t Exist')

我再次得到持续的扫描

当约束禁止其结果时，为什么在使用两个谓词运行查询时无法获得持续扫描？我是否正确地假设这只是优化器内功能的限制？

我试图了解 SQL Server 如何估计以下 Stack Overflow 数据库查询的基数

首先，我创建索引

CREATE INDEX IX_PostId ON dbo.Comments
(
    PostId
)
INCLUDE
(
    [Text]
)

这是查询：

SELECT  u.DisplayName,
        c.PostId,
        c.Text
FROM    Users u
        JOIN Comments c
            ON u.Reputation = c.PostId
WHERE   u.AccountId = 22547

执行计划在这里

首先，SQL Server 扫描用户表上的聚集索引以返回与 AccountId 谓词匹配的用户。我可以看到它使用了这个统计数据：_WA_Sys_0000000E_09DE7BCC

我可以看到该用户没有范围高键，因此 SQL Server 使用 avg_range 行并估计 1

评论索引搜索的搜索谓词是

soScalar Operator([StackOverflow2010].[dbo].[Users].[Reputation] as [u].[Reputation]表示 users 表中 accountId 为 User(s) 的信誉值22547

我可以看到总共加载了三个统计数据：

_WA_Sys_0000000E_09DE7BCC- Users.AccountId（用于估计聚簇索引查找谓词）

IX_PostId- Comments.PostId（用于估计Index seek predicate）

_WA_Sys_0000000A_09DE7BCC- Users.Reputation (?)

SQL Server 如何得出索引搜索的估计值？22547它无法在编译时知道 accountId 的信誉，因为帐户 ID 统计信息不显示这一点，因此它无法对 IX_PostId 的直方图执行查找。我可以看到声誉统计数据也已加载，那么它是否以某种方式同时使用了两者？

此查询针对 CE 150 运行

我们在 SQL 服务器上启用了 SSL，并且我们的监控工具警告存在未加密的连接。

监控此检查每分钟运行一次，并使用以下查询进行检查：

SELECT  COUNT(*)
FROM    sys.dm_exec_connections
WHERE   encrypt_option <> 'TRUE' 

我做了一些进一步的调查，设置了一个代理作业以将以下查询的结果转储到一个表中（该作业每分钟运行一次）

SELECT  c.*,
        r.status,
        r.command,
        r.last_wait_type,
        s.host_name,
        s.program_name,
        s.client_interface_name,
        s.login_name,
        s.database_id,
        qs.text
FROM    sys.dm_exec_connections c
        LEFT JOIN sys.dm_exec_requests r
            ON c.connection_id = r.connection_id
        LEFT JOIN sys.dm_exec_sessions s
            ON c.session_id = s.session_id
        OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) qs
WHERE   encrypt_option <> 'TRUE'

如果我从我的表中选择，我会看到一些结果（由于结果集很宽，分布在三个屏幕截图中）：

对我来说，sys.dm_exec_connections在这里继续下去并没有太多意义。这些连接唯一看起来特别的是它们都是端点 2 ( TSQL Local Machine) 并且具有 auth_schema(unknown)

我可以在此基础上将它们排除在监控之外，但我想在这样做之前确保它们是良性的。

这些联系是什么？为什么启用 SSL 后它们未加密？我可以查看更多信息以更好地了解它们吗？

我在查询存储中强制执行如下计划

EXEC sys.sp_query_store_force_plan @query_id = 113366, @plan_id = 3687662

但是当我再次运行查询时，查询没有使用计划，也没有显示failure_force_reason

以下查询显示该计划已被强制执行，并表明在上次运行时强制执行没有失败

SELECT  plan_id,
        query_id,
        is_forced_plan,
        last_force_failure_reason_desc
FROM    sys.query_store_plan
WHERE   is_forced_plan = 1

以下查询显示了所讨论查询的最后运行时间，这向我证实了我确实重新运行了这个查询，并且它使用了与我强制执行的计划不同的计划：

SELECT  TOP 1
        q.query_id,
        p.plan_id,
        s.last_execution_time,
        SYSDATETIMEOFFSET() AS CurrentTime
FROM    sys.query_store_query q
        JOIN sys.query_store_plan p
            ON q.query_id = p.query_id
        JOIN sys.query_store_runtime_stats s
            ON s.plan_id = p.plan_id
WHERE   q.query_id = 113366
ORDER BY s.last_execution_time DESC

为什么查询存储似乎忽略了这个计划力？我可以利用任何扩展事件或其他故障排除工具来了解吗？

前几天有人问我，如果 SQL Server 想要运行单个查询，而该查询被授予的内存比实例可用的内存多，会发生什么情况。我最初的想法是我可能会看到RESOURCE_SEMAPHORE等待并且查询永远不会开始。

我做了一些测试试图找出答案。

我的实例在 4000MB RAM 上启动：

EXEC sys.sp_configure N'max server memory (MB)', N'4000'
GO
RECONFIGURE WITH OVERRIDE
GO

如果我然后运行我的（故意可怕的）查询：

USE StackOverflow
SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp2,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp3
FROM        dbo.Users AS u
            JOIN dbo.Posts p
                ON LTRIM(u.DisplayName) = LTRIM(p.Tags)
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate < '2010-12-26'
ORDER BY    u.CreationDate;

执行计划说授予的内存是 732,008KB。

然后我将我的实例可用的内存设置为低于此数字，然后重新启动实例：

EXEC sys.sp_configure N'max server memory (MB)', N'500' /* a value lower than the previous memory grant */
GO
RECONFIGURE WITH OVERRIDE
GO

我再次运行查询，发现它被授予的内存比以前少（93,176KB），但计划实际上是不同的形状。

然后我再次运行查询并使用查询提示强制原始计划查看授予的内存：

USE StackOverflow
SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp2,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp3
FROM        dbo.Users AS u
            JOIN dbo.Posts p
                ON LTRIM(u.DisplayName) = LTRIM(p.Tags)
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate < '2010-12-26'
ORDER BY    u.CreationDate
OPTION (RECOMPILE, USE PLAN N'<xml here>'

我发现查询现在使用原始计划，但获得与其编译的计划（93,168KB）非常相似的内存授权 -强制实际计划

这似乎反驳了我的理论，即我会看到RESOURCE_SEMAPHORE等待，并表明存在某种机制可以防止 SQL Server 授予比查询可用的内存更多的内存（这似乎非常明智！）顺便说一下，如果我在同时会话中运行查询两次500MB 服务器设置，然后两个会话都会RESOURCE_SEMAPHORE等待似乎不确定的内容。

我可以MaxQueryMemory在计划中看到，这似乎是阻止 SQL Server 授予比查询可用的更多内存的原因。

是否可以为单个查询授予比实例可用的内存更多的内存？如果不是，是MaxQueryMemory什么原因导致 SQL Server 分配的内存超出了可用内存？这个数字是如何计算的？

注意 - 我的 StackOverflow 数据库处于 130 兼容级别

我有一个查询，我在查询存储中强制执行一个计划（该计划是为此查询编译的一个 SQL Server）如果我在强制执行该计划后立即运行查询，NO_PLAN尽管数据库没有更改，但我会得到 last_force_failure_reason_desc。我可以成功地为同一个查询强制执行不同的计划

问题如下图所示：

创建我们的测试数据库

USE [master]
CREATE DATABASE NO_PLAN
ALTER DATABASE [NO_PLAN] SET QUERY_STORE = ON
ALTER DATABASE [NO_PLAN] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL)
GO

USE NO_PLAN
GO
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTableA') DROP TABLE MyTableA
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTableB') DROP TABLE MyTableB

/* create  our tables */
CREATE TABLE [dbo].[MyTableA](
    [Column1] VARCHAR(50) NULL ,
    [Column2] VARCHAR(255) NULL ,
    [Column3] INT NULL ,
    [Column4] DATETIME NULL ,
    [Column5] INT NULL ,
    [Column6] VARCHAR(50) NULL ,
    [Column7] VARCHAR(255) NULL ,
    [Column8] INT NULL ,
    [Column9] DATETIME NULL ,
    [Column10] INT NULL ,
    [Column11] INT NULL ,
    [Column12] DATETIME NULL ,
    [Column13] VARCHAR(50) NULL ,
    [Column14] VARCHAR(50) NULL ,
    [Column15] DATETIME NULL ,
    [Column16] DATETIME NULL ,
    [Column17] VARCHAR(8) NULL ,
    [Column18] DATETIME NULL ,
    [Column19] INT NULL ,
    [Column20] INT NULL ,
    [Column21] VARCHAR(50) NULL ,
    [Column22] VARCHAR(255) NULL ,
    [Column23] VARCHAR(50) NULL ,
    [Column24] VARCHAR(255) NULL ,
    [Column25] VARCHAR(50) NULL ,
    [Column26] INT NULL ,
    [Column27] INT NULL ,
    [Column28] INT NULL ,
    [Column29] INT NULL ,
    [Column30] INT NULL ,
    [Column31] INT NULL ,
    [Column32] INT NULL ,
    [Column33] INT NULL ,
    [Column34] INT NULL ,
    [Column35] VARCHAR(50) NULL ,
    [Column36] VARCHAR(50) NULL ,
    [Column37] VARCHAR(50) NULL ,
    [Column38] VARCHAR(50) NULL ,
    [Column39] VARCHAR(255) NULL ,
    [Column40] INT NULL ,
    [Column41] VARCHAR(50) NULL ,
    [Column42] INT NULL ,
    [Column43] VARCHAR(255) NULL ,
    [Column44] INT NULL ,
    [Column45] VARCHAR(255) NULL ,
    [Column46] INT NULL ,
    [Column47] DATETIME NULL ,
    [Column48] DATETIME NULL ,
    [Column49] DATETIME NULL ,
    [Column50] INT NULL ,
    [Column51] VARCHAR(50) NULL ,
    [Column52] VARCHAR(255) NULL ,
    [Column53] VARCHAR(50) NULL ,
    [Column54] VARCHAR(255) NULL ,
    [Column55] VARCHAR(50) NULL ,
    [Column56] VARCHAR(255) NULL ,
    [Column57] VARCHAR(50) NULL ,
    [Column58] VARCHAR(50) NULL ,
    [Column59] CHAR NULL ,
    [Column60] CHAR NULL ,
    [Column61] CHAR NULL ,
    [Column62] CHAR NULL ,
    [Column63] CHAR NULL ,
    [Column64] CHAR NULL ,
    [Column65] CHAR NULL ,
    [Column66] CHAR NULL ,
    [Column67] CHAR NULL ,
    [Column68] CHAR NULL ,
    [Column69] CHAR NULL ,
    [Column70] CHAR NULL ,
    [Column71] CHAR NULL ,
    [Column72] CHAR NULL ,
    [Column73] CHAR NULL ,
    [Column74] CHAR NULL ,
    [Column75] CHAR NULL ,
    [Column76] DATETIME NULL ,
    [Column77] INT NULL ,
    [Column78] INT NULL ,
    [Column79] VARCHAR(50) NULL ,
    [Column80] VARCHAR(255) NULL ,
    [Column81] VARCHAR(50) NULL ,
    [Column82] VARCHAR(255) NULL ,
    [Column83] VARCHAR(50) NULL ,
    [Column84] VARCHAR(255) NULL ,
    [Column85] VARCHAR(50) NULL ,
    [Column86] VARCHAR(255) NULL ,
    [Column87] VARCHAR(50) NULL ,
    [Column88] VARCHAR(255) NULL ,
    [Column89] VARCHAR(50) NULL ,
    [Column90] VARCHAR(255) NULL ,
    [Column91] VARCHAR(50) NULL ,
    [Column92] VARCHAR(255) NULL ,
    [Column93] VARCHAR(50) NULL ,
    [Column94] VARCHAR(255) NULL ,
    [Column95] VARCHAR(50) NULL ,
    [Column96] VARCHAR(255) NULL ,
    [Column97] VARCHAR(50) NULL ,
    [Column98] VARCHAR(255) NULL ,
    [Column99] VARCHAR(50) NULL ,
    [Column100] VARCHAR(255) NULL ,
    [Column101] VARCHAR(50) NULL ,
    [Column102] VARCHAR(255) NULL ,
    [Column103] VARCHAR(50) NULL ,
    [Column104] VARCHAR(255) NULL ,
    [Column105] VARCHAR(50) NULL ,
    [Column106] VARCHAR(255) NULL ,
    [Column107] VARCHAR(50) NULL ,
    [Column108] VARCHAR(50) NULL ,
    [Column109] VARCHAR(50) NULL ,
    [Column110] VARCHAR(255) NULL ,
    [Column111] VARCHAR(50) NULL ,
    [Column112] VARCHAR(255) NULL ,
    [Column113] VARCHAR(50) NULL ,
    [Column114] VARCHAR(255) NULL ,
    [Column115] VARCHAR(50) NULL ,
    [Column116] VARCHAR(255) NULL ,
    [Column117] VARCHAR(50) NULL ,
    [Column118] VARCHAR(255) NULL ,
    [Column119] VARCHAR(50) NULL ,
    [Column120] VARCHAR(50) NULL ,
    [Column121] VARCHAR(255) NULL ,
    [Column122] VARCHAR(50) NULL ,
    [Column123] VARCHAR(255) NULL ,
    [Column124] VARCHAR(50) NULL ,
    [Column125] VARCHAR(255) NULL ,
    [Column126] VARCHAR(50) NULL ,
    [Column127] VARCHAR(255) NULL ,
    [Column128] VARCHAR(50) NULL ,
    [Column129] VARCHAR(255) NULL ,
    [Column130] VARCHAR(50) NULL ,
    [Column131] VARCHAR(255) NULL ,
    [Column132] DATETIME NULL ,
    [Column133] VARCHAR(50) NULL ,
    [Column134] VARCHAR(255) NULL ,
    [Column135] VARCHAR(50) NULL ,
    [Column136] INT NULL ,
    [Column137] VARCHAR(50) NULL ,
    [Column138] VARCHAR(255) NULL ,
    [Column139] VARCHAR(50) NULL ,
    [Column140] VARCHAR(255) NULL ,
    [Column141] VARCHAR(50) NULL ,
    [Column142] VARCHAR(255) NULL ,
    [Column143] VARCHAR(50) NULL ,
    [Column144] VARCHAR(255) NULL ,
    [Column145] VARCHAR(50) NULL ,
    [Column146] VARCHAR(255) NULL ,
    [Column147] VARCHAR(50) NULL ,
    [Column148] VARCHAR(255) NULL ,
    [Column149] VARCHAR(50) NULL ,
    [Column150] VARCHAR(255) NULL ,
    [Column151] VARCHAR(50) NULL ,
    [Column152] VARCHAR(255) NULL ,
    [Column153] VARCHAR(50) NULL ,
    [Column154] VARCHAR(255) NULL ,
    [Column155] VARCHAR(50) NULL ,
    [Column156] VARCHAR(255) NULL ,
    [Column157] VARCHAR(50) NULL ,
    [Column158] VARCHAR(255) NULL ,
    [Column159] INT NULL ,
    [Column160] INT NULL ,
    [Column161] VARCHAR(50) NULL ,
    [Column162] VARCHAR(50) NULL ,
    [Column163] VARCHAR(50) NULL ,
    [Column164] VARCHAR(50) NULL ,
    [Column165] VARCHAR(50) NULL ,
    [Column166] VARCHAR(50) NULL ,
    [Column167] VARCHAR(50) NULL ,
    [Column168] VARCHAR(50) NULL ,
    [Column169] VARCHAR(255) NULL ,
    [Column170] INT NULL ,
    [Column171] VARCHAR(50) NULL ,
    [Column172] INT NULL ,
    [Column173] VARCHAR(50) NULL ,
    [Column174] VARCHAR(50) NULL ,
    [Column175] VARCHAR(50) NULL ,
    [Column176] VARCHAR(255) NULL ,
    [Column177] VARCHAR(50) NULL ,
    [Column178] VARCHAR(255) NULL ,
    [Column179] VARCHAR(50) NULL ,
    [Column180] VARCHAR(50) NULL ,
    [Column181] VARCHAR(50) NULL ,
    [Column182] VARCHAR(255) NULL ,
    [Column183] VARCHAR(50) NULL ,
    [Column184] VARCHAR(255) NULL ,
    [Column185] VARCHAR(50) NULL ,
    [Column186] VARCHAR(255) NULL ,
    [Column187] VARCHAR(50) NULL ,
    [Column188] VARCHAR(255) NULL ,
    [Column189] VARCHAR(50) NULL ,
    [Column190] VARCHAR(50) NULL ,
    [Column191] VARCHAR(50) NULL ,
    [Column192] VARCHAR(255) NULL ,
    [Column193] VARCHAR(50) NULL ,
    [Column194] VARCHAR(255) NULL ,
    [Column195] VARCHAR(50) NULL ,
    [Column196] VARCHAR(50) NULL ,
    [Column197] VARCHAR(255) NULL ,
    [Column198] INT IDENTITY (1,1) ,
    [Column199] VARCHAR(500) NULL ,
    [Column200] VARCHAR(255) NULL ,
    [Column201] VARCHAR(50) NULL ,
    [Column202] VARCHAR(255) NULL ,
    [Column203] CHAR NULL ,
    [Column204] CHAR NULL ,
    [Column205] VARCHAR(50) NULL ,
    [Column206] VARCHAR(255) NULL ,
    [Column207] BIGINT NULL ,
    [Column208] VARCHAR(50) NULL ,
    [Column209] VARCHAR(50) NULL ,
    [Column210] VARCHAR(50) NULL ,
    [Column211] VARCHAR(255) NULL ,
    [Column212] VARCHAR(50) NULL ,
    [Column213] VARCHAR(255) NULL ,
    [Column214] VARCHAR(50) NULL ,
    [Column215] VARCHAR(50) NULL ,
    [Column216] VARCHAR(50) NULL ,
    [Column217] VARCHAR(50) NULL ,
    [Column218] VARCHAR(50) NULL ,
    [Column219] VARCHAR(50) NULL ,
    [Column220] VARCHAR(50) NULL ,
    [Column221] VARCHAR(50) NULL ,
    [Column222] DATETIME NULL ,
    [Column223] VARCHAR(50) NULL ,
    [Column224] VARCHAR(50) NULL ,
    [Column225] CHAR NULL ,
    [Column226] CHAR NULL ,
    [Column227] CHAR NULL ,
    [Column228] CHAR NULL ,
    [Column229] CHAR NULL ,
    [Column230] CHAR NULL ,
    [Column231] VARCHAR(50) NULL ,
    [Column232] VARCHAR(50) NULL ,
    [Column233] VARCHAR(50) NULL ,
    [Column234] VARCHAR(255) NULL ,
    [Column235] VARCHAR(50) NULL ,
    [Column236] VARCHAR(50) NULL ,
    [Column237] VARCHAR(255) NULL ,
    [Column238] VARCHAR(50) NULL ,
    [Column239] VARCHAR(255) NULL ,
    [Column240] VARCHAR(50) NULL ,
    [Column241] VARCHAR(255) NULL ,
    [Column242] CHAR NULL ,
    [Column243] CHAR NULL ,
    [Column244] DATE NULL ,
    [Column245] DATE NULL ,
    [Column246] DATE NULL ,
    [Column247] VARCHAR(50) NULL ,
    [Column248] VARCHAR(255) NULL ,
    [Column249] VARCHAR(50) NULL ,
    [Column250] VARCHAR(255) NULL ,
    [Column251] DATE NULL ,
    [Column252] DATE NULL ,
    CONSTRAINT [PKC_MyTableA] PRIMARY KEY CLUSTERED 
    (
        [Column198] ASC
    )
)
GO

CREATE TABLE [dbo].[MyTableB]
(
    Column1 [INT] IDENTITY(1,1) NOT NULL,
    Column2 [INT] NULL,
    Column3 [VARCHAR](255) NOT NULL,
    Column4 [VARCHAR](255) NULL,
    Column5 [CHAR](1) NOT NULL,
    Column6 [VARCHAR](MAX) NULL,
    Column7 [VARCHAR](50) NULL,
    CONSTRAINT [PK_MyTableB] PRIMARY KEY CLUSTERED 
    (
        Column3 ASC
    )
)
GO

插入一些虚拟数据：

DECLARE @valsSQL NVARCHAR(MAX) = 'SET IDENTITY_INSERT MyTableA ON; 

INSERT INTO [MyTableA] (' 

SELECT  @valsSQL += c.name + ','
FROM    sys.columns c
        JOIN sys.tables t
            ON c.object_id = t.object_id
WHERE   t.name = 'MyTableA'
ORDER BY column_id

SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')

SET @valsSQL += ' VALUES ( '

SELECT  @valsSql +=
        CASE
            WHEN c.system_type_id = 167 OR --varchar 
                    c.system_type_id = 175 -- char
            THEN '''' +  REPLICATE('a',c.max_length) + ''''
            WHEN c.system_type_id = 61
            THEN '''' +  CONVERT(NVARCHAR,GETDATE(),120) + ''''
            WHEN c.system_type_id = 56 OR --int OR
                c.system_type_id = 47 OR -- bigint
                c.system_type_id = 127
            THEN CONVERT(NVARCHAR(10),CONVERT(INT,FLOOR(RAND()*2147483647)))
            WHEN c.system_type_id = 40
            THEN '''' +  '1900-01-01' + ''''
        END + ','
FROM    sys.columns c
        JOIN sys.types t
            ON c.system_type_id = t.system_type_id
WHERE   OBJECT_NAME(object_id) = 'MyTableA'
ORDER BY column_id

SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')
SET @valsSQL += '; SET IDENTITY_INSERT MyTableA OFF;'

EXEC sp_executesql @stmt = @valsSQL 
GO 500

现在数据库已经建立，运行查询：

USE NO_PLAN
SELECT  1
        -- my unique text to find this query in query store views
FROM    MyTableA 
        INNER JOIN MyTableB Alias  
            ON Alias.Column3 = 'value'
        LEFT JOIN MyTableB  
            ON MyTableB.Column3 =  'value'
WHERE   MyTableB.Column4 IS NULL

NB - 实际的执行计划在这里

使用查询存储 DMV 获取查询 ID 和计划 ID，以便我们可以强制执行计划：

SELECT  t.query_sql_text,
        q.query_id,
        p.plan_id,
        p.query_plan,
        p.is_forced_plan,
        p.last_force_failure_reason_desc,
        p.last_execution_time 
 FROM    sys.query_store_plan p
        JOIN sys.query_store_query q
            ON q.query_id = p.query_id
        JOIN sys.query_store_query_text t
            ON t.query_text_id = q.query_text_id 
 WHERE   t.query_sql_text LIKE '%-- my unique text to find this query in query store views%' AND
        t.query_sql_text NOT LIKE '%sys.query_store_plan%' /* exclude this query */

我的输出如下：

现在强制 SQL 服务器使用它刚刚编译的计划，每次它运行这个查询

EXEC sp_query_store_force_plan @query_id = 6, @plan_id = 6

再次运行查询：

USE NO_PLAN
SELECT  1
        -- my unique text to find this query in query store views
FROM    MyTableA 
        INNER JOIN MyTableB Alias  
            ON Alias.Column3 = 'value'
        LEFT JOIN MyTableB  
            ON MyTableB.Column3 =  'value'
WHERE   MyTableB.Column4 IS NULL

检查查询存储 DMV 以查看它是否使用了计划：

SELECT  t.query_sql_text,
        q.query_id,
        p.plan_id,
        p.query_plan,
        p.is_forced_plan,
        p.last_force_failure_reason_desc,
        p.last_execution_time
FROM    sys.query_store_plan p
        JOIN sys.query_store_query q
            ON q.query_id = p.query_id
        JOIN sys.query_store_query_text t
            ON t.query_text_id = q.query_text_id
WHERE   t.query_sql_text LIKE '%-- my unique text to find this query in query store views%' AND
        t.query_sql_text NOT LIKE '%sys.query_store_plan%' /* exclude this query */

我们可以看到 NO_PLAN 的失败原因：

如果我通过截断表、清除查询存储然后只向表中添加 20 行（或删除数据库并运行上述所有设置但使用GO 20而不是GO 500）来重置：

USE NO_PLAN;
ALTER DATABASE NO_PLAN SET QUERY_STORE CLEAR;
TRUNCATE TABLE [MyTableA];

DECLARE @valsSQL NVARCHAR(MAX) = 'SET IDENTITY_INSERT MyTableA ON; 

INSERT INTO [MyTableA] (' 

SELECT  @valsSQL += c.name + ','
FROM    sys.columns c
        JOIN sys.tables t
            ON c.object_id = t.object_id
WHERE   t.name = 'MyTableA'
ORDER BY column_id

SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')

SET @valsSQL += ' VALUES ( '

SELECT  @valsSql +=
        CASE
            WHEN c.system_type_id = 167 OR --varchar 
                    c.system_type_id = 175 -- char
            THEN '''' +  REPLICATE('a',c.max_length) + ''''
            WHEN c.system_type_id = 61
            THEN '''' +  CONVERT(NVARCHAR,GETDATE(),120) + ''''
            WHEN c.system_type_id = 56 OR --int OR
                c.system_type_id = 47 OR -- bigint
                c.system_type_id = 127
            THEN CONVERT(NVARCHAR(10),CONVERT(INT,FLOOR(RAND()*2147483647)))
            WHEN c.system_type_id = 40
            THEN '''' +  '1900-01-01' + ''''
        END + ','
FROM    sys.columns c
        JOIN sys.types t
            ON c.system_type_id = t.system_type_id
WHERE   OBJECT_NAME(object_id) = 'MyTableA'
ORDER BY column_id

SET @valsSQL = STUFF(@valsSQL,LEN(@valsSQL),1,')')
SET @valsSQL += '; SET IDENTITY_INSERT MyTableA OFF;'

EXEC sp_executesql @stmt = @valsSQL 
GO 20

然后再次运行查询，我得到了不同的计划（注意过滤器运算符的位置已更改）

如果我然后重复获取 query_id 和 plan_id 的过程，强制执行计划并重新运行查询，这一次它将强制执行计划：

我可以确认 NO_PLAN 计划不能通过OPTION (RECOMPILE, USE PLAN N'<planxmlhere>')提示强制执行，我明白了

Msg 8698, Level 16, State 0, Line 5 Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.

A number of articles suggest that the NO_PLAN failure reason is due to changing indexes, however, as can be seen from the example above, nothing has changed between forcing and running the query for the second time.

Article A

Article B

Why can SQL server not be forced to use a plan it just generated, when nothing has changed? What is it about the first plan that causes the forcing to fail an why is that not an issue for the second plan?

我在查询存储中有一个查询，我已经为此制定了一个计划。

我可以确认该计划是被迫的

SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1

并且计划显示在结果中

但是，如果我查看该last_force_failure_reason_desc列，我会看到NO_PLAN

一些谷歌搜索把我带到了以下文章：

肯德拉小

Deepthi Goguri

这两者都表明更改计划使用的索引是导致NO_PLAN失败的原因。

我在第二篇文章中设置了扩展事件会话：

CREATE EVENT SESSION [Querystoreforcedplanfailures] ON SERVER 

ADD EVENT qds.query_store_plan_forcing_failed
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\qserror.xel'),
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

我可以看到相关计划的事件，其中包含以下文本：

查询处理器无法生成查询计划，因为 USE PLAN 提示包含无法验证为合法查询的计划。删除或替换 USE PLAN 提示。为获得成功执行计划的最大可能性，请验证 USE PLAN 提示中提供的计划是 SQL Server 为同一查询自动生成的计划

该查询作为数据仓库构建的一部分每晚运行，其中 DDL 命令很常见，因此我决定设置一个数据库审计规范来捕获SCHEMA_OBJECT_CHANGE_GROUP操作类型，以查看是否有任何索引被更改

USE [master]
GO

CREATE SERVER AUDIT [PlanForceAlters]
TO FILE 
(   FILEPATH = N'P:\Audit\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = 'd2b6b090-395f-42f9-a8bb-c0ba742ce30e')
ALTER SERVER AUDIT [PlanForceAlters] WITH (STATE = ON)
GO


USE [MyDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [PlanForceAlters]
FOR SERVER AUDIT [PlanForceAlters]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

当我如下询问结果时：

SELECT  o.name,
        a.statement
FROM    sys.fn_get_audit_file ('P:\Audit\PlanForce*',default,default) a
        JOIN sys.objects o
            ON o.object_id = a.object_id
WHERE   o.name IN ('MyTableA','MyTable')

我可以看到 IN 子句中表的所有更改（哪些表是从查询中选择的表，我试图强制执行谁的计划）

我所能看到的只是外键删除和重新创建，这对于我们的数据仓库来说是相当正常的。外键使用与删除时相同的名称重新创建。事件顺序是

1. 存在约束（不可信）
2. 约束被删除
3. 查询运行
4. 重新创建约束（同名和 NOCHECK）

强制执行的计划是查询处理器在上面第 3 点生成的计划，因此事件的顺序每晚都相同，我会认为约束的变化是无关紧要的吗？

我更进一步，计算出被删除/重新创建的外键在哪些列上：

SELECT  o.name,
        a.statement,
        c.name
FROM    sys.fn_get_audit_file ('P:\Audit\PlanForce*',default,default) a
        JOIN sys.objects o
            ON o.object_id = a.object_id
        LEFT JOIN sys.foreign_keys fk
            ON  statement LIKE '%ADD CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%DROP CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%ADD  CONSTRAINT%' + fk.name + '%' OR
                statement LIKE '%DROP  CONSTRAINT%' + fk.name + '%'
        LEFT JOIN sys.foreign_key_columns fkc
            ON fk.object_id = fkc.constraint_object_id
        LEFT JOIN sys.all_columns c
            ON c.column_id = fkc.parent_column_id AND
                c.object_id = fkc.parent_object_id
WHERE   o.name IN ('MyTableA','MyTableB')

并且这些都不是 query_plan 中使用的任何非聚集索引中的列

我试图在 AdventureWorks2016 数据库上重新创建一个示例，在该示例中我强制执行一个计划，该计划对具有可信外键的列执行 NCI 搜索，然后观察优化器仍然使用该计划，尽管外键被删除并且在重新创建不受信任时仍然使用：

/* create our stored proc */
CREATE OR ALTER PROCEDURE sp_SalesbyProduct
    @ProductID INT
AS
SELECT 
  SalesOrderID, 
  OrderQty,
  UnitPrice
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
GO

/* create an index to support the query */
CREATE INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail
(
    ProductId
) 
WITH 
(
    DROP_EXISTING = ON
)

/* add a trusted foreign key on the column IX_SalesOrderDetail_ProductID is on */
ALTER TABLE Sales.SalesOrderDetail ADD CONSTRAINT FK_MyKey FOREIGN KEY (ProductID) REFERENCES Production.Product(ProductId)

/* run the proc and ensure differing plans */
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710 /* seek on IX_SalesOrderDetail_ProductID with key lookup */
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870 /* CI Scan*/
GO

/* force the seek / lookup plan */
EXEC sp_query_store_force_plan 222, 224;

/* verify the plan is forced */
SELECT  *
FROM    sys.query_store_plan
WHERE   is_forced_plan = 1

/* run the queries again and ensure both use the seek / lookup plan */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

/* drop the constraint on the column in the index */
ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT FK_MyKey 

/* is the plan still forced? */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

是的！

/* re-add the FK but make it untrusted */
ALTER TABLE Sales.SalesOrderDetail WITH NOCHECK ADD CONSTRAINT FK_MyKey FOREIGN KEY (ProductID) REFERENCES Production.Product(ProductId)

/* is the plan still forced? */

DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 710
GO
DBCC FREEPROCCACHE
GO
EXEC sp_SalesbyProduct @ProductID = 870
GO

是的！

是什么导致了NO_PLAN错误？这与删除/创建外键约束有关吗？

使用 StackOverflow2010 数据库，我可以在 users 表上创建索引，如下所示：

CREATE INDEX IX_DisplayName ON dbo.Users
(
    DisplayName,
    UpVotes
)

然后对索引的键运行不等式搜索：

SELECT  DisplayName,
        UpVotes
FROM    Users
WHERE   DisplayName <> N'Alex'

我在这里得到计划

我正在尝试弄清楚 SQL Server 如何获取此查询的结果。

该计划以一些持续扫描开始，但输出列表是空白的，所以我不清楚它们的用途。

然后每个恒定扫描进入一个计算标量，每个计算标量输出

Compute Scalar Node6
Expr1002 = 10
Expr1003 = NULL
Expr1004 = N'Alex'

Compute Scalar Node9 
Expr1005 = 6 
Expr1006 = N'Alex' 
Expr1007 = NULL

然后，连接运算符似乎连接了上面的一些输出：

Expr1010 = Expr1008,Expr1006
Expr1011 = Expr1004,Expr1009
Expr1012 = Expr1002,Expr1005

但它有我在计划中任何地方都看不到的输入（Expr 1008 和 Expr1009）

我也不确定为什么需要 TOP N 排序

索引搜索是有意义的 - 它正在寻找 > Expr1011 和 < Expr1012。我会假设这基本上是这样的

>= 'a' AND < 'Alex' 

或者

> 'Alex' AND <= 'zzzzzzzzzzzzzz'

或类似的。

有人可以一步一步地向我解释这个计划是如何工作的，以及我如何理解连接运算符产生的 Expr1011 和 Expr1012 （用于索引搜索）的值

我一直在做一些测试，试图更好地理解 SQL Server 如何使用直方图来估计将匹配相等谓词以及 < 或 > 谓词的行数

鉴于我正在使用AdventureWorks2016 OLTP 数据库

如果能理解 SQL Server 对 = 和 > 谓词的估计过程：

/* update stats with fullscan first */    
UPDATE STATISTICS Production.TransactionHistory WITH FULLSCAN

然后我可以看到该列的直方图TransactionHistory.Quantity

DBCC SHOW_STATISTICS (
    'Production.TransactionHistory', 
    'Quantity')

下面的屏幕截图是我运行测试的直方图的顶端：

以下查询将估计 6 行，因为谓词中的值是 RANGE_HI_KEY，因此对该存储桶使用 EQ_ROWS：

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity = 2863

以下将估计 1.36 行，因为它不是 RANGE_HI_KEY，因此将 AVG_RANGE_ROWS 用于它所在的存储桶：

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity = 2862

以下“大于”查询将估计 130 行，这似乎是所有 RANGE_HI_KEY > 2863 的桶的 RANGE_ROWS 和 EQ_ROWS 的总和

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity > 2863

下面的类似查询，但该值不是直方图中的 RANGE_HI_KEY。SQL Server 再次估计为 130 并且似乎使用与上述相同的方法

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity > 2870 

到目前为止，这一切都很有意义，所以我的测试转移到了“小于”查询

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity < 490 

对于这个查询，SQL Server 估计有 109,579 行，但我不知道它是从哪里得到的：

所有存储桶的 RANGE_HI_KEY + RANGE_ROWS 直到 RANGE_HI_KEY 470 = 109,566 = 109,566 所以我们在某个地方还差 11 个。

SQL Server 如何使用直方图估计“小于”谓词将返回的行数

鉴于我正在使用AdventureWorks2016 OLTP 数据库，为什么当该列中有 113k 个不同的值时PK_TransactionHistory_TransactionID，表上索引的统计直方图Production.TransactionHistory仅包含 3 个直方图“桶”？

下面的一个例子：

USE AdventureWorks2016

/* ensure statistics are as accurate as they can be */
UPDATE STATISTICS Production.TransactionHistory WITH FULLSCAN

然后我们可以查看更新后的直方图

/* look at the statistics for the primary key column */
DBCC SHOW_STATISTICS (
    'Production.TransactionHistory', 
    'PK_TransactionHistory_TransactionID')
WITH HISTOGRAM;

我看到了输出：

注意最大和最小事务 ID：

SELECT MIN(TransactionID) FROM Production.TransactionHistory /* 100000 */
SELECT MAX(TransactionID) FROM Production.TransactionHistory /* 213442 */

SQL Server 似乎为最大值创建了一个“桶”，一个用于最小值，一个用于介于两者之间的所有值（它知道它们都是不同的）

我注意到如果我从这个表中删除主键

ALTER TABLE Production.TransactionHistory DROP CONSTRAINT PK_TransactionHistory_TransactionID

然后插入一些重复的值

INSERT INTO [Production].[TransactionHistory]
(
    TransactionID,
    [ProductID],
    [ReferenceOrderID],
    [ReferenceOrderLineID],
    [TransactionDate],
    [TransactionType],
    [Quantity],
    [ActualCost],
    [ModifiedDate]
)
VALUES
(200001,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200011,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200021,1,1,1,GETDATE(),'P',1,1,GETDATE()),
(200031,1,1,1,GETDATE(),'P',1,1,GETDATE())

更新表上的统计信息，然后查看列的统计信息（而不是我们删除的PK）

USE AdventureWorks2016

/* ensure statistics are as accurate as they can be */
UPDATE STATISTICS Production.TransactionHistory WITH FULLSCAN

/* look at the statistics for the primary key column */
DBCC SHOW_STATISTICS (
    'Production.TransactionHistory', 
    'TransactionID')
WITH HISTOGRAM;

我们仍然有两个存储桶，尽管 DISTINCT_RANGE_ROWS 已相应更新

为什么 SQL Server 不使用此处直方图中的 200 个“桶”？这是否与填充 8KB 统计页面所需的资源有关，并且使用所有 200 个存储桶意味着它可能需要重新定义何时将新数据添加到表中？

我们其中一台服务器上的并行设置的成本阈值设置为通常被认为太低的值 (15)，我们正在考虑将其增加到 50，以希望在 CPU 变高时减少它。

我想知道哪些查询会受到影响，以便我们可以对它们进行一些测试和监控。

我解决这个问题的方法是查询计划缓存（并解析 XML）。尽管使用计划缓存存在问题（计划在内存压力下过期/抛出，服务器重新启动等）这是解决此问题的最佳方法吗？

我的查询是

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') AS QueryCost ,
        t.text
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
WHERE   p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') > 15 AND
        p.query_plan.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementSubTreeCost)[1]','FLOAT') <= 50