-- CPU bottleneck detection
-- look for high runnable_task_count
-- sys.dm_os_schedulers
SELECT 
	scheduler_id,
	current_tasks_count,
	runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

-- CPU bottleneck detection
-- aggregate CPU usage by cached plans
-- sys.dm_exec_query_stats
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.sql_handle 
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC

-- CPU bottleneck detection
-- aggregate CPU usage by cached plans
-- with SQL text
-- sys.dm_exec_query_stats cross apply sys.dm_exec_query_text
SELECT 
	total_cpu_time, 
	total_execution_count,
	number_of_statements,
	s2.text
	--(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM 
	(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.sql_handle --,
		--MIN(statement_start_offset) AS statement_start_offset, 
		--MAX(statement_end_offset) AS statement_end_offset
	FROM 
		sys.dm_exec_query_stats AS qs
	GROUP BY qs.sql_handle
	ORDER BY SUM(qs.total_worker_time) DESC) AS stats
	CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2 

-- CPU recomilation illustration

dbcc freeproccache
use AdventureWorks          
go 
create procedure DemoProc1 as 
create table #t1 (a int, b int) 
select * from #t1 
go 
exec DemoProc1 
go 
exec DemoProc1 
go

drop procedure DemoProc1 
go 


-- recompiles due to row count changes
use AdventureWorks
go 


create procedure RowCountDemo 
as 
begin 
    create table #t1 (a int, b int) 
 
    declare @i int 
    set @i = 0    while (@i < 20) 
    begin 
       insert into #t1 values (@i, 2*@i - 50) 
 
       select a 
       from #t1  
       where a < 10 or ((b > 20 or a >=100) and (a < 10000)) 
       group by a 
  
       set @i = @i + 1 
    end 
end 
go 
exec RowCountDemo 
go


-- CPU bottleneck: excessive compiles/recompiles
-- sys.dm_exec_query_optimizer_info
select * from sys.dm_exec_query_optimizer_info
where 
	counter = 'optimizations'
	or counter = 'elapsed time'

-- CPU bottleneck: excessive compiles/recompiles
-- sys.dm_exec_query_stats
select top 25
	sql_text.text,
	sql_handle,
	plan_generation_num,
	execution_count,
	dbid,
	objectid 
from sys.dm_exec_query_stats a
	cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

-- plans with large CPU consumption
-- looking for Sort and Hash Match in plans
-- sys.dm_exec_cached_plans
-- caveat of having this query itself in the output
select *
from 
	sys.dm_exec_cached_plans
	cross apply sys.dm_exec_query_plan(plan_handle)
where 
	cast(query_plan as nvarchar(max)) like '%Sort%'
	or cast(query_plan as nvarchar(max)) like '%Hash Match%'

-- parallel queries
-- sys.dm_exec_requests, sys.dm_os_tasks, sys.dm_exec_sessions 
-- for currently executing requests
select r.session_id,
	r.request_id,
	max(isnull(exec_context_id, 0)) as number_of_workers,
	r.sql_handle,
	r.statement_start_offset,
	r.statement_end_offset,
	r.plan_handle
from sys.dm_exec_requests r
	join sys.dm_os_tasks t on r.session_id = t.session_id
	join sys.dm_exec_sessions s on r.session_id = s.session_id
where s.is_user_process = 0x1
group by r.session_id, r.request_id, r.sql_handle, r.plan_handle, 
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

-- candidates for parallel execution, naive query
-- sys.dm_exec_cached_plans, sys.dm_exec_query_plan, sys.dm_exec_sql_text
select 
p.*, 
q.*,
cp.plan_handle
from sys.dm_exec_cached_plans cp
	cross apply sys.dm_exec_query_plan(cp.plan_handle) p
	cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where 
	cp.cacheobjtype = 'Compiled Plan'
	and cast(p.query_plan as nvarchar(max)) like '%Parallel="1"%'

--sp_configure 'cost threshold for parallelism', 0
--reconfigure

-- CPU consumption is bigger than duration 
-- not very reliable
select 
	qs.sql_handle, 
	qs.statement_start_offset, 
	qs.statement_end_offset, 
	q.dbid,
	q.objectid,
	q.number,
	q.encrypted,
	q.text
from sys.dm_exec_query_stats qs
	cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time > qs.total_elapsed_time