Time spent by workers in RUNNABLE state

-- Time spent by workers in RUNNABLE state
/*
You can capture  wait times along with signal wait times on an instance of sql server
to investigate   percentage of  CPU waits

*/


create table #tempsignalwait ( wait_time_ms bigint,signal_wait_time_ms bigint, batchid int identity(1,1))
declare @slno int,@counter int
set @slno = 0
set @counter = 3
while @slno < @counter
begin
set @slno = @slno + 1
insert into #tempsignalwait(wait_time_ms,signal_wait_time_ms)
SELECT SUM(wait_time_ms), SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats;


waitfor delay '00:01:00' -- wait for a minute

end


select a.batchid,
b.wait_time_ms - a.wait_time_ms as delay_tot_ms_in_onemin,
b.signal_wait_time_ms - a.signal_wait_time_ms as delay_signal_ms_in_onemin,
(b.signal_wait_time_ms - a.signal_wait_time_ms)/(b.wait_time_ms - a.wait_time_ms) * 100.00 as percent_signal_delay
from #tempsignalwait a, #tempsignalwait b
where a.batchid < @counter and b.batchid > 1
and b.batchid = a.batchid + 1
order by a.batchid



drop table #tempsignalwait