SQL Server : Get CPU consuming query

On certain occasions you will have to troubleshoot high CPU consumption on a server. A rogue query would start chewing up CPU resource and you will see that sqlserver.exe will be using up all the CPU. 

Before running a profiler trace to see which queries are consuming more CPU resources than others, run the following TSQL to capture the SPID that is consuming most of the CPU. In the script I have given an interval of 5 secs and if you want you can use an interval on 10 or 15 secs too.

create table #cpu2 (spid int, dbid int, cpu bigint)

create table #cpu (spid int, dbid int, cpu bigint)

insert into #cpu

select spid, dbid, cpu from sysprocesses order by cpu desc

WAITFOR DELAY '00:00:05'

insert into #cpu2

select spid, dbid, cpu from sysprocesses order by cpu desc

select * from sysprocesses where spid in (select t.spid from #cpu2 t, #cpu o 

where t.cpu> o.cpu and t.spid=o.spid) order by cpu desc

drop table #cpu2

drop table #cpu

Run the DBCC inputbuffer with the SPID at the top of the results and you will find the query that is chewing up all the CPUs.

If you want to select the top 10 queries that are currently consuming more CPU than others then use the top 10 SPIDs to find the queries.

This is mostly useful if you have one or two rogue queries. If you have a set of queries that you doubt are using up more resources then run the profiler trace to capture them in real time.

SQL Server : Execute same query against each database or table

How to execute multiple queries against all databases?
Running queries against all the databases?

On several occasion I have had a requirement to run a query against each database or each table, or each index. As long as I can query any system table and create a list of objects I want to run the query for, then you can use the following script.
                                         

DECLARE @Database VARCHAR(255)
DECLARE @cmd varchar(1000)


DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.sys.databases  
WHERE state_desc='ONLINE' and name NOT IN ('master','model','msdb','tempdb','distribution','ReportServerTempDB')   
ORDER BY 1  


OPEN DatabaseCursor  
FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN 
     select 'Current database name is : ', @Database
SET @cmd='use ['+@Database+']; Select * from sys.tables'
EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

You can change the script at the line "SELECT name FROM master.sys.databases " and select anything else you want. Example you want to get a row count on each table then replace that SELECT with

select name from sys.tables 

and the cursor will go through each table.
Now inside the WHILE loop use any command you want. In this case you can have

BEGIN
            Select 'Current Table name is :', @Database
            SET @cmd='select count(*) from '+@Database
            Exec (@cmd)


Obviously you can change the reference to @Database to @Table or anything other object that you want to use. I find this method to be easier than using msforeachdb and msforeachtable since I can run it for all indexes, columns or anything else I want.

Get blocking query

If you want to capture the SPID and the query that is causing blocking then refer to my previous post
http://saveadba.blogspot.com/2012/02/find-query-causing-blocking-extended.html


But I have  come across situations where I am troubleshooting for extended blocking. But unfortunately the queries causing the extended blocking finish execution before I can login to the server. So I have written a script which will capture all the information that I need. I save the output to a file via a job and I email the output to myself. This way I have a record of the queries that cause extended blocking.

I have set up the following script in a job and have configured the job to save the output to a file. Following is a sample of the output which tells me which query is causing blocking and which one is being blocked.


Query #1
run by spid : 62
This is the SPID that is causing the blocking
 hostname                            loginame
--------
SQLServerprd01          Appuser


--------
 Running DBCC INPUTBUFFER for SPID 62
EventType Parameters EventInfo
--------- ---------- ---------
Language Event 0  insert into test...........

Use the following script for this.............................................................................................

use master

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
set nocount on
DECLARE @RefCount varchar(100)
DECLARE @sql1 VARCHAR(4000),@sql2 VARCHAR(4000)
DECLARE @querynum varchar(100)
DECLARE @sqlcmd varchar(8000)
DECLARE @request_status varchar(50)
SET @querynum=1


DECLARE RefCursor CURSOR FOR


SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL))


OPEN RefCursor
FETCH NEXT FROM RefCursor INTO @RefCount
WHILE @@FETCH_STATUS = 0
BEGIN
Select 'Query #'+@querynum+'
run by spid : '+@RefCount
SET @request_status=(SELECT
distinct(l.request_status)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND ( l.resource_description = l1.resource_description OR
(l.resource_description IS NULL AND l1.resource_description IS NULL))
and l.request_session_id=@RefCount)
IF (@request_status='WAIT')
begin
     PRINT 'This is the SPID that is being blocked'
    end
else
begin
     PRINT 'This is the SPID that is causing the blocking'
     end
select ''
SET @sqlcmd='Select hostname from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)


SET @sqlcmd='Select loginame from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)
SET @querynum=@querynum+1
SET @sql2='DBCC INPUTBUFFER('+@RefCount+') with no_infomsgs'
SELECT 'Running DBCC INPUTBUFFER for SPID '+@RefCount
EXEC (@sql2)



SELECT '-----------------------------------------------------------------'


FETCH NEXT FROM RefCursor INTO @RefCount
END
CLOSE RefCursor
DEALLOCATE RefCursor
GO

SQL Server : Backup and restore progress

Ever wondered how long it would take to complete a backup or restore that is currently in progress. Use the following command to find that. It gives you a rough estimate of the amount of time required to complete it.


SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

SQL Server Get status of Backup
SQL Server Get status of Restore
SQL Server Get Backup completion
SQL Server Get Restore completion.