Encontrando as consultas mais lentas no servidor

Olá pessoal, tudo bem? Essa semana vou contribuir com um script bem útil para quem trabalha com tuning de consultas. Existem diversos aspectos que podem comprometer o desempenho num banco de dados. Já é de conhecimento de todos que grande parte desses problemas de performance que encontramos está muito relacionado às consultas mal modeladas. Bem, no primeiro instante que recebemos um reclamação de lentidão no ambiente de dados, por recomendação, é sempre bom partirmos para a análise das esperas presentes na instância, para diante dos resultados, identificarmos quais os caminhos devemos tomar para solucionar o problema. Porém, não será esse o nosso propósito neste poste. Deixarei essas questões para os próximos postes. Abaixo considero a identificação das cincos consultas mais lentas no servidor.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 5
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Elapsed Duration (s)]
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE
qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.dm_exec_cached_plans cp
ON qs.plan_handle=cp.plan_handle
ORDER BY total_elapsed_time DESC

Um exemplo abaixo mostra o que a consulta gera como resultado.

23

Percebam que o retorno do resultado traz informações importantes, como duração decorrida da consulta, quantidade de vezes que foi executada, a consulta em si e o plano em cache, no qual considero ser o dado mais importante neste script, pois é através dele que poderemos analisar de forma coerente as decisões tomadas pelo SQL Server para trazer os Results Sets, além de nos dar uma ideia de como é possível melhoramos as consultas.

Por hoje é só, pois preciso também de descanso. (Risos!!).

Bom feriado à todos.

Anúncios

Estimando o tempo final dos jobs no SQL Server

Olá pessoal, como têm indo a semana? Antes de começar a escrever quero que possam refletir sobre a seguinte mensagem: “O conhecimento não é pra ser guardado a sete chaves e sim pra ser compartilhado, dividido, pois conhecimento guardado é igual dinheiro num cofre, perde o valor e não rende nada.”

Essa senana quero falar rapidinho sobre uma dica que costumo usar as vezes nos ambientes que administro. Quem nunca se perguntou ou foi perguntado: “Em quanto tempo conclui o job que mandou rodar?”. E ai o que você faz? Com o script abaixo será possível programar e informar aos usuários quando a tarefa em execução será concluída. Ressaltando mais uma vez sobre a importância da leitura do livro DMVs SQL Server em Ação.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT r.percent_complete
, DATEDIFF(MINUTE, start_time, GETDATE()) AS Age
, DATEADD(MINUTE, DATEDIFF(MINUTE, start_time, GETDATE()) /
percent_complete * 100, start_time) AS EstimatedEndTime
, t.Text AS ParentQuery
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END – r.statement_start_offset)/2) + 1) AS IndividualQuery
, start_time
, DB_NAME(Database_Id) AS DatabaseName
, Status
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id > 50
AND percent_complete > 0
ORDER BY percent_complete DESC

 

Percebam a junção da DMV e DMF, pois é através delas que encontramos informações suficientes para determinar o término dos jobs administrativos que rodamos em nosso ambiente. Vou explicitar apenas o conceito da sys.dm_exec_requests, pois a sys.dm_exec_sql_text foi adicionada no quadro do artigo anterior. Caso queira dar uma olhada Exibindo as últimas consultas SQL em execução.

DMV Descrição
sys.dm_exec_requests

Contém detalhes de pedidos em execução no SQL Server.

Um exemplo abaixo mostra o que a consulta gera como resultado.

Sem título

As colunas apresentam informações sobre o percentual decorrido do job, o tempo em minutos de quanto o trabalho foi executado, o tempo final estimado, a instrução executado no job, o horário de ínicio e a base de dados envolvida.

Bem, por hoje acho que é só. Espero que o script possa contribuir no dia a dia dos profissionais mais experientes e iniciantes.

Abraços, até a próxima!

Exibindo as últimas consultas SQL em execução

Olá pessoal,  tudo bem? Espero que tenham tido uma semana de muitos problemas no ambiente de vocês (risos), no qual sempre nos deixa super apreensivos. Afinal de contas, o problema faz parte da nossa rotina, como profissional de tecnologia, assim dizia a lei de Murphy “Se alguma coisa tem chances de sair errado certamente sairá, e da pior maneira possível!“. Por isso a necessidade de nos aperfeiçoarmos sempre para estarmos preparados para definir soluções eficazes e eficientes no ambiente que estamos responsáveis. Busquemos viver os momentos difíceis como uma grande oportunidade de aprender e crescer na profissão de escolhemos seguir. São desses momentos de caus que adquirimos experiências fantásticas e descobrirmos que o profissional experiente é aquele que encara o problema de frente sem medo dos riscos e desafios, seguro que pode dar conta do recado.

Bem, a partir de hoje irei iniciar uma série de exposição de consultas que certamente serão de grande utilidade no dia a dia de um administrador de banco de dados. Serão postagens curtas e objetivas, no qual abordaram informações que podem ser importantes na determinação do comportamento de usuários como até teste de desempenho de um ambiente.

Confesso que a leitura do livro DMVs SQL Server em ação, escrito por Ian W. Stirk tem me ajudado bastante em meu trabalho. Considero um livro de fundamental importância para quem trabalha com administração de bancos de dados SQL Server, pois através de uma gramática de fácil compreensão e de scripts escritos de forma muito simples, tem facilitado bastante a minha vida.

Hoje irei aborda uma instrução que trás informação detalhadas das últimas consultas executadas em um banco de dados. Quem nunca se deparou com o seguinte argumento do usuário: “…o único comando que executei foi…”. Nós sabemos que as vezes sempre a uma discrepância no que o usuário diz quando queremos extrair algo dele para nos ajudar a resolver determinado problema e com o que realmente foi feito por ele.

O script abaixo nos ajudará a eliminar essa diferença.

SELECT c.session_id, s.host_name, s.login_name, s.status
, st.text, s.login_time, s.program_name, *
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
ORDER BY c.session_id

Percebam que estamos usando na consulta objetos DMVs e DMF. É a junção delas que nos trás as informações que precisamos analisar.

DMVs/DMF Descrição
sys.dm_exec_connections Contém detalhes de conexão do SQL Server.
sys.dm_exec_sessions Contém detalhes de sessões do SQL Server
sys.dm_exec_sql_text DMF que retorna ao texto SQL identificado por um lado sql_handle.

Um exemplo abaixo mostra o que a consulta gera como resultado.

1

Por hoje é só. Espero que o script possa contribuir no dia a dia de vocês.

Até a próxima!

Quantificando o impacto da atualização no sistema

Olá galera, segue um script bem interessante que extrair da leitura do livro DMVs SQL Server em ação, escrito por Ian W. Stirk. É um script útil no dia a dia de muitos administradores de banco, pois nos auxilia a quantificar o impacto que uma atualização pode causar no ambiente de dados. O uso do script é bem simples, executa  a consulta antes de qualquer atualização, seja de substituição de versão do SQL Server ou um service pack, salvar a saida num arquivo excel  e após a atualização utiliza o mesmo script para comparar os resultados e compreender se a mudança foi benéfica ou prejudicial. Para os administradores que se preocupam em acompanhar as atualizações de seus servidores fica a dica.

Abraços!

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #prework
FROM sys.dm_exec_query_stats

SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #postwork
FROM sys.dm_exec_query_stats

SELECT
SUM(p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) AS [TotalDuration]
, SUM(p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time on CPU]
, SUM((p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) –
(p2.total_worker_time – ISNULL(p1.total_worker_time, 0))) AS [Total Time Waiting]
, SUM(p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0)) AS [TotalReads]
, SUM(p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0)) AS [TotalWrites]
, SUM(p2.total_clr_time – ISNULL(p1.total_clr_time, 0)) AS [Total CLR time]
, SUM(p2.execution_count – ISNULL(p1.execution_count, 0)) AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid)

SELECT
SUM(p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) AS [TotalDuration]
, SUM(p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time on CPU]
, SUM((p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0))
– (p2.total_worker_time – ISNULL(p1.total_worker_time, 0))) AS [Total Time Waiting]
, SUM(p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0)) AS [TotalReads]
, SUM(p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0)) AS [TotalWrites]
, SUM(p2.total_clr_time – ISNULL(p1.total_clr_time, 0)) AS [Total CLR time]
, SUM(p2.execution_count – ISNULL(p1.execution_count, 0)) AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
, qt.text AS [Parent Query]
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid), qt.text
ORDER BY [TotalDuration] DESC

SELECT
p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0) AS [TotalDuration]
, p2.total_worker_time – ISNULL(p1.total_worker_time, 0) AS [Total Time on CPU]
, (p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0))
– (p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time Waiting]
, p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0) AS [TotalReads]
, p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0) AS [TotalWrites]
, p2.total_clr_time – ISNULL(p1.total_clr_time, 0) AS [Total CLR time]
, p2.execution_count – ISNULL(p1.execution_count, 0) AS [Total Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END – p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [TotalDuration] DESC

DROP TABLE #prework
DROP TABLE #postwork