SEarle1986's questions

Se eu tiver a seguinte consulta no banco de dados Stack Overflow2010

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

A saída resumida STATISTICS IO está abaixo

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)

e o plano de execução está aqui

Se eu criar uma tabela para armazenar o valor que desejo usar na minha comparação:

CREATE TABLE dbo.Canary
(
    TheDate DATETIME
)

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

e então se eu alterar a consulta da seguinte forma:

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

A saída 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)

e o plano de execução está aqui

Podemos ver que o número de leituras lógicas aumentou massivamente, de 445k para 16m. Levei algum tempo para encontrar no plano de execução onde está a fonte disso, mas eu o rastreei usando a propriedade Estatísticas de E/S reais / Leituras lógicas reais e posso ver que as leituras extras estão no operador de atualização de índice clusterizado, também posso ver que esse operador agora tem um valor para Número real de linhas para todas as execuções, enquanto o plano para a primeira consulta não tem.

O que está acontecendo aqui? O que está acontecendo na atualização do índice clusterizado que está causando o aumento nas leituras?

Acredito que a consulta deve usar uma variável se esse padrão de "valor em uma tabela de configuração" for usado. No entanto, esta é uma consulta de um aplicativo de fornecedor, então quero dar um feedback sobre o que está acontecendo como resultado da consulta ter sido escrita da maneira que foi.

Tenho um banco de dados em uma instância do SQL Server 2019 que tem o armazenamento de consultas habilitado e já faz isso há muitos anos

De repente, o estado do repositório de consultas retornou por

SELECT actual_state_desc FROM sys.database_query_store_options

foi para

ERRO

então eu reativei

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

e tudo estava bem. No entanto, algumas horas depois, a mesma coisa aconteceu e notei a seguinte mensagem no log do SQL Server na mesma hora

Erro interno FILESTREAM: falha ao acessar a tabela de coleta de lixo.

Parece que os dois estão relacionados, embora eu não tenha certeza de como exatamente.

Nosso banco de dados não possui nenhum arquivo filestream:

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

retorna 0

Reativei o armazenamento de consultas mais uma vez, mas gostaria de entender o que está acontecendo aqui para evitar que isso aconteça novamente no futuro.

Alguém pode me dizer o que está acontecendo e como posso consertar?

Dado o seguinte trabalho do SQL Server Agent

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

E o seguinte código de chamada

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

Recebo o seguinte erro:

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.

Então podemos ver que a segunda sp_start_jobchamada falhou porque o trabalho ainda estava em execução, então o SQL Server continuou a execução

a falha é nível 16 e quando eu faço o seguinte

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

Recebo o mesmo comportamento de "falhar e continuar"

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

no entanto, se eu fizer o seguinte, o que também gera um erro de nível 16, a segunda impressão não será executada

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

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

Os documentos para o estado THROW

Qualquer erro que ocorra em uma instrução THROW faz com que o lote de instruções seja encerrado.

Os documentos para o estado RAISERROR

O erro é retornado ao chamador se RAISERROR for executado: Fora do escopo de qualquer bloco TRY. Com uma gravidade de 10 ou menor em um bloco TRY. Com uma gravidade de 20 ou maior que encerra a conexão com o banco de dados.

Minha pergunta é: sp_start_jobusar RAISERROR em vez de throw - Eu olhei a definição e não consigo ver se em algum lugar

Digamos que eu tenha os dois bancos de dados a seguir

USE [master]
CREATE DATABASE Jack
GO

CREATE DATABASE Jill
GO

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

Se eu criar o seguinte procedimento armazenado temporário no contexto do banco de dados Jack:

USE Jack
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable
GO

e então execute no banco de dados Jack:

USE Jack
GO
EXEC #Temp

Eu obtive o resultado1

Se eu então executar no contexto do banco de dados Jill:

USE Jill
GO
EXEC #Temp

Eu entendo1

Agora, se eu abandonar o procedimento

DROP PROC #Temp

e crie o procedimento novamente no contexto do banco de dados Jill:

USE Jill
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable 
GO

e execute-o

USE Jill
GO    
EXEC #Temp

Recebo um erro

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

Se eu tentar executá-lo a partir do banco de dados Jack:

USE Jack
GO
EXEC #Temp

Eu entendo

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

Se eu então abandonar o procedimento

DROP PROC #Temp

Recrie novamente no contexto do banco de dados Jill

USE Jill
GO

CREATE PROC #Temp
AS
    SELECT  *
    FROM    JacksTable 
GO

e desta vez executado a partir do banco de dados Jack

USE Jack
GO
EXEC #Temp

Recebo o mesmo erro de ligação

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

isso me diz que o SQL Server tenta vincular os objetos no procedimento temporário ao banco de dados que estava no contexto quando o procedimento armazenado temporário foi criado.

este artigo sugere

Quando um procedimento armazenado é executado pela primeira vez, o processador de consulta lê o texto do procedimento armazenado na exibição do catálogo sys.sql_modules e verifica se os nomes dos objetos usados ​​pelo procedimento estão presentes.

Em qual banco de dados essa verificação é realizada? Eu diria que seria tempdb, pois esse é o banco de dados no qual a definição de procedimentos armazenados temporários é persistida? Se for, essa resolução falharia em todos os casos (a menos que criássemos as tabelas em tempdb), então não pode ser isso.

Com base no que vi acima, parece ser o banco de dados que estava no escopo quando o procedimento armazenado temporário foi criado (em vez do banco de dados em que a definição do objeto é persistida - tempdb). No entanto, se esse for realmente o caso, como o SQL Server sabe qual banco de dados é esse e onde ele está armazenado? Eu verifiquei sys.sql_expression_dependencies, sys.sql_modules, sys.objectse sys.procedurese nenhum parece ter essa informação

Dado o banco de dados StackOverflow2010, criei algumas chaves estrangeiras para explorar a eliminação de junções:

Chave Estrangeira 1 - Todos os comentários devem ter uma postagem

Excluir comentários sem uma postagem

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

crie a chave estrangeira

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

Agora podemos executar a consulta abaixo e ver a eliminação da junção:

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

o plano está aqui e mostra como eu esperaria, que apenas a tabela de comentários é lida

Chave Estrangeira 2 - Todas as postagens devem ter um usuário proprietário

Ao contrário do que foi dito acima, se eu redefinir as coisas e tentar com a seguinte chave estrangeira, não vejo a eliminação da junção:

Excluir postagens sem um OwnerUser

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

Crie a chave estrangeira

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

Execute minha consulta

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

o plano mostra que o SQL Server acessa ambas as tabelas.

Por que a chave estrangeira 2 não se beneficia da eliminação de junção, mas a chave estrangeira 1 sim?

Estou ajustando uma consulta que está lenta, reduzi a raiz do problema para estar bem no início do plano de execução, onde o SQL Server faz uma estimativa incorreta em um filtro WHERE IS NULL que suporta uma anti-junção à esquerda - SQL O servidor estima 1 linha e favorece algumas varreduras de índice através de um loop aninhado, pensando que só as executará uma vez, quando na verdade isso acontece milhares de vezes:

Consegui criar um MCVE para replicar o problema.

Configurar o ambiente de teste

/* 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

Então, a consulta que sofre o problema é

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

O que é bastante simples "mostre-me todos os IDs na Tabela A que não estão na Tabela B"

O plano de execução do meu ambiente de teste está aqui

Podemos ver algo muito semelhante acontecendo como vemos no plano acima, em termos do operador de filtro - o SQL Server une as duas tabelas e depois filtra os registros que estão na tabela da esquerda, mas não na tabela da direita e isso subestima enormemente o número de linhas que correspondem a esse predicado

Se eu forçar a estimativa herdada, obterei uma estimativa muito melhor do operador

Acredito que uma das principais diferenças entre o estimador antigo e os novos estimadores é como eles diferem na suposição da correlação entre dois predicados - o antigo assume que há pouca correlação entre dois predicados, enquanto o novo estimador é mais otimista e assume uma maior correlação?

Minhas perguntas são

• O que causa essa subestimação no estimador de cardinalidade mais recente?
• Existe uma maneira de consertar isso além de forçar o modelo de compatibilidade mais antigo?

Dadas as seguintes tabelas:

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')

Se eu executar a seguinte consulta;

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

O SQL Server precisa executar uma conversão implícita, pois #a.MyIntsão #b.MyVarchartipos de dados incompatíveis. Devido à precedência do tipo de dados, a coluna com a precedência de tipo mais baixa (#b.MyVarchar) é convertida para o tipo de precedência mais alta (INT)

Isso significa que a consulta acima se torna equivalente a

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

e ambos falham porque há um valor #b.MyVarcharinválido para uma INTcoluna.

Minha pergunta é por que VARCHARtem uma precedência menor que INT? se fosse o contrário, a conversão implícita seria bem-sucedida, mas em vez disso obteríamos uma consulta com erros. Por que um erro seria preferido a uma execução bem-sucedida? Meu palpite seria algo como este ser mais um mecanismo "defensivo" por parte do SQL Server - ele prefere um erro e, portanto, precisa que o usuário decida explicitamente o que deseja fazer, em vez de fornecer resultados de consulta talvez inesperados, sem que o usuário seja Cientes?

Temos uma configuração de espelhamento de banco de dados entre PRODe SECONDARYe fizemos failover para a réplica secundária ( SECONDARY). Agora estamos recebendo uma série de logins com falha quase constantes exibidos no log do servidor PROD.

Os aplicativos usam a propriedade Failover Partner da cadeia de conexão:

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

e confirmamos que eles estão funcionando conforme o esperado.

Faz sentido que o banco de dados especificado explicitamente PRODnão possa ser aberto, pois está em um estado de restauração devido ao failover, mas o aplicativo está se conectando com êxito ao SECONDARY. Esta mensagem de erro é o comportamento esperado em uma réplica primária de espelhamento de banco de dados com falha?

Abaixo replica o problema usando um cenário simples baseado em ODBC

Configure um banco de dados espelhado:

crie um login no primário

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

e o secundário:

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

Configure um DSN:

Configure uma tabela vinculada em um banco de dados Access

Faça failover do banco de dados para o secundário:

ALTER DATABASE Mirroring SET PARTNER FAILOVER

Abra a tabela vinculada do Access e verifique se podemos ver os dados após o failover:

Observe o erro em .\PROD

Posso criar a seguinte restrição na tabela Person.Person do AdventureWorks:

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

Isso informa ao SQL Server que nenhum LastName pode ter o valor deDoesn't Exist

O Optimizer usa isso a seu favor na seguinte consulta simples:

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

Como a restrição diz ao otimizador que nada na coluna pode ser igual ao valor que estamos procurando por igualdade (assumindo uma restrição confiável), o otimizador apenas executa uma varredura constante e não faz "nada"

Se eu eliminar a restrição acima e criar uma ligeiramente diferente:

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

e execute uma consulta com um predicado cujos resultados violariam a restrição de verificação:

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

Obtemos uma busca de índice com uma pesquisa de chave

No entanto, se eu correr

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

com apenas a restrição original no lugar:

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

Mais uma vez, recebo a verificação constante

Por que não podemos obter uma varredura constante ao executar a consulta com dois predicados quando a restrição proíbe seus resultados? Estou certo ao assumir que isso é apenas uma limitação da funcionalidade do otimizador?

Estou tentando entender como o SQL Server estima a cardinalidade na consulta de banco de dados Stack Overflow abaixo

Em primeiro lugar, crio o índice

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

E aqui está a consulta:

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

O plano de execução está aqui

Em primeiro lugar, o SQL Server verifica o índice Clustered na tabela de usuários para retornar os usuários que correspondem ao predicado AccountId. Eu posso ver que ele usa esta estatística:_WA_Sys_0000000E_09DE7BCC

Eu posso ver que este usuário não tem uma chave alta de intervalo, então o SQL Server usa as linhas avg_range e estima 1

O predicado seek no índice de comentários seek é

representa Scalar Operator([StackOverflow2010].[dbo].[Users].[Reputation] as [u].[Reputation]o valor da reputação do(s) usuário(s) na tabela de usuários com o accountId de22547

Eu posso ver três estatísticas carregadas no total:

_WA_Sys_0000000E_09DE7BCC- Users.AccountId (usado para estimar o predicado de busca do índice clusterizado)

IX_PostId- Comments.PostId (usado para estimar o predicado de busca do índice)

_WA_Sys_0000000A_09DE7BCC- Usuários.Reputação (?)

como o servidor SQL apresenta a estimativa na busca do índice? Ele não pode saber a reputação de accountId 22547no tempo de compilação, pois a estatística de ID da conta não mostra isso, portanto, não pode realizar uma pesquisa no histograma para IX_PostId. Eu posso ver que a estatística de reputação também é carregada, então ela usa os dois de alguma forma?

Esta consulta foi executada contra CE 150

Temos SSL ativado em nossos servidores SQL e nossa ferramenta de monitoramento está alertando que existem conexões não criptografadas.

A verificação que monitora isso é executada a cada minuto e usa a seguinte consulta para verificar:

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

Fiz uma investigação mais aprofundada, configurando um trabalho de agente para despejar os resultados da seguinte consulta em uma tabela (o trabalho é executado a cada minuto)

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'

Se eu selecionar na minha tabela, vejo alguns resultados (espalhados em três capturas de tela devido ao amplo conjunto de resultados):

Para mim, sys.dm_exec_connectionsnão dá muito para continuar aqui. A única coisa que parece particular a essas conexões é que elas são todas endpoint 2 ( TSQL Local Machine) e têm um auth_schema de(unknown)

Posso excluí-los do monitoramento com base nisso, mas gostaria de ter certeza de que são benignos antes de fazer isso.

Quais são essas conexões? Por que eles não são criptografados quando o SSL está ativado? Existe alguma informação adicional que eu poderia olhar para entendê-los melhor?

Eu forcei um plano no armazenamento de consultas da seguinte forma

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

mas quando executo a consulta novamente, a consulta não usa o plano, nem mostra umfailure_force_reason

A consulta a seguir mostra que o plano foi forçado e não sugere nenhuma falha em forçar na última execução

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

A consulta a seguir mostra o último tempo de execução da consulta em questão, isso me confirma que foi realmente essa consulta que executei novamente e que usou um plano diferente daquele que forcei:

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

Por que o query store parece ignorar essa força de plano? Existem eventos estendidos ou outras ferramentas de solução de problemas que eu possa aproveitar para entender?

Outro dia me perguntaram o que aconteceria se o SQL Server quisesse executar uma única consulta que recebesse mais memória do que a disponível para a instância. Meus pensamentos iniciais eram de que eu poderia ver RESOURCE_SEMAPHOREesperas e a consulta nunca seria iniciada.

Fiz alguns testes para tentar descobrir.

Minha instância inicia em 4000 MB de RAM:

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

Se eu executar minha consulta (deliberadamente horrível):

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;

O plano de execução diz que a memória concedida é de 732.008 KB.

Em seguida, defino a memória disponível para minha instância abaixo desse número e reinicio a instância:

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

Executei a consulta novamente e descobri que ela recebe menos memória do que antes (93.176 KB), mas o plano é, na verdade, uma forma diferente.

Em seguida, executei a consulta novamente e usei uma dica de consulta para forçar o plano original a ver qual memória é concedida:

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>'

Descobri que a consulta agora usa o plano original, mas obtém uma concessão de memória muito semelhante ao plano compilado (93.168 KB) - plano real forçado

Isso parece refutar minha teoria de que eu veria RESOURCE_SEMAPHOREesperas e sugere que há algum mecanismo que impede o SQL Server de conceder mais memória do que está disponível para uma consulta (o que parece perfeitamente sensato!) Aliás, se eu executar a consulta duas vezes em sessões simultâneas no Configuração de servidor de 500 MB, ambas as sessões RESOURCE_SEMAPHOREaguardam o que parece indefinidamente.

Eu posso ver MaxQueryMemoryno plano que parece ser o que está impedindo o SQL Server de conceder mais memória do que tem disponível para uma consulta.

É possível que uma única consulta receba mais memória do que a disponível para a instância? Se não, MaxQueryMemoryo que faz com que o SQL Server não atribua mais memória do que a disponível e como esse número é calculado?

NB - Meu banco de dados StackOverflow está no nível de compatibilidade 130

Eu tenho uma consulta para a qual forço um plano no Query Store (o plano é o SQL Server compilado para esta consulta) Se eu executar a consulta imediatamente após forçar o plano, recebo o NO_PLANlast_force_failure_reason_desc apesar de nenhuma alteração no banco de dados. Posso forçar um plano diferente para a mesma consulta com sucesso

O problema pode ser ilustrado abaixo:

Crie nosso banco de dados de teste

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

Insira alguns dados fictícios:

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

Agora que o banco de dados está configurado, execute a consulta:

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 - o plano de execução real está aqui

use os DMVs do repositório de consultas para obter o ID da consulta e o ID do plano para que possamos forçar o plano:

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 */

Minha saída está abaixo:

Agora force o SQL Server a usar o plano que acabou de compilar, cada vez que executar esta consulta

EXEC sp_query_store_force_plan @query_id = 6, @plan_id = 6

Execute a consulta novamente:

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

Verifique os DMVs do repositório de consultas para ver se ele usou o plano:

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 */

We can see a failure reason of NO_PLAN:

If I reset things by truncating the table, clearing query store and then adding only 20 rows to the table (or dropping the database and running all the above setup above but use GO 20 rather than 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

then once again run the query, I get a different plan (note the filter operator placement has changed)

If I then repeat the process of getting the query_id and plan_id, forcing the plan and re-running the query, this time it will force the plan:

I can confirm that NO_PLAN plan cannot be forced with OPTION (RECOMPILE, USE PLAN N'<planxmlhere>') hints, I get

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?

Eu tenho uma consulta no repositório de consultas para a qual forcei um plano.

Posso confirmar que o plano é forçado com

SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1

e o plano aparece nos resultados

No entanto, se eu olhar para a last_force_failure_reason_desccoluna, vejoNO_PLAN

Algumas pesquisas no Google me levaram aos seguintes artigos:

Kendra Little

Deepthi Goguri

que ambos sugerem que a alteração dos índices que são usados ​​pelo plano é a causa do motivo da NO_PLANfalha.

Configurei a sessão de eventos estendidos no segundo artigo:

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

e posso ver os eventos do plano em questão, que possuem o seguinte texto:

O processador de consulta não pôde produzir o plano de consulta porque a dica USE PLAN contém um plano que não pôde ser verificado como válido para a consulta. Remova ou substitua a dica USE PLAN. Para maior probabilidade de forçar o plano bem-sucedido, verifique se o plano fornecido na dica USE PLAN é gerado automaticamente pelo SQL Server para a mesma consulta

A consulta é executada todas as noites como parte de uma construção de data warehouse onde os comandos DDL são comuns, então decidi configurar uma especificação de Auditoria de Banco de Dados para capturar SCHEMA_OBJECT_CHANGE_GROUPtipos de ação para ver se algum índice estava sendo alterado

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

Quando eu interrogar os resultados da seguinte forma:

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')

Eu posso ver todas as alterações para as tabelas na cláusula IN (quais são as tabelas que são selecionadas na consulta quem é o plano que estou tentando forçar)

Tudo o que posso ver são quedas e recriações de chaves estrangeiras que são bastante normais para nosso data warehouse. As chaves estrangeiras são recriadas com o mesmo nome que tinham quando foram descartadas. A sequência de eventos é

1. Existem restrições (não confiáveis)
2. As restrições são eliminadas
3. Execuções de consulta
4. Restrições recriadas (mesmo nome e NOCHECK)

O plano que foi forçado é aquele que foi gerado pelo processador de consultas no ponto 3 acima, então como a sequência de eventos é a mesma todas as noites, eu teria pensado que a mudança de restrições é irrelevante?

Fui mais longe e descobri em quais colunas as chaves estrangeiras que foram descartadas / recriadas estão:

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')

e nenhum deles são colunas em qualquer um dos índices não clusterizados usados ​​no query_plan

Tentei recriar um exemplo em um banco de dados AdventureWorks2016 em que forço um plano que executa uma busca NCI em uma coluna que possui uma chave estrangeira confiável e, em seguida, observo que o otimizador ainda usa o plano, apesar da chave estrangeira ser descartada e ainda usa quando é recriado não confiável:

/* 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

Sim!

/* 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

Sim!

O que está causando os NO_PLANerros? Tem algo a ver com a eliminação/criação de restrições de chave estrangeira?

Usando o banco de dados StackOverflow2010, posso criar um índice na tabela de usuários da seguinte forma:

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

E, em seguida, execute uma pesquisa de desigualdade na chave do índice:

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

eu pego o plano aqui

Estou tentando descobrir como o SQL Server conseguiu obter os resultados dessa consulta.

O plano começa com algumas varreduras constantes, mas a lista de saída está em branco, então não está claro para mim para que servem.

Cada varredura constante então entra em um escalar de computação, cada uma das quais saída

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

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

o operador concatenar então parece concatenar algumas das saídas acima:

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

Mas tem entradas que não consigo ver em nenhum lugar do plano (Expr 1008 e Expr1009)

Também não sei por que a classificação TOP N é necessária

A busca do Índice faz sentido - ela está procurando por > Expr1011 e < Expr1012. Eu diria que isso é basicamente algo como

>= 'a' AND < 'Alex' 

ou

> 'Alex' AND <= 'zzzzzzzzzzzzzz'

ou similar.

Alguém pode me explicar passo a passo, como está funcionando esse plano e como posso entender os valores de Expr1011 e Expr1012 (usados ​​na busca do índice) que são produzidos pelo operador de concatenação

Tenho feito alguns testes para tentar entender melhor como o SQL Server usa um histograma para estimar o número de linhas que corresponderão a um predicado de igualdade e também a um predicado < ou >

Dado que estou usando o banco de dados OLTP AdventureWorks2016

Se puder entender o processo de estimativa do SQL Server para = e > predicados:

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

Então eu posso ver o histograma para a colunaTransactionHistory.Quantity

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

A captura de tela abaixo é a extremidade superior do histograma onde executei meus testes:

A consulta a seguir estimará 6 linhas, pois o valor no predicado é um RANGE_HI_KEY, portanto, use o EQ_ROWS para esse bucket:

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity = 2863

O seguinte estimará 1,36 linhas, pois não é um RANGE_HI_KEY, portanto, usa o AVG_RANGE_ROWS para o bucket em que se enquadra:

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity = 2862

A seguinte consulta "maior que" estimará 130 linhas que parecem ser a soma de RANGE_ROWS e EQ_ROWS para todos os buckets com RANGE_HI_KEY > 2863

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity > 2863

Uma consulta semelhante abaixo, mas o valor não é um RANGE_HI_KEY no histograma. SQL Server novamente estima 130 e parece usar o mesmo método acima

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity > 2870 

Tudo isso faz sentido até agora, então meu teste mudou para uma consulta "menor que"

SELECT  * 
FROM    Production.TransactionHistory
WHERE   Quantity < 490 

para esta consulta, o SQL Server estima 109.579 linhas, mas não consigo descobrir de onde veio isso:

RANGE_HI_KEY + RANGE_ROWS de todos os buckets até e incluindo RANGE_HI_KEY 470 = 109.566, então temos 11 curtos em algum lugar.

Como o SQL Server usa o histograma para estimar o número de linhas que serão retornadas por um predicado "menor que"

Dado que estou usando o banco de dados OLTP AdventureWorks2016 , por que o histograma de estatísticas para o índice PK_TransactionHistory_TransactionIDna tabela Production.TransactionHistorycontém apenas 3 "buckets" de histograma quando há 113k valores distintos nessa coluna?

Um exemplo abaixo:

USE AdventureWorks2016

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

então podemos olhar para o histograma atualizado

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

e vejo a saída:

Observe os IDs de transação máximo e mínimo:

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

O SQL Server parece ter criado um "bucket" para o valor máximo, um para o valor mínimo e um para todos os valores intermediários (que ele sabe que são todos distintos)

Observo que se eu remover a chave primária desta tabela

ALTER TABLE Production.TransactionHistory DROP CONSTRAINT PK_TransactionHistory_TransactionID

e, em seguida, insira alguns valores duplicados

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())

Atualize as estatísticas na tabela e, em seguida, observe a estatística da coluna (em vez do PK que excluímos)

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;

Ainda temos dois buckets, embora DISTINCT_RANGE_ROWS tenha sido atualizado de acordo

Por que o SQL Server não faz uso dos 200 "buckets" disponíveis em um histograma aqui? É algo a ver com os recursos necessários para preencher a página de estatísticas de 8 KB e usar todos os 200 buckets significaria que pode ser necessário redefinir quando novos dados são adicionados à tabela?

O limite de custo para a configuração de paralelismo em um de nossos servidores é geralmente considerado muito baixo (15) e estamos considerando aumentar para 50 na esperança de reduzir a CPU, pois ela está se tornando alta.

Gostaria de saber quais consultas serão afetadas para que possamos fazer alguns testes e monitorá-las.

A maneira como fiz isso é consultar o cache do plano (e analisar o XML). Não obstante os problemas de usar o cache do plano (planos envelhecendo / descartados sob pressão de memória, reinicializações do servidor etc.), essa é a melhor maneira de fazer isso?

Minha consulta é

;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 

O código a seguir cria um usuário capaz de restaurar um banco de dados:

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

Isso funciona bem e o usuário pode restaurar o banco de dados, no entanto, se eu quiser definir o banco de dados SINGLE_USERe reverter quaisquer conexões existentes, o comando de restauração será bloqueado e falhará:

Sessão 1

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

Sessão 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

Eventualmente, a sessão 2 expira com

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.

A sp_whoisactivecaptura de tela a seguir mostra que o comando está bloqueado:

O sp_whoisactiverelatório de bloqueio para a sessão 59 mostra

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

O artigo da Microsoft para SET SINGLE_USER afirma

Para obter acesso exclusivo rapidamente, o exemplo de código usa a opção de encerramento WITH ROLLBACK IMMEDIATE. Isso fará com que todas as transações incompletas sejam revertidas e quaisquer outras conexões com o banco de dados AdventureWorks2012 sejam imediatamente desconectadas.

e

Requer permissão ALTER no banco de dados.

Minha RestoreUserconta tem as permissões corretas ( dbcreatora função de servidor fornece ALTER ANY DATABASEpermissões), mas não sei por que o comando está bloqueado, a primeira citação sugere que todas as outras conexões seriam desconectadas.

Eu também concedi ALTER ANY CONNECTION, RestoreUsermas isso não ajudou.

O que encontrei resolveu o problema, é se RestoreUserum usuário no banco de dados está sendo restaurado (não precisa de permissões no banco de dados)

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

USE AdventureWorks2014
CREATE USER RestoreUser

O código

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

Em seguida, é executado sem ser bloqueado pela sessão de suspensão.

Minhas perguntas são

• Por que o SET SINGLE_USER WITH ROLLBACK IMMEDIATEcódigo é bloqueado quando deveria matar e reverter todos os SPIDs abertos
• Por que criar o usuário no banco de dados corrige esse problema?