Skip Navigation Links
Novas Tecnologias
Ferramentas Adicionais
Ferramentas Adicionais


Dica No :
1216
Assunto : SQL Server
Titulo: Identifique Sort Spills para o TempDB

Gostou do texto ? Vote e dê sua opinião! Pontuação atual :

Translate this page now :






Adicione aos Favoritos!
BlogBlogs Rec6 Linkk Ueba Technorati Delicious DiggIt! StumbleUpon



Uma das possíveis consequencias de ter estatísticas desatualizadas ocorre quando a memória alocada para a execução de uma determinada operação se torna insuficiente e o SQL Server se vê obrigado a processar a operação em disco.

Um grande exemplo disso é a operação SORT nos planos de execução. O SORT por sí só já não é algo muito bom, mas o pior dos mundos é quando ele é realizado em disco, dentro do tempdb.

Utilizando Extended Events podemos identificar as ocorrências de Sort Spills em nosso servidor e com isso identificar estatísticas que estejam desatualizadas.

Podemos criar uma sessão para capturar sort spills utilizando o evento sort_warning da seguinte forma:


CREATE EVENT SESSION [SortWarnings_2016] ON SERVER
ADD EVENT sqlserver.sort_warning(
ACTION(
sqlserver.client_app_name,sqlserver.database_id,sqlserver.sql_text,
sqlserver.tsql_stack)
)
ADD TARGET package0.event_file(
SET filename=N'C:\xel\HashSortWarnings2016',max_file_size=(512)
)
GO

Você precisa, claro, alterar a pasta em que vai guardar os arquivos do XE.

Para ler o resultado da sessão, pode utilizar a seguinte query:


select
DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), theNodes.event_data.value('(./@timestamp)[1]', 'datetime')) AS timestamp,
theNodes.event_data.value('(./data[@name="sort_warning_type"]/text)[1]', 'varchar(20)') AS sort_warning_type,
theNodes.event_data.value('(./data[@name="granted_memory_kb"]/value)[1]', 'int') AS granted_memory_kb,
theNodes.event_data.value('(./data[@name="used_memory_kb"]/value)[1]', 'int') AS used_memory_kb,
theNodes.event_data.value('(./data[@name="worktable_physical_reads"]/value)[1]', 'int') AS worktable_physical_reads,
theNodes.event_data.value('(./data[@name="worktable_physical_writes"]/value)[1]', 'int') AS worktable_physical_writes,
theNodes.event_data.value('(./data[@name="actual_row_count"]/value)[1]', 'int') AS actual_row_count,
theNodes.event_data.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text,
theNodes.event_data.value('(./action[@name="database_id"]/value)[1]', 'int') AS database_id,
DB_NAME(theNodes.event_data.value('(./action[@name="database_id"]/value)[1]', 'int')) database_name
from
(select convert(xml,event_data) event_data
from
sys.fn_xe_file_target_read_file('C:\xel\HashSortWarnings2016*.xel', NULL, NULL, NULL)) theData
cross apply theData.event_data.nodes('//event') theNodes(event_data)

Com isso pode capturar as ocorrências de sort spills para o tempdb, o passo seguinte fica sendo analisar o plano de execução das queries que estiverem causando o sort spill.

Para testar você pode realizar a seguinte query no banco de dados AdventureWorks2016 (download em https://www.microsoft.com/en-us/download/details.aspx?id=49502) :


SELECT [CustomerID], [SalesOrderID], [OrderDate], [SubTotal]
FROM [Sales].[SalesOrderHeader]
WHERE [OrderDate] BETWEEN '2012-01-01 00:00:00.000' AND '2013-12-31 23:59:59.997'
ORDER BY [OrderDate];
GO

Essa query usa sort em memoria, sem spills, mas você pode, em um ambiente de teste, falsificar as estatísticas para ver o spill ocorrendo e idendificar a estatística desatualizada, da seguinte forma:


UPDATE STATISTICS [Sales].[SalesOrderHeader]
WITH ROWCOUNT = 100, PAGECOUNT = 697;
GO

Lembre-se de limpar o cache de planos de execução (DBCC FREEPROCCACHE), então verá a query causando o spill para o tempdb.

Para voltar as estatísticas ao normal, basta reindexar:


Alter index [PK_SalesOrderHeader_salesorderid] on sales.salesorderheader rebuild


Nome :
E-mail:
Comentarios :
 
 
Os Últimos Comentários
data: 1/31/2017 12:12:00 PM
nome: lriZGJSferEmBZeBnV
email: derby451@hotmail.com
comentário:
nOtr9R http://www.y7YwKx7Pm6OnyJvolbcwrWdoEnRF29pb.com

data: 1/29/2017 9:49:00 AM
nome: noKcFTxUiSFmesE
email: derby451@hotmail.com
comentário:
eG9uac http://www.y7YwKx7Pm6OnyJvolbcwrWdoEnRF29pb.com

data: 1/29/2017 9:47:00 AM
nome: UJPoPLqRvSb
email: jogcbfn@hotmail.com
comentário:
fPf2Qm http://www.y7YwKx7Pm6OnyJvolbcwrWdoEnRF29pb.com

data: 1/5/2017 10:42:00 PM
nome: onGzduirdIAETzWFQ
email: jfvynms4281rt@hotmail.com
comentário:
1sLi3Q http://www.FyLitCl7Pf7ojQdDUOLQOuaxTXbj5iNG.com

 1  
Dicas
Dica do Dia
Receba Dicas Por Email
E-mail :  
 


 (help)
Aceito receber informativos do devASPNet, informações de eventos e treinamentos

Veja Quais Informativos Você Receberá

Pesquisar Dicas
Pesquisar Artigos, Dicas e Noticias

Banco de Dados
Algumas Entrevistas
Links Importantes

Búfalo Informática, Treinamento e Consultoria
R. Alvaro Alvim, 37/920 Centro - Cinelândia - Rio de Janeiro Cep: 20031-010
Tel : (21) 2262-1368 (21) 9240-5134 E-mail : Contato@bufaloinfo.com.br