Requests, Sessions, Connections, Stats, Logs, Sizes:
Query #1: sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_sql_text
Execute in user db:
select * from sys.dm_exec_requests
Select * from sys.dm_exec_sessions
SELECT
s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000.0) 'Wait Sec',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000.0) 'Elaps Sec',
db_name(r.database_id) as DBName,
'"'+Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE
WHEN r.statement_end_offset = -1
THEN Datalength(st.TEXT)
WHEN (r.statement_end_offset - r.statement_start_offset) < 0
Then Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1)+'"' AS statement_text,
'"'+Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)),
'')+'"' AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM
sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc, r.status,
r.blocking_session_id,
s.session_id
Query #2: sys.database_connection_stats
Execute in master db:
select DATEPART(yy,end_time) as Y, DATEPART(mm,end_time) as M, DATEPART(dd,end_time) as D
,sum(success_count) as success_count_sum
,sum(total_failure_count) as total_failure_count_sum
,sum(connection_failure_count) as connection_failure_count_sum
,sum(terminated_connection_count) as terminated_connection_count_sum
,sum(throttled_connection_count) as throttled_connection_count_sum
from sys.database_connection_stats
group by DATEPART(yy,end_time), DATEPART(mm,end_time), DATEPART(dd,end_time)
order by 1,2,3
Query #3: sys.event_log
Execute in master db:
select DATEADD(hh,-5,end_time) as End_Time_EST, * from sys.event_log where event_subtype not in ('0') order by end_time desc
Query #4: sys.dm_db_resource_stats
Execute in user db:
select DATEADD(hh,-5,end_time) as End_Time_EST, * from sys.dm_db_resource_stats order by end_time desc
Query #5: sys.dm_db_wait_stats
Execute in master db or user db:
select * from sys.dm_db_wait_stats
Query #6: sys.dm_exec_query_stats
Execute in user db
select * from sys.dm_exec_query_stats order by execution_count desc
Query #7: Calculate Total DB Size in GB
SELECT SUM(reserved_page_count)*8.0/1024/1024 as 'Total Size (GB)'
FROM sys.dm_db_partition_stats;
GO
Query #8: Calculate DB Objects Size in GB > 1GB
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0/1024/1024 as 'Total Size (GB)'
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
HAVING SUM(reserved_page_count) * 8.0/1024/1024 > 1
ORDER BY 'Total Size (GB)' DESC;
GO
Procedure Cache:
Query #1: Stored Procedure Execution Stats
SELECT TOP(25) p.name AS [SP Name],
qs.total_physical_reads AS [TotalPhysicalReads],
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads],
qs.execution_count,
qs.total_logical_reads,
qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
AND qs.total_physical_reads > 0
ORDER BY qs.total_elapsed_time/qs.execution_count DESC OPTION (RECOMPILE);
--ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
--ORDER BY execution_count DESC OPTION (RECOMPILE);
Query #2: Find how many execution plans are in procedure cache for a stored procedure
select
DB_NAME(dbid), objectid, so.name, objtype, cacheobjtype, usecounts, refcounts, p.size_in_bytes,
plan_handle, LEFT([sql].[text], 150) as [text],
getdate()
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
inner join sysobjects so on so.id = sql.objectid
ORDER BY usecounts desc
Query #3: Get ShowPlan XML
select * from sys.dm_exec_query_plan (0x05000500C39D122910BA1CC55300000001000000000000000000000000000000000000000000000000000000)
Query #4: Plan Handle Attributes
SELECT attribute, value, is_cache_key FROM sys.dm_exec_plan_attributes(0x050005009E5954674041930B7D00000001000000000000000000000000000000000000000000000000000000)
CPU:
Query #1: Top 5 queries by average CPU time
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC
|
|
|
|
|
|