Sort Warnning e agora?
No ultimo evento SQL SAT 284 nós citamos sobre alguns pontos importantes que podem comprometer a performance das nossas queries, sendo eles: cache bloat, indexação e Sort Warnning. No post de hoje, mostrarei como identificar um sort Warning e como corrigi-lo.
O que é um Sort Warnning ?
Simplificando um sort warnning ocorre quando o SQL Server realiza uma operação de ordenação e a memória estimada para o operador de ordenação não é o suficiente fazendo com que o SQL Server realizae um “spill” para o tempdb. Esse tipo de operação é extremamente custoso para o SQL Server.
Abaixo vamos montar o nosso cenário:
O script acima para gerar os 4 bilhões – Itzik Ben Gan.
Atualizaremos as estatísticas da tabela com Full Scan e então executaremos o T-SQL abaixo para sabermos qual a média de registros que existe com um Id menor que 3500.
Agora como identificar um Sort Warnning?
Até o SQL Server 2008 R2 é possivel identificar a operação de Sort Warnning pelo profiler, porém, ele apenas mostra que uma operação de Sort Warnning ocorreu, mas, não mostra qual foi a instrução que levou o evento à acontecer. A partir do SQL Server 2012 é possível identificar a instrução que disparou o evento realizando a coleta com os XEvents.
Irei abrir um SQL Profiler e coletarei apenas o evento de Sort Warning antes de executar a query para analisarmos se o mesmo será gerado ou não.
Agora irei executar a query abaixo:
E podemos ver que o plano de execução da query possui um operador de sort, porém, o mesmo não gerou o evento de Warnning.
Plano de Execução:
Não existe eventos no Profiler:
Obs: Se executaros a query acima seguido de um select na DMV sys.dm_io_virtual_file_stats, podemos perceber que os bytes de reads/writes da tempdb não sofrem qual mudança.
Podemos observar que o tempdb não sofreu alterações no momento da operação de Sort, isso significa que a operação de Sort está sem feira na memória:
Se modificarmos o código de 3000 para 3500 podemos ver que o SQL Server gerou um evento de Sort Warnning no SQL Profiler.
Profiler:
Para quem está acostumado a olhar planos de execução, pode perceber que desde o SQL Server 2012 tivemos uma pequena mudança, porém, muito importante já que através do plano de execução podemos ver que a operação de Sort disparou um evento.
Dando uma olhada no evento detalhadamente, podemos perceber que a mensagem é clara dizendo que o SQL Server gerou o evento na tempdb:
Agora porque o problema ocorreu?
Como podemos perceber na primeira query o SQL Server estimou o “Memory Grant” de 8040 para executar uma ordenação em 2999.94 registros (estimado).
Obs: Não entraremos no mérito do porque da estimativa e etc. Essa abordagem faremos em um futuro artigo de Estatísticas.
Para a segunda query quando mudamos o ID do predicado da consulta, temos um “Memory Grant” de 9296 para executar uma ordenação de 3499.93 registros. Como o Memory Grant não foi suficiente o SQL Server decide fazer um “Spill” para o tempdb, pois, realmente é melhor do que ele retornar a seguinte mensagem para a aplicação: “Não consegui Ordenar por falta de recurso”.
Como podemos resolver esse problema? De uma maneira bem básica é criar um índice na coluna e ai o problema se acabar. Infelizmente esse não é o caso, vamos ver? Crie o índice abaixo e execute novamente a query:
Create Index:
Resultado da Query
O real problema aqui é que a quantidade memória não é suficiente para ordenação mesmo criando o indice. Thiago qual a solução para esse caso? Pense o seguinte: Que bom seria se o SQL Server estimasse uma quantidade maior de recurso se a linha retornada tivesse um tamanho “maior” no número total de bytes. O que precisamos fazer é dizer ao SQL Server que o tamanho da linha é retornada é maior em bytes do que realmente é, sendo assim, é necessário que o SQL Server estime uma quantidade de recurso maior do que o normal.
Agora veja o plano gerado:
E de quanto o SQL Server estimou agora:
Por enquanto é só. Em alguns outros artigos entramos em alguns conceitos importantes, nesse artigo apenas quis mostrar o evento de Sort Warnning e como identifica-lo e resolve-lo. Segue algumas referências que me ajudaram na criação desse artigo:
http://www.sqlpassion.at/archive/2011/10/19/query-memory-spills/
http://www.solidq.com/identifying-solving-sort-warnings-problems-sql-server/
