Troubleshoot Sql Server Performance Bottlenecks

Pingback from:http://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/

Monitor  Recent CPU Utilization History Trends:

DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info
SELECT top 2000 record_id, EventTime,
CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
FROM
(
SELECT
record.value(‘(Record/@id)[1]’, ‘int’) AS record_id,
DATEADD (ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS EventTime,
100-record.value(‘(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) AS system_cpu_utilization_post_sp2,
record.value(‘(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) AS sql_cpu_utilization_post_sp2 ,
100-record.value(‘(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) AS system_cpu_utilization_pre_sp2,
record.value(‘(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) AS sql_cpu_utilization_pre_sp2
FROM (
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE ‘%<SystemHealth>%’) AS t
) AS t
ORDER BY record_id desc

Monitor  Expensive Queries:

SELECT TOP 20
LEFT(P.CACHEOBJTYPE + ‘(‘ +P.OBJTYPE + ‘)’, 35) AS cacheobjtype,
stat.
p.usecounts,
p.size_in_bytes / 1024 AS SIZE_IN_KB,
stat.total_worker_time/1000 AS TOT_CPU_MS,
stat.total_elapsed_time/1000 AS TOT_DURATION_MS,
stat.total_physical_reads,
stat.total_logical_writes,
stat.total_logical_reads,
LEFT (CASE
WHEN PA.VALUE=32767 THEN ‘ResourceDb’
ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname, pa.value))
END, 40) AS dbname,
sql.objectid,
CONVERT (NVARCHAR(50), CASE
WHEN sql.objectid IS NULL THEN NULL
ELSE REPLACE (REPLACE (sql.[text], CHAR(13), ‘ ‘), CHAR(10), ”)
END) as PROCNAME,
REPLACE(REPLACE(SUBSTRING (sql.[text], stat.statement_start_offset/2 + 1,
CASE WHEN stat.statement_end_offset = -1 THEN LEN (CONVERT (nvarchar(max), sql.[text]))
ELSE stat.statement_end_offset / 2 – stat.statement_start_offset / 2 + 1 END),
CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS STMT_TEXT,
QPLAN.Query_Plan
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sql
OUTER APPLY sys.dm_exec_query_plan(p.plan_handle) as QPLAN
WHERE pa.attribute = ‘dbid’
ORDER BY tot_cpu_ms DESC
OR
SELECT * FROM sys.dm_exec_requests ORDER BY CPU_TIME desc
OR
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc