i-one's questions

Na preparação da minha pergunta anterior do Constant Scan, eu estava experimentando VALUESde várias maneiras e me deparei com a coisa sobre a junção VALUESque parece estranha para mim.

A configuração é simples

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

Então há uma consulta

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

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

Não há nada de especial nisso. Ele funciona e produz seu resultado, se você executá-lo. Aqui está o seu plano de execução

Removendo uma linha de VALUESno entanto

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

faz com que o otimizador falhe

Msg 8622, Level 16, State 1, Line 1
Processador de consultas não pôde produzir um plano de consulta...

Por quê? Existe uma maneira (além de colocar o parâmetro na tabela temporária) para fazê-lo funcionar usando o algoritmo de hash?

Observação: isso não é um dispositivo real e serve para estudar o comportamento e os recursos do otimizador.

O exemplo acima foi testado em

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

Eu tenho uma tabela com algumas dezenas de linhas. A configuração simplificada está seguindo

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

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

E eu tenho uma consulta que une essa tabela a um conjunto de linhas construídas com valor de tabela (feitas de variáveis ​​e constantes), como

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];

O plano de execução da consulta está mostrando que a decisão do otimizador é usar a FULL LOOP JOINestratégia, o que parece apropriado, pois ambas as entradas possuem poucas linhas. Uma coisa que notei (e não posso concordar), porém, é que as linhas do TVC estão sendo colocadas em spool (veja a área do plano de execução na caixa vermelha).

Por que o otimizador introduz o spool aqui, qual é a razão para fazê-lo? Não há nada complexo além do carretel. Parece que não é necessário. Como se livrar dele neste caso, quais são as maneiras possíveis?

O plano acima foi obtido em

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

Em um de nossos bancos de dados temos uma tabela que é acessada de forma intensiva e concorrente por várias threads. Threads atualizam ou inserem linhas via MERGE. Há também threads que excluem linhas ocasionalmente, portanto, os dados da tabela são muito voláteis. Threads que fazem upserts sofrem de deadlock algumas vezes. O problema é semelhante ao descrito nesta pergunta. A diferença, porém, é que no nosso caso cada thread atualiza ou insere exatamente uma linha .

A configuração simplificada está a seguir. A tabela é heap com dois índices não clusterizados exclusivos sobre

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

e a consulta típica é

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;

ou seja, a correspondência acontece por chave de índice exclusiva. A dica HOLDLOCKestá aqui, por causa da simultaneidade (como recomendado aqui ).

Eu fiz uma pequena investigação e o seguinte é o que eu encontrei.

Na maioria dos casos, o plano de execução da consulta é

com o seguinte padrão de bloqueio

ou seja IX, bloqueio no objeto seguido por bloqueios mais granulares.

Às vezes, no entanto, o plano de execução da consulta é diferente

(esta forma de plano pode ser forçada adicionando INDEX(0)dica) e seu padrão de travamento é

Xbloqueio de aviso colocado no objeto depois de IXjá ter sido colocado.

Como dois IXsão compatíveis, mas dois Xnão são, o que acontece em concorrência é

impasse !

E aqui surge a primeira parte da questão . A colocação Xde bloqueio no objeto é IXelegível? Não é bug?

A documentação informa:

Os bloqueios de intenção são denominados bloqueios de intenção porque são adquiridos antes de um bloqueio no nível inferior e, portanto, sinalizam a intenção de colocar bloqueios em um nível inferior .

e também

IX significa a intenção de atualizar apenas algumas das linhas em vez de todas elas

então, colocar o Xbloqueio no objeto depois IXparece MUITO suspeito para mim.

Primeiro, tentei evitar o bloqueio tentando adicionar dicas de bloqueio de tabela

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

e

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

com o TABLOCKpadrão de travamento no lugar torna-se

e com o TABLOCKXpadrão de bloqueio é

como dois SIX(assim como dois X) não são compatíveis, isso evita o deadlock efetivamente, mas, infelizmente, também impede a simultaneidade (o que não é desejado).

Minhas próximas tentativas foram adicionar PAGLOCKe ROWLOCKtornar os bloqueios mais granulares e reduzir a contenção. Ambos não tem efeito (o Xobjeto ainda foi observado imediatamente após IX).

Minha tentativa final foi forçar a forma do plano de execução "boa" com um bom bloqueio granular adicionando FORCESEEKdica

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

e funcionou.

E aqui surge a segunda parte da questão . Pode acontecer que FORCESEEKseja ignorado e um padrão de bloqueio ruim seja usado? (Como mencionei, PAGLOCKe ROWLOCKforam ignorados aparentemente).

Adicionar UPDLOCKnão tem efeito ( Xno objeto ainda observável depois IX).

Tornar IX_Cacheo índice agrupado, conforme previsto, funcionou. Isso levou ao planejamento com busca de índice clusterizado e bloqueio granular. Além disso, tentei forçar o Clustered Index Scan que também mostrava o bloqueio granular.

No entanto. Observação adicional. Na configuração original, mesmo com o FORCESEEK(IX_Cache(ItemKey)))local, se alguém alterar @itemKeya declaração da variável de varchar(200) para nvarchar(200) , o plano de execução se tornará

veja que seek é usado, MAS o padrão de bloqueio neste caso mostra novamente Xo bloqueio colocado no objeto após IX.

Assim, parece que a busca forçada não garante necessariamente bloqueios granulares (e, portanto, ausência de deadlocks). Não estou confiante de que o índice clusterizado garanta o bloqueio granular. Ou não?

Meu entendimento (corrija-me se estiver errado) é que o bloqueio é situacional em grande medida, e certa forma de plano de execução não implica em determinado padrão de bloqueio.

A questão sobre a elegibilidade de colocar o Xbloqueio no objeto depois IXde ainda aberto. E se for elegível, há algo que se possa fazer para evitar o bloqueio de objetos?

Eu preciso realizar alguma auditoria, quando mudar de propriedade segura, como

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

acontece no banco de dados.

O gatilho DDL do escopo do banco de dados parece ser um mecanismo apropriado para essa finalidade. Na documentação (seção DDL Statements That Have Server or Database Scope ) vejo que deve haver ALTER_AUTHORIZATIONevent.

No entanto, quando estou tentando criar um DDL-trigger apropriado

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

Estou recebendo erro Msg 1084

Msg 1084, Level 15, State 1, Procedure OnAlterAuthorization, Line 2 [Batch Start Line 0] 'ALTER_AUTHORIZATION' é um tipo de evento inválido.

Dentrosys.event_notification_event_types

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

não há nenhum ALTER_AUTHORIZATIONevento, apenas

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

deles ALTER_AUTHORIZATION_SERVERnão atende obviamente, e ALTER_AUTHORIZATION_DATABASEde acordo com a documentação

Aplica-se à instrução ALTER AUTHORIZATION quando ON DATABASE é especificado

Então, a questão é. Onde está ALTER_AUTHORIZATIONprometido na documentação? Como posso pegar a mudança de uma propriedade segura no banco de dados?

Suponha que haja uma tabela com índice clusterizado

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

e alguns dados

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;

Ao inspecionar as estatísticas de armazenamento desta tabela

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;

alguem pode ver

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

essa tabela ocupa 166659 páginas no total.

No entanto, a varredura da tabela

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

produz

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

número quase três vezes maior de leituras lógicas em relação ao espaço ocupado pela tabela. Quando examinei o plano de consulta, notei que o SqlServer usava a varredura de índice paralela. E é aqui que surge a primeira parte da questão.

Como a varredura de índice paralela é executada para fazer com que o SqlServer faça tantas leituras lógicas?

Especificando option (maxdop 1)para suprimir o paralelismo

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;

resultou em

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

A comparação de estatísticas para varredura de índice paralela e não paralela neste caso leva à conclusão de que às vezes é melhor evitar a varredura de índice paralela. E é aqui que surge a segunda parte da questão.

Quando devo me preocupar com a varredura de índice paralelo? Quando deve ser evitado/suprimido? quais são as melhores práticas?

Os resultados acima são obtidos em

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

Suponha que haja um certificado criado no banco de dados

create certificate certName
    with subject = 'subj';
GO

E um usuário mapeado para este certificado

create user userName
    from certificate certName;
GO

Tentando representar este usuário diretamente

execute as user = 'userName';
GO

ou especificando o usuário na execute ascláusula do módulo

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

retorna erro

Msg 15517, Nível 16, Estado 1...
Não é possível executar como principal do banco de dados porque o principal "userName" não existe, esse tipo de principal não pode ser representado ou você não tem permissão.

No entanto, não consegui encontrar essa restrição mencionada na documentação ( aqui e aqui ), onde a única declaração relevante parece ser

user_name deve existir no banco de dados atual e deve ser uma conta singleton. user_name não pode ser um grupo, função, certificado, chave ou conta integrada, como NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService ou NT AUTHORITY\LocalSystem.

É possível representar o usuário (ou login) mapeado para o certificado ou não?