--highest CPU
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
--If you are seeing lots of SOS_SCHEDULER_YIELD in
--your Wait States, that is a very stong indicator of CPU pressure.
--You can run the DMV query to confirm that:
-- Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
--If you see the runnable tasks count above zero,
--that is cause for concern, and if you see it in double digits
--for any length of time, that is cause for extreme concern!
--This query will give you an idea of
--how many tasks are waiting in the system.
--You can use this information to understand
--blocking characteristics of your load
select count(*)
from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL'
--The following query gives you a high-level view of which
--currently cached batches or procedures are using the most CPU
--The query aggregates the CPU consumed by all statements with the same
--plan__handle (meaning that they are part of the same batch or procedure).
--If a given plan_handle has more than one statement, you may have to drill in
--further to find
--the specific query that is the largest contributor to the overall CPU usage
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
--To find more details about plan run this query
-- takes either spid or plan_handle
SELECT *
FROM sys.dm_exec_query_plan(0x06000700185B93054043ED7F010000000000000000000000)
-- 4. Q. Does my load have an active resource bottleneck?
--You can answer this question by looking at the resource address
--that tasks are blocked on.
--Keep in mind that not all wait types have resource associated with them.
select resource_address, count (*)
from sys.dm_os_waiting_tasks
WHERE resource_address <> 0
group by resource_address
order by count (*) desc
--
--sys.dm_os_schedulers
--1. Q. Do I need to by more CPUs?
--In order to answer this question you have to find out if your
--load is really CPU bounded. Your load is really CPU bounded if a
--number of runnable tasks per each scheduler always greater than 1
--and all of your queries have correct plan.
--The latter statement is very important, your load can be CPU bounded
--due to the fact that somehow optimizer generated bad plan –
--it can happen if your statistics out of date or you tried to
--perform handcrafted optimization. In this case you don’t want to run to
--Circuit City to buy more CPUs right a way – you want to fix the plan.
--Here is the query to find out average length of a runable queue on the system:
select AVG (runnable_tasks_count)
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'
--1. Q. How many sockets does my machine have?
select cpu_count/hyperthread_ratio AS sockets
from sys.dm_os_sys_info
--Q. How many either cores or logical CPU share the same socket?
select hyperthread_ratio AS cores_or_logical_cpus_per_socket
from sys.dm_os_sys_info
--4. Q. How much physical memory my machine has?
select physical_memory_in_bytes/1024 AS physical_memory_in_kb
from sys.dm_os_sys_info
