Semana gratuita de Noções básicas sobre ajuste de consulta: Parte 3, Como os parâmetros influenciam os planos em cache

Semana gratuita de Noções básicas sobre ajuste de consulta: Parte 3, Como os parâmetros influenciam os planos em cache

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


/ *

Fundamentos do ajuste de consulta: como os parâmetros afetam os planos

v1.0 – 2019-06-30

https://www.BrentOzar.com/go/queryfund

Esta demonstração requer:

* Qualquer versão suportada do SQL Server

* Qualquer banco de dados Stack Overflow: https://www.BrentOzar.com/go/querystack

Este primeiro RAISERROR é apenas para garantir que você não acerte F5 acidentalmente.

execute o script inteiro. Você não precisa executar isso:

* /

RAISERROR(N‘Opa! Não, nãobasta pressionar F5. Execute essas demos uma de cada vez., 20, 1) COM REGISTRO;

IR

/ * Estou usando o banco de dados Stack médio de 50 GB: * /

USAR StackOverflow2013;

IR

/ * E este procedimento armazenado descarta todos os índices não clusterizados: * /

DropIndexes;

IR

/ * No entanto, deixa os índices agrupados no lugar.

Crie alguns índices para apoiar nossas consultas: * /

CRIO ÍNDICE IX_Localização EM dbo.Comercial(Localização);

CRIO ÍNDICE IX_UserId EM dbo.Comentários(ID do usuário);

CRIO ÍNDICE IX_CreationDate EM dbo.Comentários(Data de criação);

IR

CONJUNTO ESTATISTICAS IO EM;

/ *

Começaremos com a mesma consulta que usamos no primeiro módulo.

Ative os planos de consulta reais e execute o seguinte:

* /

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = ‘Helsinki, Finlândia’

E c.Data de criação ENTRE ’01/08/2013′ E ’30/08/2013′

ORDEM POR c.Ponto DESC;

IR

/ *

Coisas para pensar:

* Qual tabela foi processada primeiro? E em segundo lugar?

* Como acessamos essas tabelas?

Vamos tentar um local mais popular: Índia.

* /

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = ‘Índia’

E c.Data de criação ENTRE ’01/08/2013′ E ’30/08/2013′

ORDEM POR c.Ponto DESC;

IR

/ *

Isso mudou:

* A forma do plano?

* Nossas estimativas de linha em cada tabela?

* A memória concede?

* Como o SQL Server escolheu acessar cada tabela?

Vamos tentar mais um local: Estados Unidos.

* /

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = ‘Estados Unidos’

E c.Data de criação ENTRE ’01/08/2013′ E ’30/08/2013′

ORDEM POR c.Ponto DESC;

IR

/ *

Pontos a considerar ao mudar de local:

* Quantos planos diferentes vimos até agora?

* Quantos planos podem existir para todos os locais diferentes?

* Existe talvez um plano que funcione bem para todos?

* Temos alguns dados discrepantes que precisam de planos diferentes?

Leia Também  Como fazer backup e restaurar bancos de dados MySQL usando o comando mysqldump

Vamos ver os principais locais:

* /

SELECT TOPO 100 Localização, CONTAGEM(*) COMO recs

A PARTIR DE dbo.Comercial

GRUPO POR Localização

ORDEM POR CONTAGEM(*) DESC;

IR

SELECT CONTAGEM(*) A PARTIR DE dbo.Comercial ONDE Localização = ‘Helsinki, Finlândia’;

IR

/ *

E estamos apenas mudando a localização – nem mesmo as datas!

Vamos facilitar nossos testes criando um procedimento armazenado:

* /

CRIO OU ALTERAR PROC dbo.usp_SearchComments

@Localização NVARCHAR(200),

@Data de início DATA HORA,

@Data final DATA HORA COMO

INÍCIO

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = @Localização

E c.Data de criação ENTRE @Data de início E @Data final

ORDEM POR c.Ponto DESC;

FIM

IR

/ *

Normalmente, você não precisa limpar o cache do plano, mas isso ajudará na

algo que estou prestes a mostrar em um minuto:

* /

DBCC FREEPROCCACHE;

IR

EXEC usp_SearchComments ‘Índia’, ’01/08/2013′, ’30/08/2013′;

IR

EXEC usp_SearchComments ‘Helsinki, Finlândia’, ’01/08/2013′, ’30/08/2013′;

IR

/ *

Quando você tem uma consulta reutilizável e parametrizada, o SQL Server cria o plano

com base no primeiro conjunto de parâmetros que são usados.

Isso é chamado de sniffing de parâmetro.

O primeiro conjunto de parâmetros é detectado e usado para criar o plano em cache.

Você pode ver os planos em cache:

* /

SELECT TOPO 100 * A PARTIR DE sys.dm_exec_query_stats;

SELECT * A PARTIR DE sys.dm_exec_query_plan(0x05000400A8F9B90D709D72C38901000001000000000000000000000000000000000000000000000000000000);

IR

/ *

Ou da maneira moderna – sp_BlitzCache do nosso kit de código aberto First Responder:

http://FirstResponderKit.org

Desative os planos reais antes de executar isso, porque você não deseja ver o

planos espetacularmente grandes envolvidos na análise de suas consultas:

* /

EXEC sp_BlitzCache;

IR

/ *

Veja a coluna “Parâmetros de execução em cache” em sp_BlitzCache. Isto é

construído com o primeiro conjunto de parâmetros do plano, o conjunto compilado.

Esses eventos (e outros) podem fazer com que o plano em cache desapareça:

* Reiniciando o SQL Server

* DBCC FREEPROCCACHE

* Reconstruindo índices em tabelas na consulta

* Atualizando estatísticas sobre tabelas na consulta

Vamos fazer um desses:

* /

ALTERAR MESA dbo.Comercial RECONSTRUIR;

IR

/ * E tente a consulta novamente, mas desta vez execute Helsinque primeiro: * /

EXEC usp_SearchComments ‘Helsinki, Finlândia’, ’01/08/2013′, ’30/08/2013′;

IR

EXEC usp_SearchComments ‘Índia’, ’01/08/2013′, ’30/08/2013′;

IR

/ *

Quando você tem uma consulta lenta:

* Se não tiver parâmetros, é fácil investigar.

* Se ele possui parâmetros, e SEMPRE é lento, também é fácil.

* Se tiver parâmetros, e algumas vezes é lento, isso é realmente complicado.

Leia Também  Usando o tSQLt para desenvolvimento de data warehouse orientado a testes (TDWD)

Não vou entrar em detalhes sobre o parâmetro sniffing aqui, mas vou

dar-lhe alguns recursos sobre como identificar quando está acontecendo, como reagir

parametrizar farejar emergências e como ajustar suas consultas para que sejam menos

suscetível à detecção de parâmetros:

Vídeo gratuito sobre a detecção de parâmetros: https://BrentOzar.com/go/sniff

Artigo longo: Lento no aplicativo, rápido no SSMS por Erland Sommarskog

http://www.sommarskog.se/query-plan-mysteries.html

Também coberto na minha aula de treinamento de três dias, Mastering Query Tuning:

https://www.brentozar.com/product/mastering-query-tuning/

A razão pela qual estou mencionando o parâmetro sniffing, no entanto, é que eu só preciso de você

para entender que você pode ajustar uma consulta para que ela funcione bem com alguns

parâmetros, mas não outros.

Quando você tem uma consulta orientada a parâmetros, precisa:

* Colete um conjunto de parâmetros para ajuste:

* Normalmente chamados de usuários preocupados com

* Outliers com conjuntos de dados muito pequenos

* Outliers com conjuntos de dados muito grandes

* Outliers onde o SQL Server estima linhas incorretamente

* Armado com isso, então você:

* Ajuste índices ou consultas para que um plano funcione melhor para todos, ou

* Execute a consulta com cada conjunto de parâmetros, obtendo seu plano

* Medir o desempenho dos diferentes planos com diferentes entradas

Para obter o conjunto de parâmetros comuns, você pode:

* Pergunte aos usuários

* Consultar as tabelas (procurando outliers)

* Verifique o cache do plano (mas estes são apenas os parâmetros compilados)

* Execute um rastreamento do Profiler ou uma sessão de Eventos Estendidos, capturando-os enquanto eles são executados

Ou altere seu código para registrá-los temporariamente:

* /

CRIO MESA dbo.Debug_ParameterLog

(Eu iria INT IDENTIDADE(1,1) PRIMARY CHAVE CLUSTERED,

ProcName NVARCHAR(200),

RunDate DATETIME2 PADRÃO GETDATE(),

Params NVARCHAR(MAX));

IR

CRIO OU ALTERAR PROC dbo.usp_SearchComments

@Localização NVARCHAR(200),

@Data de início DATA HORA,

@Data final DATA HORA,

@Debug_ParameterLog MORDEU = 0 0 COMO

INÍCIO

E SE @Debug_ParameterLog = 1

INSERIR PARA DENTRO dbo.Debug_ParameterLog (ProcName, Params)

VALORES (‘usp_SearchComments’,

N‘@ Localização = N’ + @Localização + ‘,’ +

N‘@ StartDate = N’ + FUNDIDA(@Data de início COMO NVARCHAR(30)) + N‘,’ +

N‘@ EndDate = N’ + FUNDIDA(@Data final COMO NVARCHAR(30)) + N‘;’);

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = @Localização

E c.Data de criação ENTRE @Data de início E @Data final

ORDEM POR c.Ponto DESC;

FIM

IR

Leia Também  Por que o pedido não é garantido sem uma ordem?

/ * Normalmente, você deixaria @Debug_ParameterLog = 0.

Mas quando você precisar começar a capturar parâmetros, basta alterar o padrão para 1: * /

CRIO OU ALTERAR PROC dbo.usp_SearchComments

@Localização NVARCHAR(200),

@Data de início DATA HORA,

@Data final DATA HORA,

@Debug_ParameterLog MORDEU = 1 COMO

INÍCIO

E SE @Debug_ParameterLog = 1

INSERIR PARA DENTRO dbo.Debug_ParameterLog (ProcName, Params)

VALORES (‘usp_SearchComments’,

N‘@ Localização = N’ + @Localização + ‘,’ +

N‘@ StartDate = N’ + FUNDIDA(@Data de início COMO NVARCHAR(30)) + N‘,’ +

N‘@ EndDate = N’ + FUNDIDA(@Data final COMO NVARCHAR(30)) + N‘;’);

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = @Localização

E c.Data de criação ENTRE @Data de início E @Data final

ORDEM POR c.Ponto DESC;

FIM

IR

EXEC usp_SearchComments ‘Helsinki, Finlândia’, ’01/08/2013′, ’30/08/2013′;

IR

EXEC usp_SearchComments ‘Índia’, ’01/08/2013′, ’30/08/2013′;

IR

SELECT * A PARTIR DE dbo.Debug_ParameterLog;

/ *

Acertar seus parâmetros pode ser complicado aqui, e você pode tecnicamente

pressione injeção SQL – então é claro que você não gostaria apenas de pegar os parâmetros

e execute-os como estão.

Isso é deixado como um exercício para o leitor.

Falando em exercícios para o leitor, vamos fazer um!

Esta consulta estava no cache do seu plano como uma das mais intensivas em recursos:

* /

CRIO OU ALTERAR PROC dbo.usp_CommentBattles

@UserId1 INT,

@UserId2 INT COMO

INÍCIO

COM Batalhas COMO (SELECT c1.PostId, c1.Ponto COMO User1Score, c2.Ponto COMO User2Score

A PARTIR DE dbo.Comentários c1

INTERIOR JUNTE-SE dbo.Comentários c2 EM c1.PostId = c2.PostId E c1.Eu iria <> c2.Eu iria

ONDE c1.ID do usuário = @UserId1

E c2.ID do usuário = @UserId2

)

SELECT User1Victories = COALESCE(SOMA(CASO QUANDO b.User1Score > b.User2Score ENTÃO 1 OUTRO 0 0 FIM),0 0),

User2Victories = COALESCE(SOMA(CASO QUANDO b.User1Score < b.User2Score ENTÃO 1 OUTRO 0 0 FIM),0 0),

Empates = COALESCE(SOMA(CASO QUANDO b.User1Score = b.User2Score ENTÃO 1 OUTRO 0 0 FIM),0 0)

A PARTIR DE Batalhas b;

FIM

IR

/ * Sua missão: encontre valores externos para @ UserId1 e @ UserId2. * /

/ *

Licença: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)

Mais informações: https://creativecommons.org/licenses/by-sa/3.0/

Você é livre para:

* Compartilhar – copie e redistribua o material em qualquer meio ou formato

* Adapte – remixe, transforme e desenvolva o material para qualquer finalidade, inclusive

comercialmente

Sob os seguintes termos:

* Atribuição – você deve dar o crédito apropriado, fornecer um link para a licença,

e indicar se foram feitas alterações.

* ShareAlike – Se você remixar, transformar ou desenvolver o material, deverá

distribua suas contribuições sob a mesma licença que o original.

* /

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br