Get Engine Startup Used/Licensed CPU Information
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
Get Scheduler Information
SELECT [Status], COUNT(1) As 'Core Count'
FROM sys.dm_os_schedulers
GROUP BY [Status]
Get CPU SQL Process %
DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT Record_ID
,dateadd(ms, - 1 * (@ms_ticks_now - [timestamp]), GetDate()) AS [Event Time]
,[SQL Process %]
,100 - SystemIdle - [SQL Process %] AS [Other Process %]
,SystemIdle AS [SystemIdle %]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQL Process %]
,TIMESTAMP
FROM (
SELECT TIMESTAMP
,convert(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC
Get Active Requests CPU Time
SELECT
r.Session_ID
,r.CPU_Time
,r.Total_Elapsed_Time
,r.Logical_Reads
,r.Writes
,r.DOP
,st.TEXT AS Batch_Text
,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
(
CASE
WHEN r.statement_end_offset = - 1
THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
ELSE r.statement_end_offset
END
) - r.statement_start_offset
) / 2 + 1) AS Statement_Text
,qp.query_plan AS 'XML Plan'
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC
Get Historical Queries
SELECT TOP(25) qs.execution_count AS [Execution Count],
(qs.total_logical_reads)/1000.0 AS [Total Logical Reads (ms)],
(qs.total_logical_reads/qs.execution_count)/1000.0 AS [Avg Logical Reads (ms)],
(qs.total_worker_time)/1000.0 AS [Total Worker Time (ms)],
(qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time (ms)],
(qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time (ms)],
(qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time (ms)],
qs.creation_time AS [Create Time],
t.text AS [Query Text],
qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY (qs.total_logical_reads/qs.execution_count) DESC;
|
|
|
|
|
|