Thursday, June 10, 2010

Handy Dynamic Management Views:

1. SPID’s with their text.

select a.session_id,a.percent_complete,a.blocking_session_id,SUBSTRING(b.text,(a.statement_start_offset)/2+1,
(Case a.statement_end_offset
when -1 then DATALENGTH(b.text)
else a.statement_end_offset
end - a.statement_Start_offset)/2)
From sys.dm_exec_requests a cross apply sys.dm_exec_sql_text(a.sql_handle) b


2. Index Fragementation:

SELECT object_name(b.object_id),a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('dbName'), OBJECT_ID(Null),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id


3. Stats last updated specific to an object:

SELECT name AS stats_name,
STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats
WHERE object_id = OBJECT_ID('Person.Address');
GO

4. Stats last updated specific to an Index:

SELECT name AS index_name,
STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes
WHERE object_id = OBJECT_ID('Person.Address');
GO


5. Top 20 Queries by Total CPU Time

select top 20 SUBSTRING(b.text,(a.statement_start_offset)/2+1,
(Case a.statement_end_offset
when -1 then DATALENGTH(b.text)
else a.statement_end_offset
end - a.statement_Start_offset)/2) as SQLText,
a.execution_count as Executes, a.plan_generation_num as [Plan Generation], a.creation_time as [Plan Cached], a.last_execution_time as [Last Executed],
a.total_worker_time/1000 as [CPU(ms)],a.total_elapsed_time/1000 as [Duration(ms)],a.total_physical_reads as [Physical Reads],
a.total_logical_reads as [Logical Reads], a.total_logical_writes as [Logical Writes], a.total_clr_time/1000 as [CLR Time(ms)]

from sys.dm_exec_query_stats as a cross apply sys.dm_exec_sql_text(a.sql_handle) b order by [CPU(ms)] desc

6. History of Wait Types:

select wait_type, case
when wait_type like N'LCK_M_%' then N'Lock'
when wait_type like N'LATCH_%' then N'Latch'
when wait_type like N'PAGELATCH_%' then N'Buffer Latch'
when wait_type like N'PAGEIOLATCH_%' then N'Buffer IO'
when wait_type like N'RESOURCE_SEMAPHORE_%' then N'Compilation'
when wait_type = N'SOS_SCHEDULER_YIELD' then N'Scheduler Yield'
when wait_type in (N'LOGMGR', N'LOGBUFFER', N'LOGMGR_RESERVE_APPEND', N'LOGMGR_FLUSH', N'WRITELOG') then N'Logging'
when wait_type in (N'ASYNC_NETWORK_IO', N'NET_WAITFOR_PACKET') then N'Network IO'
when wait_type in (N'CXPACKET', N'EXCHANGE') then N'Parallelism'
when wait_type in (N'RESOURCE_SEMAPHORE', N'CMEMTHREAD', N'SOS_RESERVEDMEMBLOCKLIST') then N'Memory'
when wait_type like N'CLR_%' or wait_type like N'SQLCLR%' then N'CLR'
when wait_type like N'DBMIRROR%' or wait_type = N'MIRROR_SEND_MESSAGE' then N'Mirroring'
when wait_type like N'XACT%' or wait_type like N'DTC_%' or wait_type like N'TRAN_MARKLATCH_%' or wait_type like N'MSQL_XACT_%' or wait_type = N'TRANSACTION_MUTEX' then N'Transaction'
when wait_type like N'SLEEP_%' or wait_type in(N'LAZYWRITER_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'WAIT_FOR_RESULTS') then N'Sleep'
else N'Other'
end as [Category],waiting_tasks_count as [Num Waits], wait_time_ms as [Wait Time], max_wait_time_ms as [Max Wait Time(ms)]
from sys.dm_os_wait_stats where waiting_tasks_count > 0 order by 3 desc,4 desc,5 desc

No comments:

Post a Comment