SP_GETUSEDOBJECTS @SQLTEXT

Fala galera, o nome dessa procedure te chamou atenção? Bom então vamos conhecê-la, nos últimos dias vim brincando com essa procedure para capturar algumas informações de objetos utilizados por uma determinada query, a ideia principal da procedure é receber um comando em TSQL e retornar os objetos utilizados na execução do comando, para isso basta o plano de execução deste comando estar no plan cache do SQL Server.

Particularmente achei muito legal, mas claro que tenho que dar os créditos também ao idealizador e amigo Caio Amante, em um dia normal da vida de DBA, uma nova query surreal e extremamente grande surgiu para tuning, como descobrir quais objetos envolvidos? Quais índices e estatísticas utilizadas? Ah Reginaldo no plano de execução tem tudo isso! Sim realmente essas informações estão lá, então boa sorte rs.

stored-procedure-part-1

A ideia da procedure SP_GETUSEDOBJECTS é de forma simples retornar os objetos utilizados pelo plano de execução, objetos como os banco de dados, esquemas, tabelas, índices e estatísticas, assim como comandos básicos para criar e dropar índices e estatísticas (trechos da SP_SHOWINDEX foram usados aqui).

Chega de história, vamos a prática…

Screen Shot 07-25-17 at 10.27 PM

 

Lista de colunas retornadas:

  • DBNAME – NOME DA BASE DE DADOS EM USO
  • SCHEMANAME – NOME DO SCHEMA DA TABELA
  • TABLENAME – NOME DA TABELA UTILIZADA PELO COMANDO
  • INDEXNAME – NOME DO ÍNDICE UTILIZADO PELO COMANDO
  • STAT_NAME – NOME DA ESTATÍSTICA UTILIZADA NO PLANO DE EXECUÇÃO
  • INDEX_TYPE – TIPO DO ÍNDICE, VALORES PARA ESSA COLUNA: CLUSTERED, NONCLUSTERED E HEAP
  • COLUMNS – COLUNAS QUE PERTENCEM AO INDICE(ESTÃO EM TODOS OS NÍVEIS DO ÍNDICE)
  • INCLUDE_COLUMNS – COLUNAS QUE PERTENCEM AO ÍNDICE(ESTÃO APENAS NO NÍVEL FOLHA DO ÍNDICE)
  • UPDATE_COMMAND – COMANDO PARA ATUALIZAR AS ESTATÍSTICAS COM A OPÇÃO FULL SCAN
  • DROP_COMMAND – COMANDO PARA EXCLUIR O ÍNDICE
  • CREATE COMMAND – COMANDO PARA CRIAR O ÍNDICE

 

Abaixo alguns exemplos de chamadas da procedure:

SP_GETUSEDOBJECTS
@HELP = 0,
@EXEQUERY = 0,
@GETSTATS = 0,
@SQLTEXT = ‘SELECT DBO.FN_MES(MONTH(J.DATA)) MES,APELIDO,SUM(ISNULL(P.PONTOS,0)) PONTOS–,ROW_NUMBER() OVER (ORDER BY DATA) POSITION
FROM TB_JOGOS J
INNER JOIN TB_JOGADORES JG ON J.ID_JOGADOR = JG.ID
LEFT JOIN TB_PONTOS P ON J.POSICAO = P.POSICAO
GROUP BY MONTH(J.DATA),APELIDO
ORDER BY 3 DESC,MONTH(J.DATA),2′
ORDER BY 3 DESC,MONTH(J.DATA),2’

Screen Shot 07-25-17 at 10.48 PM

Essa é a chamada padrão, apenas com o parâmetro @SQLTEXT, onde o plano de execução já deve residir em memória, ou seja, você precisa ter executado a query ao menos uma vez, o comando informado no parâmetro @SQLTEXT deve ser IDÊNTICO ao executado, pois a busca é através do operador ‘=’.

 

SP_GETUSEDOBJECTS
@HELP = 0,
@EXEQUERY = 1,
@GETSTATS = 0,
@SQLTEXT = ‘SELECT DBO.FN_MES(MONTH(J.DATA)) MES,APELIDO,SUM(ISNULL(P.PONTOS,0)) PONTOS–,ROW_NUMBER() OVER (ORDER BY DATA) POSITION
FROM TB_JOGOS J
INNER JOIN TB_JOGADORES JG ON J.ID_JOGADOR = JG.ID
LEFT JOIN TB_PONTOS P ON J.POSICAO = P.POSICAO
GROUP BY MONTH(J.DATA),APELIDO
ORDER BY 3 DESC,MONTH(J.DATA),2’

Screen Shot 07-25-17 at 10.53 PM

Essa chamada com o parâmetro @EXECQUERY, o plano de execução não precisa residir em memória, pois a query será executada antes, em casos onde a otimização para queries ADHOC estiver habilitado deve ser executado mais de uma vez.

 

SP_GETUSEDOBJECTS
@HELP = 0,
@EXECQUERY = 1,
@GETSTATS = 1,
@SQLTEXT = ‘SELECT DBO.FN_MES(MONTH(J.DATA)) MES,APELIDO,SUM(ISNULL(P.PONTOS,0)) PONTOS–,ROW_NUMBER() OVER (ORDER BY DATA) POSITION
FROM TB_JOGOS J
INNER JOIN TB_JOGADORES JG ON J.ID_JOGADOR = JG.ID
LEFT JOIN TB_PONTOS P ON J.POSICAO = P.POSICAO
GROUP BY MONTH(J.DATA),APELIDO
ORDER BY 3 DESC,MONTH(J.DATA),2’

Screen Shot 07-25-17 at 10.57 PM

(Warning) A chamada da procedure com a combinação do parâmetro @EXECQUERY e @GETSTATS aciona o Trace Flag NÃO DOCUMENTADO 8666, esse parâmetro aumentará consideravelmente o custo da execução e pode degradar a performance do ambiente dependendo do tamanho do cache, use esse parâmetro com cuidado e por sua conta e risco. Esse parâmetro traz informações das estatísticas utilizadas no plano de execução, assim como seus comandos para atualizar as estatísticas com a opção FULL SCAN, isso é muito legal, mas tem um preço a ser pago.

Quer saber mais sobre as estatísticas no plano de execução, o mestre Fabiano Amorim da mais detalhes sobre isso neste post:

https://blogfabiano.com/2012/07/03/statistics-used-in-a-cached-query-plan/

 

SP_GETUSEDOBJECTS
@HELP = 1,
@EXECQUERY = 0,
@GETSTATS = 0,
@SQLTEXT = ‘SELECT DBO.FN_MES(MONTH(J.DATA)) MES,APELIDO,SUM(ISNULL(P.PONTOS,0)) PONTOS–,ROW_NUMBER() OVER (ORDER BY DATA) POSITION
FROM TB_JOGOS J
INNER JOIN TB_JOGADORES JG ON J.ID_JOGADOR = JG.ID
LEFT JOIN TB_PONTOS P ON J.POSICAO = P.POSICAO
GROUP BY MONTH(J.DATA),APELIDO
ORDER BY 3 DESC,MONTH(J.DATA),2’

 

Screen Shot 07-27-17 at 06.22 PM

Como sempre não pode faltar o @HELP com as informações de versionamento, updates, contato, nessa também coloquei os exemplos de chamada bem no começo do HELP.

Importante ressaltar, quando a query possuir o carácter (‘) apóstrofo, o mesmo deve ser duplicado, como o padrão dentro de um contexto EXEC(”).

Observação: Essa versão funcionou bem nos meus testes em versões SQL Server 2012 e versões superiores, alguns testes nas versões 2008 também obtive sucesso, porém encontrei funcionamentos inesperados e não identificados na versão 2008 quando utilizado o parâmetro @GETSTATS, use com moderação, de preferência não use em produção antes de testar muito bem, como de praxe, use por conta e risco.

Bom galera é isso, espero que gostem, abaixo o código no GitHub e no meu drive para quem quiser baixar, diverta-se e use com moderação, qualquer problema, sugestão ou melhoria é bem vinda, você pode baixa-lo e modicar a vontade, apenas me atualize para deixarmos cada vez melhor.

https://github.com/Jamal27/SQLServer_Scripts/blob/master/SP_GETUSEDOBJECTS

https://drive.google.com/open?id=0B5j2xWOxw677d0ZyQkktc0p4V1k

Reginaldo Silva

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s