i-one's questions

在准备我之前的Constant Scan 问题时，我VALUES以各种方式进行了试验，并遇到了关于加入的事情，VALUES这对我来说很奇怪。

设置很简单

CREATE TABLE #data ([Id] int);
INSERT INTO #data VALUES (101), (103);

然后有一个查询

DECLARE @id1 int = 101, @id2 int = 102;

SELECT *
FROM (VALUES (@id1), (@id2)) p([Id])
    FULL HASH JOIN #data d ON d.[Id] = p.[Id];

没有什么特别的。如果您运行它，它会工作并产生结果。这是它的执行计划

VALUES但是从中删除一行

SELECT *
FROM (VALUES (@id1)) p([Id])
    FULL HASH JOIN #data d ON d.[Id] = p.[Id];

导致优化器失败

消息 8622，级别 16，状态 1，第 1 行
查询处理器无法生成查询计划...

为什么？有没有办法（除了将参数放入临时表之外）使用哈希算法使其工作？

注意：这不是真正的工具，而是用于研究优化器行为和功能的目的。

上面的例子是在

Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)

我有一张有几十行的表。简化设置如下

CREATE TABLE #data ([Id] int, [Status] int);

INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);

我有一个查询，将这个表连接到一组表值构造的行（由变量和常量组成），比如

DECLARE @id1 int = 101, @id2 int = 105;

SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (@id1, 'A'),
        (@id2, 'B')
    ) p([Id], [Code])
    FULL JOIN #data d ON d.[Id] = p.[Id];

查询执行计划显示优化器的决定是使用FULL LOOP JOIN策略，这似乎是合适的，因为两个输入的行数都很少。但是，我注意到（并且不能同意）的一件事是 TVC 行正在假脱机（请参见红框中的执行计划区域）。

为什么优化器会在这里引入 spool，这样做的原因是什么？除了线轴之外，没有什么复杂的。看起来没有必要。在这种情况下如何摆脱它，有哪些可能的方法？

上述计划获得于

Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)

在我们的一个数据库中，我们有一个由多个线程集中并发访问的表。线程确实通过MERGE. 有时也会有线程删除行，因此表数据非常不稳定。执行 upsert 的线程有时会遇到死锁。该问题看起来类似于此问题中描述的问题。但是，不同之处在于，在我们的例子中，每个线程都确实更新或插入了一行。

简化的设置如下。该表是堆的，上面有两个唯一的非聚集索引

CREATE TABLE [Cache]
(
    [UID] uniqueidentifier NOT NULL CONSTRAINT DF_Cache_UID DEFAULT (newid()),
    [ItemKey] varchar(200) NOT NULL,
    [FileName] nvarchar(255) NOT NULL,
    [Expires] datetime2(2) NOT NULL,
    CONSTRAINT [PK_Cache] PRIMARY KEY NONCLUSTERED ([UID])
)
GO
CREATE UNIQUE INDEX IX_Cache ON [Cache] ([ItemKey]);
GO

典型的查询是

DECLARE
    @itemKey varchar(200) = 'Item_0F3C43A6A6A14255B2EA977EA730EDF2',
    @fileName nvarchar(255) = 'File_0F3C43A6A6A14255B2EA977EA730EDF2.dat';

MERGE INTO [Cache] WITH (HOLDLOCK) T
USING (
    VALUES (@itemKey, @fileName, dateadd(minute, 10, sysdatetime()))
) S(ItemKey, FileName, Expires)
ON T.ItemKey = S.ItemKey
WHEN MATCHED THEN
    UPDATE
    SET
        T.FileName = S.FileName,
        T.Expires = S.Expires
WHEN NOT MATCHED THEN
    INSERT (ItemKey, FileName, Expires)
    VALUES (S.ItemKey, S.FileName, S.Expires)
OUTPUT deleted.FileName;

即，匹配通过唯一索引键发生。HOLDLOCK由于并发性，提示就在这里（如此处所建议的那样）。

我做了小调查，以下是我发现的。

在大多数情况下，查询执行计划是

具有以下锁定模式

即IX锁定对象，然后是更细粒度的锁定。

然而，有时查询执行计划是不同的

（这个计划形状可以通过添加INDEX(0)提示来强制），它的锁定模式是

X已放置在对象上的通知锁IX。

由于两个IX兼容，但两个X不兼容，并发下发生的事情是

僵局！

这里出现了问题的第一部分。符合条件后是否X锁定对象IX？不是bug吗？

文档指出：

意向锁之所以被命名为意向锁，是因为它们是在较低级别的锁之前获取的，因此表明意图将锁放置在较低级别。

还有_

IX 意味着打算只更新部分行而不是所有行

所以，在我看来非常可疑X之后将锁锁定在对象上。IX

首先，我尝试通过添加表锁定提示来防止死锁

MERGE INTO [Cache] WITH (HOLDLOCK, TABLOCK) T

和

MERGE INTO [Cache] WITH (HOLDLOCK, TABLOCKX) T

随着TABLOCK就地锁定模式变为

并且TABLOCKX锁定模式是

由于两个SIX（以及两个X）不兼容，这可以有效地防止死锁，但不幸的是，它也可以防止并发（这是不希望的）。

我的下一个尝试是添加PAGLOCK和ROWLOCK使锁更细化并减少争用。两者都没有影响（X在对象上仍然被立即观察到IX）。

FORCESEEK我的最后一次尝试是通过添加提示来强制具有良好粒度锁定的“良好”执行计划形状

MERGE INTO [Cache] WITH (HOLDLOCK, FORCESEEK(IX_Cache(ItemKey))) T

它奏效了。

这里出现了问题的第二部分。会不会发生这种情况FORCESEEK会被忽略并使用错误的锁定模式？（正如我所提到的，PAGLOCK并且ROWLOCK似乎被忽略了）。

添加UPDLOCK没有效果（X在对象仍然可以观察到之后IX）。

IX_Cache正如预期的那样，使索引聚集在一起是有效的。它导致了使用Clustered Index Seek和粒度锁定的计划。此外，我尝试强制显示粒度锁定的聚集索引扫描。

然而。补充观察。在原来的设置FORCESEEK(IX_Cache(ItemKey)))中，如果一个@itemKey变量声明从varchar(200)更改为nvarchar(200)，执行计划变为

看到使用了 seek ，但在这种情况下，锁定模式再次显示X在IX.

因此，似乎强制搜索不一定能保证粒度锁（因此没有死锁）。我不相信聚集索引能保证粒度锁定。或者是吗？

我的理解（如果我错了，请纠正我）是锁定在很大程度上是情境性的，并且某些执行计划形状并不意味着某些锁定模式。

关于仍然打开X后对对象加锁的资格问题。IX如果它符合条件，是否可以采取一些措施来防止对象锁定？

当安全所有权发生变化时，我需要执行一些审计，例如

ALTER AUTHORIZATION ON SCHEMA::[SchemaName] TO [PrincipalName];

发生在数据库中。

数据库范围 DDL 触发器似乎是一种适合此目的的机制。在文档中（DDL Statements That Have Server or Database Scope部分）我看到应该有ALTER_AUTHORIZATION事件。

但是，当我尝试创建适当的 DDL 触发器时

CREATE TRIGGER [OnAlterAuthorization] ON DATABASE
FOR ALTER_AUTHORIZATION
AS
BEGIN
    PRINT 'Perform audit';
END

我收到错误消息 1084

消息 1084，级别 15，状态 1，过程 OnAlterAuthorization，第 2 行 [批处理起始行 0]“ALTER_AUTHORIZATION”是无效的事件类型。

在sys.event_notification_event_types

SELECT type_name
FROM sys.event_notification_event_types
WHERE type_name LIKE 'ALTER_AUTHOR%';

没有ALTER_AUTHORIZATION事件，只是

type_name
-----------------------------
ALTER_AUTHORIZATION_SERVER
ALTER_AUTHORIZATION_DATABASE

他们ALTER_AUTHORIZATION_SERVER显然不适合，并且ALTER_AUTHORIZATION_DATABASE根据文档

指定 ON DATABASE 时适用于 ALTER AUTHORIZATION 语句

所以，问题是。文档中在哪里ALTER_AUTHORIZATION承诺？如何捕捉数据库中安全所有权的变化？

假设有一个带聚簇索引的表

create table [a_table] ([key] binary(900) unique clustered);

和一些数据

insert into [a_table] ([key])
select top (1000000) row_number() over (order by @@spid)
from sys.all_columns a cross join sys.all_columns b;

通过检查该表的存储统计信息

select st.index_level, page_count = sum(st.page_count)
from sys.dm_db_index_physical_stats(
    db_id(), object_id('a_table'), NULL, NULL, 'DETAILED') st
group by rollup (st.index_level)
order by grouping_id(st.index_level), st.index_level desc;

有人能看见

index_level page_count
----------- ----------
8           1
7           7
6           30
5           121
4           487
3           1952
2           7812
1           31249
0           125000
NULL        166659

该表总共需要 166659 页。

然而表扫描

set nocount on;
set statistics io, time on;
declare @cnt int;
select @cnt = count(1) from [a_table];
set statistics io, time off;

产生

Table 'a_table'. Scan count 5, logical reads 484367, ...
CPU time = 1757 ms,  elapsed time = 460 ms.

与表占用的空间相比，逻辑读取次数几乎高出三倍。当我检查查询计划时，我注意到 SqlServer 使用并行索引扫描。这就是问题的第一部分出现的地方。

并行索引扫描是如何让SqlServer做这么多逻辑读的？

指定option (maxdop 1)抑制并行

set nocount on;
set statistics io, time on;
declare @cnt2 int;
select @cnt2 = count(1) from [a_table] option (maxdop 1);
set statistics io, time off;

结果

Table 'a_table'. Scan count 1, logical reads 156257, ...
CPU time = 363 ms,  elapsed time = 367 ms.

在这种情况下比较并行和非并行索引扫描的统计数据得出一个结论，即有时最好避免并行索引扫描。这就是问题的第二部分出现的地方。

我什么时候应该担心并行索引扫描？什么时候应该避免/抑制？最佳做法是什么？

以上结果是在

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

假设在数据库中创建了一个证书

create certificate certName
    with subject = 'subj';
GO

以及映射到此证书的用户

create user userName
    from certificate certName;
GO

试图直接冒充该用户

execute as user = 'userName';
GO

execute as或在模块的子句中指定用户

create procedure procName
with execute as 'userName'
as
    set nocount on;
GO

返回错误

消息 15517，级别 16，状态 1 ...
无法作为数据库主体执行，因为主体“userName”不存在，无法模拟此类主体，或者您没有权限。

但是，我无法找到文档（此处和此处）中提到的此限制，其中唯一相关的声明似乎是

user_name 必须存在于当前数据库中，并且必须是单例帐户。user_name 不能是组、角色、证书、密钥或内置帐户，例如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。

是否可以模拟映射到证书的用户（或登录名）？