Coleta de dados no SQL 2008–Script 2

Coleta de dados no SQL 2008–Script 2

  • Comments 2

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.

  • Olá Fabrício!

    O link para versão final do script não está funcionando.

    Abs

  • As páginas foram apagadas!!!! Obrigado pelo aviso... estou verificando como resolver.

Page 1 of 1 (2 items)
Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post