SEarle1986's questions

假设我有以下两个数据库

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 

以下代码创建一个能够恢复数据库的用户：

CREATE LOGIN RestoreUser WITH PASSWORD = 'MyPassword'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser

这工作正常，用户可以恢复数据库，但是如果我想将数据库设置为SINGLE_USER并回滚任何现有连接，恢复命令将被阻止并失败：

第 1 节

/* SELECT from a table and leave the SSMS window open, leaving a sleeping SPID */
USE AdventureWorks2014
SELECT * FROM Person.Person

第 2 节

EXECUTE AS LOGIN = 'RestoreUser'

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE

REVERT

最终，会话 2 超时

Msg 5061, Level 16, State 1, Line 3
ALTER DATABASE failed because a lock could not be placed on database 'AdventureWorks2014'. Try again later.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

以下sp_whoisactive屏幕截图显示该命令被阻止：

会话 59的sp_whoisactive锁定报告显示

<Database name="AdventureWorks2014">
  <Locks>
    <Lock request_mode="X" request_status="CONVERT" request_count="1" />
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
    <Lock request_mode="U" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="(null)">
      <Locks>
        <Lock resource_type="DATABASE.BULKOP_BACKUP_DB" request_mode="U" request_status="GRANT" request_count="1" />
        <Lock resource_type="DATABASE.BULKOP_BACKUP_LOG" request_mode="S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

SET SINGLE_USER的Microsoft 文章指出

为了快速获得独占访问，代码示例使用了 WITH ROLLBACK IMMEDIATE 终止选项。这将导致所有未完成的事务被回滚，并且与 AdventureWorks2012 数据库的任何其他连接立即断开。

和

需要对数据库的 ALTER 权限。

我的RestoreUser帐户具有正确的权限（dbcreator服务器角色授予ALTER ANY DATABASE权限）但我不知道为什么命令被阻止，第一个引号表明所有其他连接都将被断开。

我也同意ALTER ANY CONNECTION了，RestoreUser但这并没有帮助。

我发现确实解决了问题，是否RestoreUser正在恢复数据库中的用户（不需要数据库中的任何权限）

CREATE LOGIN RestoreUser WITH PASSWORD = 'ABC@123'
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser

USE AdventureWorks2014
CREATE USER RestoreUser

编码

EXECUTE AS LOGIN = 'RestoreUser'

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE AdventureWorks2014 FROM DISK = 'C:\Test\AW14.bak' WITH REPLACE

REVERT

然后运行而不会被睡眠会话阻塞。

我的问题是

• 为什么SET SINGLE_USER WITH ROLLBACK IMMEDIATE代码应该杀死并回滚所有打开的 SPID 时会被阻止
• 为什么在数据库中创建用户可以解决这个问题？

鉴于 StackOverflow2010 数据库上的以下简单查询：

SELECT  u.DisplayName,
        u.Reputation
FROM    Users u
        JOIN Posts p
            ON u.id = p.OwnerUserId
WHERE   u.DisplayName = 'alex' AND
        p.CreationDate >= '2010-01-01' AND
        p.CreationDate <= '2010-03-01'

我试图理解为什么要创建索引

CREATE INDEX IX_CreationDate ON Posts
(
    CreationDate
)
INCLUDE (OwnerUserId)

产生更好的估计Posts.CreationDate

当我运行没有索引的查询时，我得到Plan 1。在这个计划中，SQL Server 估计有 298,910 行来自对 Posts 的 CI 扫描，实际上有 552 行回来了——这个估计还有很长的路要走。

添加索引后，我会得到Plan 2，这会导致索引搜索和更准确的估计。

我很好奇为什么添加索引会导致更好的估计，因为在谓词中使用列时会创建统计信息WHERE，无论它是否被索引。

进一步检查，我可以看到Posts.CreationDate计划 1 和计划 2 的谓词不同：

计划 1 谓词

[StackOverflow2010].[dbo].[Posts].[CreationDate] as [p].[CreationDate]>='2010-01-01 00:00:00.000' AND [StackOverflow2010].[dbo].[Posts].[CreationDate] as [p].[CreationDate]<='2010-03-01 00:00:00.000' AND PROBE([Bitmap1002],[StackOverflow2010].[dbo].[Posts].[OwnerUserId] as [p].[OwnerUserId],N'[IN ROW]')

计划 2 谓词

Seek Keys[1]: Start: [StackOverflow2010].[dbo].[Posts].CreationDate >= Scalar Operator('2010-01-01 00:00:00.000'), End: [StackOverflow2010].[dbo].[Posts].CreationDate <= Scalar Operator('2010-03-01 00:00:00.000')

所以我可以看到计划 2 只是要使用直方图来查找两个日期之间的行数，但计划 1 有一个稍微复杂的谓词，涉及位图探测。

这（我认为）解释了为什么对搜索的估计更准确，但我现在想知道什么是位图探测？我可以在计划中看到创建了一个与 Alex 谓词匹配的用户 ID 的位图，这就是正在调查的内容。

我想知道“没有索引，为什么计划 1 与计划 2 不同，唯一的区别是 CI 扫描而不是 CreationDate 上的索引搜索？”

我做了一些进一步的测试，发现如果我在没有索引的情况下运行查询但强制计划进入串行，使用OPTION (MAXDOP 1)我得到计划 3，尽管现在对 Posts 进行 CI 扫描，但它对 CreationDate 的估计更好。如果我查看谓词，我可以看到探针现在已经消失并且位图不再在计划中，因此这使我相信位图与计划并行有关。

所以我的问题是 - 为什么在计划并行时会创建位图，为什么会导致对 的估计如此糟糕Posts.CreationDate？

我有一个错误级别 16 警报设置如下：

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity 16', 
        @message_id=0, 
        @severity=16, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'

我很好奇为什么

SELECT 1/0;

消息 8134，级别 16，状态 1，第 17 行遇到除以零错误。

没有引发错误，但

BACKUP DATABASE MyDatabase TO DISK = 'C:\FolderThatDoesntexist'

消息 3201，级别 16，状态 1，第 8 行无法打开备份设备“C:\FolderThatDoesntexist”。操作系统错误 5（拒绝访问。）。

消息 3013，级别 16，状态 1，第 8 行备份数据库异常终止。

曾是

这篇文章和它引用的博客文章表明 SQL Server 只会为记录的错误引发事件，这可以在sys.messages

所以我查询sys.messages了这些错误代码：

SELECT  severity,
        message_id,
        is_event_logged
FROM    sys.messages
WHERE   language_id = 1033 AND
        message_id IN (8134,3201,3013)

但发现三个都设置为0：

为什么 3201 和 3013 会触发我的警报而 8134 不会？

如果我有一台使用 TDE 的服务器，配置如下：

USE [master]
CREATE DATABASE MyTDE

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My57r0ngp455';
CREATE CERTIFICATE MyTDECert WITH SUBJECT = 'My Certificate';
GO

/* enable TDE */
USE MyTDE;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyTDECert;
GO

ALTER DATABASE MyTDE SET ENCRYPTION ON;
GO

/* backup the TDE cert */
USE [master];
BACKUP CERTIFICATE [MyTDECert] TO FILE = 'C:\Test\TDECert.cert'
WITH PRIVATE KEY (
                   FILE = 'C:\Test\TDEcert.key', 
                   ENCRYPTION BY PASSWORD = 'MyStr0ngP455w0rd'
                  );

我按计划每晚备份：

BACKUP DATABASE MyTDE
TO     DISK = 'C:\Test\TDEDatabaseBackup.bak'
WITH   INIT,
       FORMAT;

如果运行此数据库的服务器发生某种灾难，我需要将备份还原到另一台服务器，为此，我需要备份MyTDECert以在新服务器上重新创建该证书

在新服务器上，我可以从备份创建 TDE 证书：

USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My57r0ngp4552';

CREATE CERTIFICATE MyTDE1
FROM FILE = 'C:\Test\TDEcert.cert'
WITH PRIVATE KEY (FILE = 'C:\Test\TDEcert.key',
DECRYPTION BY PASSWORD = 'MyStr0ngP455w0rd');

这允许我恢复数据库：

RESTORE DATABASE MyTDE
FROM     DISK = 'C:\Test\TDEDatabaseBackup.bak'

同样，如果我有一个未使用 TDE 的数据库，但我使用证书进行备份：

USE [master]
CREATE DATABASE MyDB

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My57r0ngp455';
CREATE CERTIFICATE [BackupCert] WITH SUBJECT = 'Backup Certificate', EXPIRY_DATE = '2022-01-29T15:00:00'

BACKUP CERTIFICATE [BackupCert] TO FILE = 'C:\Test\BackupCert.cert' 
WITH PRIVATE KEY (
                   FILE = 'C:\Test\backupcert.key', 
                   ENCRYPTION BY PASSWORD = 'MyStr0ngP455w0rd'
                  );

BACKUP DATABASE MyDB
TO     DISK = 'C:\Test\EncryptedDBBackup.bak'
WITH   INIT,
       ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert),
       FORMAT;

同样，要将这些备份恢复到另一台服务器，我需要BackupCert在第二台服务器上创建证书的备份：

USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My57r0ngp4552';

CREATE CERTIFICATE [BackupCert]
FROM FILE = 'C:\Test\STEVETEST\BackupCert.cert'
WITH PRIVATE KEY (FILE = 'C:\Test\backupcert.key',
DECRYPTION BY PASSWORD = 'MyStr0ngP455w0rd');

RESTORE DATABASE MyDB
FROM     DISK = 'C:\Test\EncryptedDBBackup.bak'

我不明白的是，我需要在master数据库中备份数据库主密钥吗？我仍然可以使用已备份的证书，并且可以在另一台服务器上重新创建它们，并且它们具有正确的指纹来恢复加密的备份

看这篇文章，它表明如果我将创建密钥的数据库移动到另一台服务器，我需要从备份中重新创建主密钥，但在我的情况下，我并没有移动主数据库本身。

以上场景需要备份DMK吗？