-- Comprobar el consumo reciente de CPU en SQL Server
-- Válido para SQL Server 2008, SQL Server 2005, SQL Server 2000
--
--
-- Notas: 
--  1) Debe especificarse el valor deseado para la variable @count
--     el cual indica el número de muestras que desean tomarse de sysprocesses.
--
--  2) Debe especificarse el valor deseado para la variable @delay,
--     el cual indica el tiempo a esperar entre cada muestra de sysprocesses,
--     debiendo ser un valor múltiplo de un segundo.
--
-- GuilleSQL - Un portal sobre Microsoft SQL Server en Castellano
-- https://guillesql.es
--

declare @delay as datetime
declare @count as int

set @delay='00:00:01'
set @count=10


-- ******************************************************************************
-- ***  Obtener la información de sysprocesses, a través de varias muestras,  ***
-- ***  para poder estimar el reparto del consumo de CPU reciente             ***
-- ******************************************************************************

-- ***  Si existe la tabla temporal => La eliminamos, para luego poder crearla con SELECT INTO  ***
if object_id('tempdb..#tmp_sysprocesses') is not null
   drop table #tmp_sysprocesses


-- ***  Creamos la tabla temporal, con una primera muestra de la info de sysprocesses  ***
select spid, sid, loginame, login_time, dbid, hostname, program_name, hostprocess, cmd, cpu, physical_io, getdate() as actual_date
INTO #tmp_sysprocesses
from  sysprocesses

-- ***  Iteramos el número de veces solicitadas. Para cada iteración:         ***
-- ***     - Esperamos la franja de tiempo solicitada  ***
-- ***     - Almacenamos en la tabla temporal, otra muestra de sysprocesses   ***
while (@count>0)
begin

   WAITFOR DELAY @delay

   insert INTO #tmp_sysprocesses
   select spid, sid, loginame, login_time, dbid, hostname, program_name, hostprocess, cmd, cpu, physical_io, getdate() as actual_date
   from  sysprocesses

   set @count = @count-1
end


-- ****************************************************************************
-- ***  Mostrar la info de las conexiones, con el consumo de CPU realizado  ***
-- ****************************************************************************
select spid, loginame, DB_NAME(dbid) dbname, login_time, hostname, program_name, hostprocess, max(cpu)-min(cpu) cpu, max(physical_io)-min(physical_io) physical_io, count(*) veces
from  #tmp_sysprocesses
group by spid, sid, loginame, login_time, dbid, hostname, program_name, hostprocess
having max(cpu)-min(cpu)>0
order by max(cpu)-min(cpu) desc, max(physical_io)-min(physical_io) desc


-- **************************************************************************************
-- ***  Mostrar el consumo de CPU por cada BBDD, para ayudar a localizar el problema  ***
-- **************************************************************************************
SELECT dbname, sum(cpu) as cpu
from
(
select spid, loginame, DB_NAME(dbid) dbname, login_time, max(cpu)-min(cpu) cpu, max(physical_io)-min(physical_io) physical_io, count(*) veces
from  #tmp_sysprocesses
group by spid, sid, loginame, login_time, dbid, hostname, program_name, hostprocess
having max(cpu)-min(cpu)>0
) DRV_TBL
group by dbname
order by cpu desc


-- select * from #tmp_sysprocesses where spid=208