Script SQLMON - Monitorando SQL 2008

Script SQLMON - Monitorando SQL 2008

  • Comments 6

Após mais de 1 ano trabalhando em um projeto de monitoração, está finalizado o script SQLMON para a captura de informações do banco de dados. Tenho usado o script no dia a dia para resolver principalmente problemas relacionado com desempenho. Até o momento, não percebi nenhum efeito colateral.

USE master
GO
IF OBJECT_ID('tempdb..#spBlockerPfe') IS NOT NULL
	DROP PROCEDURE #spBlockerPfe
GO
-----------------------------------------------------------------------------------------------------
CREATE PROCEDURE #spBlockerPFE(@timespan INT = 12)
AS

SET NOCOUNT ON
SET LOCK_TIMEOUT 30000

DECLARE @startDate DATETIME
DECLARE @prevDate DATETIME
DECLARE @endDate DATETIME
DECLARE @step INT = 0
DECLARE @time DATETIME

SELECT @startDate = GETDATE(), @prevDate = '1900-01-01', @step = 0

SET @endDate = DATEADD(HOUR,@timespan,GETDATE())

PRINT 'BLOCKER_PFE_SCRIPT_KATMAI Script v10.0.14 (SQL2008)'
PRINT ''
PRINT '  SQL Instance:       ' + @@SERVERNAME
PRINT '  SQL Version:        ' + CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) + ' (' + CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR) + ')'
PRINT '  Start time:         ' + CONVERT(VARCHAR(24), GETDATE(), 121)
PRINT '  Scheduled end time: ' + CONVERT(VARCHAR(24), @endDate, 121)
	
-- SQL_TEXT --------------------------------------
CREATE TABLE #sqlquery_requested
(
	sql_handle			varbinary(64),
	stmt_start			int,
	stmt_end			int,
	query_hash			binary(8),
	query_plan_hash		binary(8)
)

CREATE TABLE #filehandle
(
	file_handle VARBINARY(8) PRIMARY KEY, 
	database_id INT, 
	file_id INT, 
	filename NVARCHAR(260)
)

SET @time = GETDATE()
EXEC #spBlockerPfe_0

WHILE @startDate < @endDate
BEGIN
	PRINT ''
	PRINT 'BLOCKER_PFE_BEGIN SqlMonData ' + CONVERT(VARCHAR(24), GETDATE(), 121)

	SET @startDate = GETDATE()
	
	EXEC #spBlockerPfe_1 
	EXEC #spBlockerPfe_1_handle
	
	IF @step % 12 = 0 
	BEGIN
		DECLARE @savePrevDate DATETIME = GETDATE()
		
		EXEC #spBlockerPfe_2 @prevDate
		SET @prevDate = @savePrevDate
		
		EXEC #spBlockerPfe_2_handle
	END

	SET @step = @step + 1
	
	PRINT ''
	PRINT 'BLOCKER_PFE_END SqlMonData ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

	RAISERROR('',0,1) WITH NOWAIT
	
	WAITFOR DELAY '0:0:5'
END

GO
-----------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#spBlockerPfe_0') IS NOT NULL
	DROP PROCEDURE #spBlockerPfe_0
	
IF OBJECT_ID('tempdb..#spBlockerPfe_1') IS NOT NULL
	DROP PROCEDURE #spBlockerPfe_1

IF OBJECT_ID('tempdb..#spBlockerPfe_2') IS NOT NULL
	DROP PROCEDURE #spBlockerPfe_2

IF OBJECT_ID('tempdb..#spBlockerPfe_1_handle') IS NOT NULL
	DROP PROCEDURE #spBlockerPfe_1_handle

IF OBJECT_ID('tempdb..#spBlockerPfe_2_handle') IS NOT NULL
	DROP PROCEDURE #spBlockerPfe_2_handle
-----------------------------------------------------------------------------------------------------
GO
CREATE PROCEDURE #spBlockerPfe_0
AS

SET NOCOUNT ON

DECLARE @time DATETIME

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN MachineInfo'
PRINT ''
PRINT 'GeneralInformation'
PRINT REPLICATE('-',100)
PRINT 'ServerName: ' + @@SERVERNAME
PRINT 'PhysicalName: ' + CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)
PRINT 'ProductVersion: ' + CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
PRINT 'ProductLevel: ' + CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR)
PRINT 'ResourceVersion: ' + CAST(SERVERPROPERTY('ResourceVersion') AS VARCHAR)
PRINT 'ResourceLastUpdateDateTime: ' + CAST(SERVERPROPERTY('ResourceLastUpdateDateTime') AS VARCHAR)
PRINT 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS VARCHAR)
PRINT 'ProcessId: ' + CAST(SERVERPROPERTY('ProcessId') AS VARCHAR)
PRINT 'SessionId: ' + CAST(@@SPID AS VARCHAR)
PRINT 'Collation: ' + CAST(SERVERPROPERTY('Collation') AS VARCHAR(32))
PRINT ''
PRINT 'BLOCKER_PFE_END MachineInfo ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN @@version'
SELECT @@version AS 'version'
PRINT 'BLOCKER_PFE_END @@version ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

PRINT ''
PRINT 'BLOCKER_PFE_BEGIN xp_msver'
EXEC xp_msver
PRINT 'BLOCKER_PFE_END xp_msver ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

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

SELECT 
	sqlserver_start_time, -- 2008
	cpu_count, hyperthread_ratio, 
	physical_memory_in_bytes/1024/1024 AS 'physical_memory(MB)',
	bpool_committed*8/1024 AS 'buffer_pool(MB)', 
	bpool_commit_target*8/1024 AS 'buffer_pool_target(MB)', 
	bpool_visible*8/1024 AS 'buffer_visible(MB)', 
	virtual_memory_in_bytes/1024/1024 AS 'virtual_memory(MB)',
	max_workers_count, scheduler_count
FROM sys.dm_os_sys_info

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

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_cluster_nodes'
SELECT NodeName FROM sys.dm_os_cluster_nodes 
PRINT 'BLOCKER_PFE_END sys.dm_os_cluster_nodes '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_io_cluster_shared_drives'
SELECT DriveName FROM sys.dm_io_cluster_shared_drives 
PRINT 'BLOCKER_PFE_END sys.dm_io_cluster_shared_drives '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
 
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.configurations'
SELECT name, value=CAST(value AS VARCHAR(16)), value_in_use=CAST(value_in_use AS VARCHAR(16)) 
FROM sys.configurations
ORDER BY name

PRINT 'BLOCKER_PFE_END sys.configurations ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
	
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.databases'
SELECT 
d.database_id, d.name, state_desc=CAST(d.state_desc AS VARCHAR(20)), user_access_desc=CAST(d.user_access_desc AS VARCHAR(16)), d.compatibility_level, 
	d.create_date, collation_name=CAST(d.collation_name AS VARCHAR(64)), d.owner_sid, 
	d.log_reuse_wait_desc, 
	readonly = d.is_read_only, 
	autoclose = d.is_auto_close_on, 
	autoshrink = d.is_auto_shrink_on, 
	standby = d.is_in_standby, 
	cleanshut = d.is_cleanly_shutdown, 
	supplog = d.is_supplemental_logging_enabled, 
	snapshot = d.snapshot_isolation_state, 
	readsnap = d.is_read_committed_snapshot_on, 
	recovery = CAST(d.recovery_model_desc AS VARCHAR(8)), 
	pageverify =  CAST(d.page_verify_option_desc AS VARCHAR(8)), 
	autostat_crt = d.is_auto_create_stats_on, 
	autostat_upd = d.is_auto_update_stats_on, 
	autostat_async = d.is_auto_update_stats_async_on, 
	fulltext = d.is_fulltext_enabled, 
	trustworthy = d.is_trustworthy_on, 
	dbchain = d.is_db_chaining_on, 
	paramforced = d.is_parameterization_forced, 
	masterkey = d.is_master_key_encrypted_by_server, 
	rep_pub = d.is_published, 
	rep_sub = d.is_subscribed, 
	rep_merge = d.is_merge_published, 
	rep_dist = d.is_distributor, 
	sync_bkp = d.is_sync_with_backup, 
	sb_enabled = d.is_broker_enabled, 
	sb_guid = d.service_broker_guid, 
	datacorr = d.is_date_correlation_on
FROM sys.databases d
PRINT 'BLOCKER_PFE_END sys.databases '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.master_files'
-- VIEW ANY DEFINITION
SELECT 
	d.database_id, d.file_id, state_desc = CAST(d.state_desc AS VARCHAR(16)), type_desc=CAST(d.type_desc AS VARCHAR(16)), d.physical_name, d.file_guid, d.data_space_id, d.name, d.size, d.max_size, d.growth, d.is_media_read_only, d.is_read_only, d.is_sparse, d.is_percent_growth 
FROM sys.master_files d
PRINT 'BLOCKER_PFE_END sys.master_files '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.master_files[Size]'
-- VIEW ANY DEFINITION
SELECT 
	d.database_id, type_desc=CAST(d.type_desc AS VARCHAR(16)), 
	CAST(d.size AS BIGINT)*8/1024 AS 'Size(MB)', 
	CASE d.is_percent_growth 
		WHEN 0 THEN CAST(d.growth AS INT)*8/1024
		WHEN 1 THEN CAST(d.growth AS INT)*CAST(d.size AS INT)/100*8/1024
	END AS 'Growth(MB)',
	CASE d.is_percent_growth 
		WHEN 0 THEN CAST( (100*d.growth/d.size) AS SMALLINT )
		WHEN 1 THEN CAST( d.growth AS SMALLINT )
	END AS 'Growth(perc)',	
	d.physical_name
FROM sys.master_files d
ORDER BY d.physical_name
PRINT 'BLOCKER_PFE_END sys.master_files[Size] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.traces'
-- VIEW ANY DEFINITION
SELECT 
	t.id, t.status, t.path, t.max_size, t.stop_time, t.max_files, t.is_rowset, t.is_rollover, t.is_shutdown, t.is_default, t.buffer_count, t.buffer_size, t.file_position, t.reader_spid, t.start_time, t.last_event_time, t.event_count, t.dropped_event_count 
FROM sys.traces t
PRINT 'BLOCKER_PFE_END sys.traces '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

TRUNCATE TABLE #filehandle

INSERT #filehandle(file_handle, database_id, file_id, filename)
SELECT vfs.file_handle, vfs.database_id, vfs.file_id, f.physical_name FROM sys.dm_io_virtual_file_stats(-1,-1) vfs
	LEFT JOIN sys.master_files f ON vfs.database_id = f.database_id AND vfs.file_id = f.file_id
	WHERE file_handle<>0
	
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 

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_connections/sessions'

SELECT 
	s.session_id, 
	s.group_id, 
	CAST(s.status AS VARCHAR(16)) AS 'status',
	CAST(s.host_name AS VARCHAR(20)) AS 'host_name', 
	CAST(s.login_name AS VARCHAR(32)) AS 'login_name', 
	CAST(s.program_name AS VARCHAR(64)) AS 'program_name', 
	s.host_process_id, 
	c.connection_id,
	CAST(s.original_login_name AS VARCHAR(32)) AS 'original_login_name', 
	s.client_interface_name, s.client_version, 
	CAST(c.auth_scheme AS VARCHAR(16)) AS 'auth_scheme', 
	CAST(c.net_transport AS VARCHAR(16)) AS 'net_transport', 
	c.client_net_address, c.client_tcp_port, 
	CAST(c.most_recent_sql_handle AS VARBINARY(26)) AS 'most_recent_sql_handle', 
	c.net_packet_size, c.encrypt_option,
	c.connect_time, s.login_time
FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id
	
PRINT 'BLOCKER_PFE_END sys.dm_exec_connections/sessions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
	
GO
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
CREATE PROCEDURE #spBlockerPfe_1
AS

SET NOCOUNT ON
SET LOCK_TIMEOUT 250

DECLARE @time DATETIME

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

SELECT 
	req.session_id, req.blocking_session_id AS 'blocked', 
	req.database_id AS db_id, req.command, 
	req.total_elapsed_time AS 'elapsed_time', req.cpu_time, req.granted_query_memory AS 'granted_memory', req.logical_reads, 
	req.wait_time, CAST(req.wait_type AS VARCHAR(16)) AS 'wait_type', 
	req.open_transaction_count AS 'tran_count', 
	req.reads, req.writes,  
	req.start_time, req.status, req.connection_id, req.user_id, 
	req.group_id, -- KATMAI (SQL2008)
	req.transaction_id, req.request_id, 
	CAST(req.plan_handle AS VARBINARY(26)) AS 'plan_handle', 
	CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle', 
	req.nest_level,
	req.statement_start_offset AS 'stmt_start', req.statement_end_offset AS 'stmt_end', 
	req.query_hash, req.query_plan_hash
FROM sys.dm_exec_requests req
WHERE group_id > 1 AND session_id<>@@SPID -- KATMAI (SQL2008)

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_cursors'

SELECT
	c.session_id, c.cursor_id, 
	DATEDIFF(ms, c.creation_time, GETDATE()) AS 'elapsed_time', 
	c.worker_time, c.reads, c.writes, c.dormant_duration, c.fetch_buffer_start, 
	c.ansi_position, c.is_open, c.fetch_status, c.creation_time, 
	CAST(c.sql_handle AS VARBINARY(26)) AS 'sql_handle', 
	c.statement_start_offset AS 'stmt_start', c.statement_end_offset AS 'stmt_end', 
	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 sys.dm_exec_query_memory_grants'

SELECT
	mg.session_id, 
	mg.query_cost, mg.dop, 
	convert(VARCHAR(12), datediff(ms,request_time,getdate())) AS 'elapsed_time',
	mg.wait_time_ms, 
	mg.resource_semaphore_id AS 'sem_id', mg.requested_memory_kb, mg.granted_memory_kb, 
	mg.used_memory_kb, mg.required_memory_kb, mg.max_used_memory_kb, mg.ideal_memory_kb, 
	mg.request_time, mg.grant_time, mg.timeout_sec, 
	mg.queue_id, mg.wait_order, 
	mg.request_id,
	CAST(mg.plan_handle AS VARBINARY(26)) AS 'plan_handle', 
	CAST(mg.sql_handle AS VARBINARY(26)) AS '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())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_tasks'
SELECT
t.session_id, t.request_id, t.exec_context_id AS 'ecid',
task_state = CAST(t.task_state AS NVARCHAR(10)), 
t.context_switches_count AS 'context_switches', t.pending_io_count AS 'pending_io',
t.scheduler_id
FROM sys.dm_os_tasks t
WHERE session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process=1) AND session_id<>@@SPID 
PRINT 'BLOCKER_PFE_END sys.dm_os_tasks '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_waiting_tasks'
SELECT
wt.session_id as 'spid', wt.blocking_session_id as 'blocked_spid', 
wt.wait_duration_ms, 
CAST(wt.wait_type AS NVARCHAR(32)) AS 'wait_type', 
CAST(wt.resource_description AS NVARCHAR(128)) AS 'resource_description',
wt.exec_context_id AS 'ecid', wt.blocking_exec_context_id AS 'block_ecid'
FROM sys.dm_os_waiting_tasks wt
WHERE session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process=1)
PRINT 'BLOCKER_PFE_END sys.dm_os_waiting_tasks  '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 


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

SELECT 
dt.transaction_id, dt.database_id AS 'db_id', 
DATEDIFF(ms, dt.database_transaction_begin_time, GETDATE()) AS 'time_ms', 
dt.database_transaction_type AS 'type', 
dt.database_transaction_state AS 'state', 
dt.database_transaction_log_record_count AS 'log_record_count', 
dt.database_transaction_log_bytes_used AS 'log_bytes_used', 
dt.database_transaction_log_bytes_reserved AS 'log_bytes_reserved', 
dt.database_transaction_begin_time AS 'begin_time', 
dt.database_transaction_begin_lsn AS 'begin_lsn', 
dt.database_transaction_last_lsn AS 'last_lsn',
dt.database_transaction_last_rollback_lsn AS 'rollback_lsn'
FROM sys.dm_tran_database_transactions dt
WHERE database_transaction_begin_time is NOT NULL

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

GO

-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
CREATE PROCEDURE #spBlockerPfe_1_handle
AS

SET NOCOUNT ON
SET LOCK_TIMEOUT 250

DECLARE @time DATETIME

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 AND session_id <> @@spid

-- 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)  AND session_id <> @@spid

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

-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
CREATE PROCEDURE #spBlockerPfe_2(@prevDate DATETIME)
AS

SET NOCOUNT ON
SET LOCK_TIMEOUT 3000

DECLARE @time DATETIME

SELECT @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_resource_governor_resource_pools'

select 
wp.pool_id, CAST(wp.name AS VARCHAR(16)) AS 'name', wp.statistics_start_time, 
wp.total_cpu_usage_ms, wp.cache_memory_kb, wp.compile_memory_kb, wp.used_memgrant_kb, wp.total_memgrant_count, 
wp.total_memgrant_timeout_count, wp.active_memgrant_count, wp.active_memgrant_kb, wp.memgrant_waiter_count, 
wp.max_memory_kb, wp.used_memory_kb, wp.target_memory_kb, 
wp.out_of_memory_count, wp.min_cpu_percent, wp.max_cpu_percent, wp.min_memory_percent, wp.max_memory_percent 
from sys.dm_resource_governor_resource_pools wp

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

SELECT @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_resource_governor_workload_groups'

SELECT 
wg.group_id, wg.pool_id, CAST(wg.name AS VARCHAR(16)) AS 'name', 
wg.active_request_count, wg.queued_request_count, wg.blocked_task_count, wg.active_parallel_thread_count, 
wg.statistics_start_time, wg.total_request_count, wg.total_queued_request_count, wg.total_cpu_limit_violation_count, 
wg.total_cpu_usage_ms, 
wg.total_lock_wait_count, 
wg.total_lock_wait_time_ms, 
wg.total_query_optimization_count, 
wg.total_suboptimal_plan_generation_count, 
wg.total_reduced_memgrant_count, 
wg.max_request_cpu_time_ms, 
wg.max_request_grant_memory_kb, 
wg.request_max_memory_grant_percent,
CAST(wg.importance AS VARCHAR(16)) AS 'importance', 
wg.request_max_cpu_time_sec, wg.group_max_requests, 
wg.request_memory_grant_timeout_sec, wg.max_dop
from sys.dm_resource_governor_workload_groups wg

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

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

SELECT
	sos.scheduler_id, sos.is_online, sos.is_idle, 
	sos.current_tasks_count, sos.runnable_tasks_count, sos.active_workers_count, sos.current_workers_count, sos.work_queue_count, 
	sos.pending_disk_io_count, sos.scheduler_address
FROM sys.dm_os_schedulers sos

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


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_waiting_tasks[BlockedSessions]';

;WITH cteBlockedSessions
AS
	(
	SELECT session_id, blocking_session_id FROM sys.dm_os_waiting_tasks 
	WHERE blocking_session_id <> session_id 
	)
SELECT 
	blk.blocking_session_id AS 'session_id', 
	CAST(c.most_recent_sql_handle AS VARBINARY(26)) as 'most_recent_sql_handle',
	at.transaction_id,
	DATEDIFF(ms, at.transaction_begin_time, GETDATE()) AS 'elapsed_time',
	CAST(r.sql_handle AS VARBINARY(26)) as 'sql_handle',
	r.statement_start_offset, r.statement_end_offset,
	at.transaction_begin_time, at.transaction_state 
FROM cteBlockedSessions blk 
	INNER JOIN sys.dm_exec_connections c ON blk.blocking_session_id = c.session_id
	INNER JOIN sys.dm_tran_session_transactions st ON st.session_id = c.session_id
	INNER JOIN sys.dm_tran_active_transactions at ON st.transaction_id = at.transaction_id
	LEFT JOIN sys.dm_exec_requests r ON c.session_id = r.session_id
WHERE blk.blocking_session_id not in (SELECT session_id FROM cteBlockedSessions)

PRINT 'BLOCKER_PFE_END sys.dm_os_waiting_tasks[BlockedSessions] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_sys_memory'
-- SQL 2008
SELECT 
s.system_high_memory_signal_state, s.system_low_memory_signal_state, s.available_physical_memory_kb, 
s.available_page_file_kb, 
s.system_cache_kb, s.kernel_paged_pool_kb, s.kernel_nonpaged_pool_kb, 
s.total_physical_memory_kb, 
s.total_page_file_kb
FROM sys.dm_os_sys_memory s

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

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_process_memory'
-- SQL 2008
SELECT
p.process_physical_memory_low, p.process_virtual_memory_low,
p.memory_utilization_percentage AS 'memory_utilization', p.available_commit_limit_kb, p.virtual_address_space_available_kb,
p.physical_memory_in_use_kb, p.large_page_allocations_kb, p.locked_page_allocations_kb, 
p.virtual_address_space_reserved_kb, p.virtual_address_space_committed_kb, p.total_virtual_address_space_kb
FROM sys.dm_os_process_memory p

PRINT 'BLOCKER_PFE_END dm_os_process_memory '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
 
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_memory_clerks'

SELECT
mc.type, total_kb = mc.single_pages_kb+mc.multi_pages_kb+mc.virtual_memory_committed_kb,
mc.memory_node_id, mc.single_pages_kb, mc.multi_pages_kb, mc.virtual_memory_reserved_kb, mc.virtual_memory_committed_kb, mc.awe_allocated_kb, mc.shared_memory_reserved_kb, mc.shared_memory_committed_kb, mc.name
FROM sys.dm_os_memory_clerks mc
WHERE single_pages_kb+multi_pages_kb+virtual_memory_committed_kb > 102400

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

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

SELECT
mb.pool_id, mb.memory_broker_type, 
mb.allocations_kb, mb.allocations_kb_per_sec, 
mb.predicted_allocations_kb, mb.target_allocations_kb, mb.future_allocations_kb, mb.overall_limit_kb, 
last_notification = CAST(mb.last_notification AS VARCHAR(8))
FROM sys.dm_os_memory_brokers mb

PRINT 'BLOCKER_PFE_END sys.dm_os_memory_brokers '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
  
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_active_transactions'

SELECT
t.transaction_id, t.name, t.transaction_begin_time, t.transaction_type, t.transaction_state
FROM sys.dm_tran_active_transactions t
WHERE transaction_type <> 2 -- read-only transaction

PRINT 'BLOCKER_PFE_END sys.dm_tran_active_transactions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
 
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_session_transactions'

SELECT
session_id, transaction_id, is_user_transaction AS 'is_user', is_local
FROM sys.dm_tran_session_transactions

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

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

SELECT 
	a.session_id, a.elapsed_time_seconds, a.is_snapshot, 
	a.transaction_id, a.transaction_sequence_num, a.first_snapshot_sequence_num, a.max_version_chain_traversed, a.average_version_chain_traversed
FROM sys.dm_tran_active_snapshot_database_transactions a

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

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

SELECT
	req.session_id, req.blocking_session_id AS 'blocked', 
	req.database_id AS db_id, req.command, 
	req.total_elapsed_time AS 'elapsed_time', req.cpu_time, req.granted_query_memory AS 'granted_memory', req.logical_reads, 
	req.wait_time, CAST(req.wait_type AS VARCHAR(16)) AS 'wait_type', 
	req.open_transaction_count AS 'tran_count', 
	req.reads, req.writes,  
	req.start_time, req.status, req.connection_id, 
	req.transaction_id, req.task_address, req.request_id
FROM sys.dm_exec_requests req
WHERE group_id = 1

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


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN dm_os_tasks[System]'
SELECT
t.task_address, t.session_id, t.request_id, t.exec_context_id AS 'ecid',
task_state = CAST(t.task_state AS NVARCHAR(10)), 
t.context_switches_count AS 'context_switches', t.pending_io_count AS 'pending_io',
t.scheduler_id, t.worker_address
FROM sys.dm_os_tasks t
WHERE worker_address IS NOT NULL
	AND (session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process=0) OR session_id IS NULL)
PRINT 'BLOCKER_PFE_END sys.dm_os_tasks[System] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 


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

DECLARE @lasttime DATETIME

SET @lasttime = @prevDate
-- SET @lasttime = DATEADD(s,-60,GETDATE())

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 (s.last_request_end_time > @lasttime OR s.last_request_end_time IS NULL) OR s.last_request_start_time > @lasttime 

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


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_exec_connections/sessions'
	
-- DECLARE @lasttime DATETIME
DECLARE @tbSessions TABLE (spid INT PRIMARY KEY)

INSERT @tbSessions(spid)
SELECT DISTINCT session_id FROM
	(
		SELECT session_id FROM sys.dm_exec_requests
	UNION ALL
		SELECT session_id FROM sys.dm_tran_session_transactions
	UNION ALL
		SELECT blocking_session_id FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL
	UNION ALL 
		SELECT session_id FROM sys.dm_exec_connections c
		WHERE c.connect_time > @lasttime
	) t
	WHERE session_id IS NOT NULL
	
SELECT 
	s.session_id, 
	s.group_id, 
	CAST(s.status AS VARCHAR(16)) AS 'status',
	CAST(s.host_name AS VARCHAR(20)) AS 'host_name', 
	CAST(s.login_name AS VARCHAR(32)) AS 'login_name', 
	CAST(s.program_name AS VARCHAR(64)) AS 'program_name', 
	s.host_process_id, 
	c.connection_id,
	CAST(s.original_login_name AS VARCHAR(32)) AS 'original_login_name', 
	s.client_interface_name, s.client_version, 
	CAST(c.auth_scheme AS VARCHAR(16)) AS 'auth_scheme', 
	CAST(c.net_transport AS VARCHAR(16)) AS 'net_transport', 
	c.client_net_address, c.client_tcp_port, 
	CAST(c.most_recent_sql_handle AS VARBINARY(26)) AS 'most_recent_sql_handle', 
	c.net_packet_size, c.encrypt_option,
	c.connect_time, s.login_time
FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id
WHERE c.session_id IN (SELECT spid FROM @tbSessions)
	
PRINT 'BLOCKER_PFE_END sys.dm_exec_connections/sessions '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

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

select 
	w.wait_type, 
	w.waiting_tasks_count, 
	w.wait_time_ms, w.max_wait_time_ms, w.signal_wait_time_ms
from sys.dm_os_wait_stats w where wait_time_ms > 60000

PRINT 'BLOCKER_PFE_END sys.dm_os_wait_stats '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
 
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_os_latch_stats'

select
	l.latch_class, l.waiting_requests_count, l.wait_time_ms, l.max_wait_time_ms
from sys.dm_os_latch_stats l where wait_time_ms > 60000

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


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

select 
	CAST(s.name AS VARCHAR(64)) AS 'name', 
	s.collisions, s.spins, s.spins_per_collision, s.sleep_time, s.backoffs
from sys.dm_os_spinlock_stats s where sleep_time > 100

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

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

SELECT 
	qrsem.resource_semaphore_id, qrsem.target_memory_kb, qrsem.max_target_memory_kb, qrsem.total_memory_kb, qrsem.available_memory_kb, qrsem.granted_memory_kb, qrsem.used_memory_kb, qrsem.grantee_count, qrsem.waiter_count, qrsem.timeout_error_count, qrsem.forced_grant_count, qrsem.pool_id
FROM sys.dm_exec_query_resource_semaphores qrsem

PRINT 'BLOCKER_PFE_END sys.dm_exec_query_resource_semaphores '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
 
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_io_pending_io_requests'

DECLARE @pending_io_requests_summary INT = 0

SELECT TOP 512
	io.io_pending_ms_ticks, io.io_pending, io.scheduler_address, io_pending_handle=io.io_handle, io.io_offset
FROM sys.dm_io_pending_io_requests io
ORDER BY io_pending_ms_ticks DESC

--IF @@ROWCOUNT > 32
SET @pending_io_requests_summary = 1

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

IF @pending_io_requests_summary = 1 
BEGIN
	SET @time = GETDATE()
	PRINT ''
	PRINT 'BLOCKER_PFE_BEGIN sys.dm_io_pending_io_requests[summary]'

	SELECT * FROM 
		(SELECT count=COUNT(*), avg_pending_ms_ticks=AVG(io_pending_ms_ticks), io_handle
		FROM sys.dm_io_pending_io_requests WHERE io_pending = 1 GROUP BY io_handle) i
	LEFT JOIN #filehandle f ON i.io_handle = f.file_handle
	ORDER BY avg_pending_ms_ticks DESC

	PRINT 'BLOCKER_PFE_END sys.dm_io_pending_io_requests[summary] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
END
	 
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_io_virtual_file_stats'

SELECT 
	fs.database_id, fs.file_id, 
	fs.num_of_reads, fs.num_of_bytes_read, fs.io_stall_read_ms, fs.num_of_writes, fs.num_of_bytes_written, fs.io_stall_write_ms, fs.io_stall, 
	fs.size_on_disk_bytes, virtual_file_handle = fs.file_handle
FROM sys.dm_io_virtual_file_stats (-1,-1) fs

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

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.databases[log_reuse_wait_desc]'

SELECT d.name, d.log_reuse_wait_desc
FROM sys.databases d 
WHERE d.log_reuse_wait <> 0

PRINT 'BLOCKER_PFE_END sys.databases[log_reuse_wait_desc] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
 
SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN DBCC_SQLPERF_LOGSPACE'

DBCC SQLPERF(LOGSPACE)

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

SELECT @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN DBCC_OPENTRAN'

DECLARE @dbid INT

DECLARE curDatabases CURSOR FAST_FORWARD FOR
SELECT database_id FROM sys.databases WHERE state=0

OPEN curDatabases;

FETCH NEXT FROM curDatabases INTO @dbid;

WHILE (@@FETCH_STATUS <> -1)
BEGIN
	PRINT ''
	DBCC OPENTRAN(@dbid)
	
	FETCH NEXT FROM curDatabases INTO @dbid

END

DEALLOCATE curDatabases

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

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_locks[OBJECTS]'
SELECT
l.resource_database_id AS 'db_id', l.resource_associated_entity_id AS 'entity_id', 
CAST(l.request_mode AS VARCHAR(8)) AS req_mode, req_status = CAST(l.request_status AS VARCHAR(8)),
l.request_session_id AS 'session_id', l.request_owner_id, 
resource_subtype = CAST(l.resource_subtype AS VARCHAR(16)),
object_description = CASE WHEN request_mode NOT LIKE 'Sch-%' THEN 
	CAST(
		DB_NAME(resource_database_id) + N'.' +
		OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id) + N'.' +
		OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS NVARCHAR(64))
	ELSE CAST(resource_description AS NVARCHAR(64)) END 
FROM sys.dm_tran_locks l
WHERE l.resource_type = 'OBJECT'

PRINT 'BLOCKER_PFE_END sys.dm_tran_locks[OBJECTS] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN sys.dm_tran_locks[WAIT]'
SELECT
CAST(l.resource_type AS VARCHAR(12)) AS 'resource_type',
l.resource_database_id AS 'db_id', l.resource_associated_entity_id AS 'entity_id', 
CAST(l.request_mode AS VARCHAR(8)) AS 'req_mode', req_status = CAST(l.request_status AS VARCHAR(8)),
l.request_session_id AS 'session_id', l.request_owner_id, 
resource_subtype = CAST(l.resource_subtype AS VARCHAR(16)),
resource_description =
	CASE WHEN l.resource_type = 'OBJECT' AND request_mode NOT LIKE 'Sch-%' THEN 
		CAST(
		DB_NAME(resource_database_id) + N'.' +
		OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id) + N'.' +
		OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS NVARCHAR(64))
	ELSE CAST(resource_description AS NVARCHAR(64)) END
FROM sys.dm_tran_locks l
WHERE l.request_status = 'WAIT'
	
PRINT 'BLOCKER_PFE_END sys.dm_tran_locks[WAIT] '  + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

GO

-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
CREATE PROCEDURE #spBlockerPfe_2_handle
AS

SET NOCOUNT ON
SET LOCK_TIMEOUT 3000

DECLARE @time DATETIME

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

-- 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) AND session_id <> @@spid

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


SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN FlushSqlHandle[object_name]'

SELECT DISTINCT
	CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle',
	
	CAST(
		DB_NAME(dbid) + N'.' + 
		OBJECT_SCHEMA_NAME(objectid,dbid) + N'.' + 
		OBJECT_NAME(objectid,dbid) AS NVARCHAR(128)) AS 'object_name',
	st.dbid, st.objectid
FROM #sqlquery_requested req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) st
WHERE objectid IS NOT NULL
ORDER BY dbid, objectid

PRINT 'BLOCKER_PFE_END FlushSqlHandle[object_name] ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN FlushSqlHandle[sqlquery_requested]'
SELECT
'COUNT=',			Count=COUNT(*),
'SQLHANDLE=',		CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle',
	req.stmt_start, 
	req.stmt_end,
	'QUERY_HASH=',		req.query_hash,
	'QUERY_PLAN_HASH=',	req.query_plan_hash
FROM #sqlquery_requested req
WHERE req.query_hash IS NOT NULL
GROUP BY sql_handle, query_hash, stmt_start, req.stmt_end, req.query_plan_hash
ORDER BY COUNT(*) DESC

PRINT 'BLOCKER_PFE_END FlushSqlHandle[sqlquery_requested] ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 

SET @time = GETDATE()
PRINT ''
PRINT 'BLOCKER_PFE_BEGIN FlushSqlHandle[dm_exec_sql_text]'
SELECT TOP 1000
'COUNT=',			COUNT(*),
'SQLHANDLE=',		CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle',
'SQLHASH=',			req.query_hash,
	req.stmt_start, 
	req.stmt_end,
	CHAR(13) + CHAR(10),
	'SQLTEXT=',			sqltext=(SELECT SUBSTRING(	text, stmt_start/2 + 1, 
												((CASE	WHEN stmt_end = -1 THEN DATALENGTH(text) 
														WHEN stmt_end = 0 THEN 1024
														ELSE stmt_end END) - stmt_start)/2 )
						FROM sys.dm_exec_sql_text(sql_handle))
FROM #sqlquery_requested req 
GROUP BY sql_handle, query_hash, stmt_start, req.stmt_end
ORDER BY COUNT(*) DESC

TRUNCATE TABLE #sqlquery_requested

PRINT 'BLOCKER_PFE_END FlushSqlHandle[dm_exec_sql_text] ' + convert(VARCHAR(12), datediff(ms,@time,getdate())) 
GO

-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
GO

-----------------------------------------------------------------------------------------------------
EXEC #spBlockerPFE
-----------------------------------------------------------------------------------------------------

 

Como usar o script?

Siga os passos:

  1. Copie o script do SQLMON para a sua máquina
  2. Abra o SQL Management Studio e cole o script
  3. Configure para gerar os resultados para arquivo
  4. Execute o script e deixe rodando durante o problema

image

  • Fabricio, tenho usado algumas versões "beta" desse seu script para monitorar servidores de nossos clientes em Latam. Estou muito satisfeito com os resultados! Recomendo o seu uso sem sombra de dúvida.

    P.S. Testei em laboratórios de SQL Server 2012 e tem funcionado quase da mesma maneira que no SQL Server 2008. Apenas algumas poucas partes do script precisam ser atualizadas para as novas DMVs.

    Por exemplo a sys.dm_os_sys_info mudou o campo "physical_memory_in_bytes" para "physical_memory_kb"

    Fiz as alteraçoes e agora tenho o mesmo script alterado para o SQL Server 2012. Aguardo agora a sua publicação oficial da versão para SQL Server 2012 :)

    Parabéns pelo desenvolvimento desse script!

  • Obrigado pela ajuda Roberto. Ainda não esqueci do nosso projeto "Ibirapuera".. depois precisamos voltar a conversa sobre script de monitoração. Se tudo der certo, esse ano terei tempo para trabalhar na ferramenta para facilitar a análise do servidor.

    Novamente, agradeço pelo seu tempo em testar o script até no SQL 2012!

    Abraços, Fabricio

  • Meus parabéns excelente script.

  • Muito bom Catae. Peguei o script agora, vou estudá-lo antes de dar algum "pitaco", mas pelo que vi, merece um artigo do tipo "Dissecando o script SQLMON".

    Com certeza será de grande utilidade para meu dia a dia.

    Abraços,

    Erickson Ricci

  • :)

  • Fala Fabricio,

    cadê o "Dissecando o script SQLMON"? to ansioso por este post!!

    parabéns pelo script.

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