Archive

Archive for May, 2014

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:

Imagem

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.

Imagem

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.

Imagem

Agora irei executar a query abaixo:

Imagem

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

 

Não existe eventos no Profiler:
Imagem

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.

Imagem

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

 

Se modificarmos o código de 3000 para 3500 podemos ver que o SQL Server gerou um evento de Sort Warnning no SQL Profiler.Imagem

Profiler:
Imagem
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.

Imagem

Dando uma olhada no evento detalhadamente, podemos perceber que a mensagem é clara dizendo que o SQL Server gerou o evento na tempdb:
Imagem

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

Imagem

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

Imagem

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

Resultado da Query
Imagem

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.

Imagem

Agora veja o plano gerado:

Imagem

E de quanto o SQL Server estimou agora:

Imagem

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://amihalj.wordpress.com/2011/12/05/sql-server-2012-detecting-sort-warnings-with-extended-events-sort_warning-event/

http://www.solidq.com/identifying-solving-sort-warnings-problems-sql-server/

 

 

 

 

 

 

 

Advertisements
Categories: Performance