Share via

Coleta de dados no SQL 2008–Script 2

  • Article

A segunda parte do script de coleta de dados para diagnóstico de performance está apresentado nesse artigo. Aqui, os dados serão coletados em intervalos de 5 a 15 segundos. Esse é o coração do blocker script e corresponde a um ponto muito delicado, porque a coleta é realizada frequentemente sem impacto de performance.

Links Relacionados

 

Introdução

O blocker script (versão modificada) é utilizado para coletar informações que auxiliam no diagnóstico de performance SQL Server 2000. Apesar desse script ser compatível com o SQL Server 2005 e 2008, ele ainda depende de tabelas e views obsoletas como a sysprocesses. Por isso, é importante atualizar o blocker script de forma a adotar as novas DMV e DMF de sistema.

 

Script – Parte II

O objetivo do script (PARTE 2) é coletar informações relacionados a execução de queries no SQL Server. Similar a análise de WaitStats, o script coleta informações sobre as threads em execução.

  • sys.dm_exec_requests
  • sys.dm_exec_sessions
  • sys.dm_exec_broker_activated_tasks
  • sys.dm_exec_cursors
  • sys.dm_exec_memory_grants

Infelizmente, o código do script ainda não foi finalizado. Segue o trecho do código com (grande) possibilidade de alteração. Uma delas é a inclusão da sys.dm_os_tasks e reescrever a sys.dm_exec_requests com join com a sys.dm_exec_session e sys.dm_exec_ connections. Admito que ainda está muito cru e precisa de melhorias.

A dificuldade aqui é balancear a quantidade de informação obtida com a qualidade. Esse trecho será executado periodicamente e poderá causar um aumento significativo no tamanho final do arquivo gerado.

 /*
CREATE TABLE #sqlquery_completed
(
 sql_handle          varbinary(64),
  stmt_start          int,
    PRIMARY KEY (sql_handle,stmt_start)
 -- TODO: adicionar stmt_end
)

CREATE TABLE #sqlquery_requested
(
   sql_handle          varbinary(64),
  stmt_start          int,
    stmt_end            int,
    query_hash          binary(8),
  query_plan_hash     binary(8)
)
*/

CREATE PROCEDURE #spBlockerPfe_1(@prevDate DATETIME)
AS

SET NOCOUNT ON

DECLARE @time DATETIME

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_requests'

SELECT 
    req.session_id, req.request_id, req.start_time, req.status, req.command, 
   req.sql_handle, req.statement_start_offset, req.statement_end_offset, req.plan_handle, 
 req.database_id, req.user_id, req.connection_id, req.blocking_session_id, req.wait_type, req.wait_time,  
   req.open_transaction_count, req.open_resultset_count, req.transaction_id, req.percent_complete, req.estimated_completion_time, 
 req.cpu_time, req.total_elapsed_time, req.scheduler_id, req.reads, req.writes, req.logical_reads, 
  req.row_count, req.prev_error, req.nest_level, req.granted_query_memory, req.group_id, 
 req.query_hash, req.query_plan_hash
FROM sys.dm_exec_requests req
WHERE sql_handle IS NOT NULL AND 

select * from sys.dm_exec_sessions

PRINT 'BLOCKER_PFE_END sys.dm_exec_requests '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_requests (brokers)'
-- BAD PLAN
SELECT 
 req.session_id, req.request_id, req.start_time, req.status, req.command, 
   req.sql_handle, req.statement_start_offset, req.statement_end_offset, req.plan_handle, 
 req.database_id, req.user_id, req.connection_id, req.blocking_session_id, req.wait_type, req.wait_time,  
   req.open_transaction_count, req.open_resultset_count, req.transaction_id, req.percent_complete, req.estimated_completion_time, 
 req.cpu_time, req.total_elapsed_time, req.scheduler_id, req.reads, req.writes, req.logical_reads, 
  req.row_count, req.prev_error, req.nest_level, req.granted_query_memory, req.group_id, 
 req.query_hash, req.query_plan_hash
FROM sys.dm_exec_requests req
WHERE req.session_id IN (SELECT spid FROM sys.dm_broker_activated_tasks)

PRINT 'BLOCKER_PFE_END sys.dm_exec_requests (brokers) '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_sessions'

SELECT 
    s.session_id, 
  s.login_time,
   s.status, 
  s.cpu_time, s.memory_usage, s.total_scheduled_time, s.total_elapsed_time, 
  s.last_request_start_time, s.last_request_end_time, 
    s.reads, s.writes, s.logical_reads, 
    s.row_count, 
   s.prev_error
FROM sys.dm_exec_sessions s
WHERE last_request_start_time > @prevDate OR last_request_end_time > @prevDate

PRINT 'BLOCKER_PFE_END sys.dm_exec_sessions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_cursors'

SELECT
 c.session_id, c.creation_time, c.cursor_id, 
    c.worker_time, c.reads, c.writes, c.dormant_duration, c.fetch_buffer_start, 
    c.ansi_position, c.is_open, c.fetch_status, c.sql_handle, c.statement_start_offset, c.statement_end_offset, 
    c.plan_generation_num, c.is_async_population, c.is_close_on_commit, c.fetch_buffer_size
FROM sys.dm_exec_cursors(0) c
 
PRINT 'BLOCKER_PFE_END sys.dm_exec_cursors '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN CollectSqlHandle'

-- COLLECT ADHOC REQUEST
INSERT #sqlquery_requested
SELECT
 sql_handle,
 statement_start_offset,
 statement_end_offset,
   query_hash,
 query_plan_hash
FROM sys.dm_exec_requests
WHERE sql_handle is not null

-- COLLECT CURSOR
INSERT #sqlquery_requested
SELECT
   sql_handle,
 statement_start_offset,
 statement_end_offset,
   NULL,
   NULL
FROM sys.dm_exec_cursors(0)

-- OPENTRAN
INSERT #sqlquery_requested
SELECT 
 c.most_recent_sql_handle,
   0,
  0,
  NULL,
   NULL
FROM sys.dm_exec_connections c
WHERE session_id IN (SELECT session_id FROM sys.dm_tran_session_transactions) 

PRINT 'BLOCKER_PFE_END CollectSqlHandle' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_query_memory_grants'

SELECT
  mg.session_id, mg.request_id, 
  mg.resource_semaphore_id, mg.query_cost, mg.dop, 
   mg.used_memory_kb, mg.required_memory_kb, mg.requested_memory_kb, mg.granted_memory_kb, mg.max_used_memory_kb, mg.ideal_memory_kb, 
 mg.request_time, mg.grant_time, mg.wait_time_ms, mg.timeout_sec, 
   mg.queue_id, mg.wait_order, mg.plan_handle, mg.sql_handle, mg.group_id, mg.pool_id
FROM sys.dm_exec_query_memory_grants mg

PRINT 'BLOCKER_PFE_END sys.dm_exec_query_memory_grants '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
 
GO

 

Fiquem à vontade para usar os comentários para dar sugestões ou comentar modificações, assim como para fazer as perguntas relacionadas ao funcionamento dele.

Comments

  • Anonymous
    September 15, 2011
    Olá Fabrício! O link para versão final do script não está funcionando. Abs

  • Anonymous
    September 19, 2011
    As páginas foram apagadas!!!! Obrigado pelo aviso... estou verificando como resolver.