André,
Execute estes Scripts:
-- Look at current expensive or blocked requests
SELECT r.session_id ,
r.[status] ,
r.wait_type ,
r.scheduler_id ,
SUBSTRING(qt.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2) AS [statement_executing] ,
DB_NAME(qt.[dbid]) AS [DatabaseName] ,
OBJECT_NAME(qt.objectid) AS [ObjectName] ,
r.cpu_time ,
r.total_elapsed_time ,
r.reads ,
r.writes ,
r.logical_reads ,
r.plan_handle
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY r.scheduler_id ,
r.[status] ,
r.session_id ;
-- Shows the memory required by both running (non-null grant_time) and waiting queries (null grant_time)
-- SQL Server 2008 version
SELECT DB_NAME(st.dbid) AS [DatabaseName] ,
mg.requested_memory_kb ,
mg.ideal_memory_kb ,
mg.request_time ,
mg.grant_time ,
mg.query_cost ,
mg.dop ,
st.[text]
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;
-- Monitoring transaction activity
SELECT st.session_id ,
DB_NAME(dt.database_id) AS database_name ,
CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only'
ELSE 'read-write'
END AS transaction_state ,
dt.database_transaction_begin_time AS read_write_start_time ,
dt.database_transaction_log_record_count ,
dt.database_transaction_log_bytes_used
FROM sys.dm_tran_session_transactions AS st
INNER JOIN sys.dm_tran_database_transactions AS dt
ON st.transaction_id = dt.transaction_id
ORDER BY st.session_id ,
database_name
-- Look at active Lock Manager resources for current database
SELECT request_session_id ,
DB_NAME(resource_database_id) AS [Database] ,
resource_type ,
resource_subtype ,
request_type ,
request_mode ,
resource_description ,
request_mode ,
request_owner_type
FROM sys.dm_tran_locks
WHERE request_session_id > 50
AND resource_database_id = DB_ID()
AND request_session_id <> @@SPID
ORDER BY request_session_id ;
-- Look for blocking
SELECT tl.resource_type ,
tl.resource_database_id ,
tl.resource_associated_entity_id ,
tl.request_mode ,
tl.request_session_id ,
wt.blocking_session_id ,
wt.wait_type ,
wt.wait_duration_ms
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
ORDER BY wait_duration_ms DESC ;
-- Missing Indexes in current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
AS [index_advantage] ,
migs.last_user_seek ,
mid.[statement] AS [Database.Schema.Table] ,
mid.equality_columns ,
mid.inequality_columns ,
mid.included_columns ,
migs.unique_compiles ,
migs.user_seeks ,
migs.avg_total_user_cost ,
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC ;
-- List unused indexes
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;
-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;
Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
