Find Queries using High CPU

There are times when you would want to know what query in SQL Server may be causing CPU spikes. There are many reason why a query may hit the CPU and the best way to troubleshoot it to:

  1. Identify the query that is causing high CPU
  2. Identify the total time taken by query to execute that query
  3. Find out when was the last time the query was executed
  4. Obtain Query execution plan for the offending query

There are many scripts available on the internet and many-a-times that confuses the DBA/Developer since there’s not much information on what each column value contains.

Here’s a query that would provide you with the top 50 queries that are hitting the CPU:

--Author: Saleem Hakani (
--Query to find top 50 high CPU queries and it's details
	Convert(varchar, qs.creation_time, 109) as Plan_Compiled_On, 
	qs.execution_count as 'Total Executions', 
	qs.total_worker_time as 'Overall CPU Time Since Compiled',
	Convert(Varchar, qs.last_execution_time, 109) as 'Last Execution Date/Time',
	cast(qs.last_worker_time as varchar) +'   ('+ cast(qs.max_worker_time as Varchar)+' Highest ever)' as 'CPU Time for Last Execution (Milliseconds)',
	Convert(varchar,(qs.last_worker_time/(1000))/(60*60)) + ' Hrs (i.e. ' + convert(varchar,(qs.last_worker_time/(1000))/60) + ' Mins & ' + convert(varchar,(qs.last_worker_time/(1000))%60) + ' Seconds)' as 'Last Execution Duration', 
	qs.last_rows as 'Rows returned',
	qs.total_logical_reads/128 as 'Overall Logical Reads (MB)', 
	qs.max_logical_reads/128 'Highest Logical Reads (MB)', 
	qs.last_logical_reads/128 'Logical Reads from Last Execution (MB)',
	qs.total_physical_reads/128 'Total Physical Reads Since Compiled (MB)', 
	qs.last_dop as 'Last DOP used',
	qs.last_physical_reads/128 'Physical Reads from Last Execution (MB)',
	t. 'Query Text', 
	qp.query_plan as 'Query Execution Plan', 
	DB_Name(t.dbid) as 'Database Name', 
	t.objectid as 'Object ID', 
	t.encrypted as 'Is Query Encrypted'
	--qs.plan_handle --Uncomment this if you want query plan handle
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.last_worker_time DESC

Let’s look at each of the columns and what their value means:

 Column NameValue / Meaning
1. Plan Compiled OnThis is the date when the query plan was compiled
2. Total ExecutionsThis shows the number of times the query/sproc was executed since the plan was compiled
3. Overall CPU Time Since CompiledThis is a total amount of CPU time taken for all the executions since the query was compiled
4. Last Execution Date/TimeThis date and time shows the last time the query was executed
5. CPU Time for Last Execution (Milliseconds) This value is the time in milliseconds taken for the query to execute the last time. Note: This column also includes the highest time taken ever since the query plan was compiled)
6. Last Execution Duration This is the same value as the above except that this is broken in to hours, minutes and seconds since converting milliseconds to minutes could be time consuming.
7.  Rows returned Number of rows returned from last query execution
8. Overall Logical Reads (MB) This is an overall logical reads value in MB (This is calculated as # of pages/128)
9. Highest Logical Reads (MB) This is the highest logical read value in MB since the last time query was compiled (This is calculated as # of pages/128)
10. Logical Reads from Last Execution (MB) This is the logical read value in MB from the last query execution (This is calculated as # of pages/128)
11. Total Physical Reads Since Compiled (MB) This is the total number of Physical reads in MB since the query was last compiled. (This is calculated as # of pages/128)
12. Last DOP used This value shows the value of degree of parallelism used during the last query execution
13. Physical Reads from Last Execution (MB) Shows the number of MB physically read from the database file during the last execution
14. Query Text Actual text of the query from the cache
15. Query Execution Plan Actual execution plan of the query generated from cache
16. Database Name Name of the database where the query was run from
17. Object ID Object ID of the query being investigated
18. Is Query Encrypted This shows if the query has encrypted columns

How to create lightweight SQL Trace?

There are times when you would want to capture database activity and the most popular tool that database engineers use is SQL Server Profiler. Note: SQL Server profiler has been deprecated and it is recommended to use Extended Events for tracing any database related activity. However, in this article, we will look into how we can use SQL Server profiler without the need to run from the SQL Server Profiler tool and how we can minimize the performance impact by creating a server side trace.

SQL Server Profiler Impacts Performance

Anytime SQL Server profiler is started, it adds to the performance of the database server. It also impacts the client machine from where it is being run. Then there’s network data round trip between the client and the server. It is recommended that if you MUST run profiler continuously on lower level environments or frequently on-demand, create a server side trace and start and stop the profiler using T-SQL.

In this example, we will look at creating a stored procedure that will help you:

  1. Create and start new trace
  2. Stop the trace
  3. Read the trace data without the need of SQL Server Profiler tool

Since this is just an example, we will capture only two events:

  1. RPC:Completed (Under Stored Procedures)
  2.  SQL:BatchCompleted (Under TSQL)
Create Procedure DBA_TraceManager (@Action varchar(10)) as
--Author:  Saleem Hakani
--This is a silent, light weight, server side trace that will capture RPC:Completed and SQL:BatchCompleted
	If (@Action='Stop')
		--If the trace is running, get the trace id 
		Declare @StopTraceID int
		Select @StopTraceID=ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%'
		If (@StopTraceID is null)
		Print 'This trace is not currently running.'
		If (@StopTraceID is not null)
		exec sp_trace_setstatus @StopTraceID, 0
		exec sp_trace_setstatus @StopTraceID, 2
		Print 'Trace has been stopped.'
	If (@Action='Read')
		Declare @TracePath Varchar(255)
		Select @TracePath=[Path] from Sys.Traces where [Path] like '%DBA_TraceManager%'
		Select * from fn_trace_gettable ( @TracePath,default )
If (@Action ='Start')
--Delete any existing trace
	If Exists (Select ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%')
		Select 'Please either move or delete the existing trace files from ['+Path+']' from sys.traces where status=1 and [Path] like '%DBA_TraceManager%'
		Declare @CheckExistingTraceID int
		Select @CheckExistingTraceID=ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%'
		exec sp_trace_setstatus @CheckExistingTraceID, 0
		exec sp_trace_setstatus @CheckExistingTraceID, 2
	If Not Exists (Select ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%')
	-- Create a Queue
		declare @rc int
		declare @TraceID int
		declare @maxfilesize bigint
		set @maxfilesize = 200 
		exec @rc = sp_trace_create @TraceID output, 2, N'F:\DBA_TraceManager_TraceFiles\DBA_TraceManagerFilesUAT', @maxfilesize, NULL 
		if (@rc != 0) goto error
		-- Set the events
		declare @on bit
		set @on = 1
		exec sp_trace_setevent @TraceID, 10, 1, @on
		exec sp_trace_setevent @TraceID, 10, 9, @on
		exec sp_trace_setevent @TraceID, 10, 2, @on
		exec sp_trace_setevent @TraceID, 10, 10, @on
		exec sp_trace_setevent @TraceID, 10, 6, @on
		exec sp_trace_setevent @TraceID, 10, 11, @on
		exec sp_trace_setevent @TraceID, 10, 12, @on
		exec sp_trace_setevent @TraceID, 10, 13, @on
		exec sp_trace_setevent @TraceID, 10, 14, @on
		exec sp_trace_setevent @TraceID, 10, 15, @on
		exec sp_trace_setevent @TraceID, 10, 16, @on
		exec sp_trace_setevent @TraceID, 10, 17, @on
		exec sp_trace_setevent @TraceID, 10, 18, @on
		exec sp_trace_setevent @TraceID, 10, 35, @on
		exec sp_trace_setevent @TraceID, 10, 48, @on
		exec sp_trace_setevent @TraceID, 12, 1, @on
		exec sp_trace_setevent @TraceID, 12, 9, @on
		exec sp_trace_setevent @TraceID, 12, 11, @on
		exec sp_trace_setevent @TraceID, 12, 6, @on
		exec sp_trace_setevent @TraceID, 12, 10, @on
		exec sp_trace_setevent @TraceID, 12, 12, @on
		exec sp_trace_setevent @TraceID, 12, 13, @on
		exec sp_trace_setevent @TraceID, 12, 14, @on
		exec sp_trace_setevent @TraceID, 12, 15, @on
		exec sp_trace_setevent @TraceID, 12, 16, @on
		exec sp_trace_setevent @TraceID, 12, 17, @on
		exec sp_trace_setevent @TraceID, 12, 18, @on
		exec sp_trace_setevent @TraceID, 12, 35, @on
		exec sp_trace_setevent @TraceID, 12, 48, @on
		--Start the trace since all the counters have been selected above
		exec sp_trace_setstatus @TraceID, 1
	select ErrorCode=@rc

If you need to start this trace automatically every time SQL Server starts, you can schedule a SQL Agent job and select the schedule type from the job schedule option as “Start automatically when SQL Server Agent starts”. This will make sure that the trace starts as soon SQL Server agent service is started.

Note: Make sure to delete or move any old trace files from F:\DBA_TraceManager_TraceFiles\ folder before starting a new trace.

DBA_TraceManager script will help with:

  1. Creating a server side lightweight trace
  2. Can be started and stopped on-demand
  3. Can be started automatically on server reboot (as long as SQL Agent service is set to auto start)
  4. Trace data will be processed at the server side rather than at a client side
  5. Auto rollover the trace file (.trc) when it reaches 200 MB
  6. Has the ability to read the trace data by simply calling the stored procedure and passing @action parameter value as ‘Read’

DBA_TraceManager accepts a parameter and here are the different values it can accept:

  1. @Action=’Start’ – This will allow you to start a new trace. However, make sure the trace file do not exist at the destination folder.
  2. @Action=’Stop’ – Stops the trace that was created and started using the above stored procedure (i.e. DBA_TraceManager)
  3. @Action=’Read’ – Allows you to read trace data while the trace is in-flight. The helps prevent the need of using SQL Server profiler tool to read profiler data.

Is PowerShell installed on SQL Server?

Many database related tasks can be automated using PowerShell. This T-SQL script can be used to check the following:

  1. Is PowerShell installed on your local SQL Server?
  2. Is it enabled for script execution?
  3. What is the version of PowerShell installed on your machine?

You can obtain all the above information using the below T-SQL script. Since we have to obtain information from the server registry and from the output of PowerShell.exe file, we will be using both xp_regread and xp_cmdshell extended stored procedures in this example:

--Check if PowerShell is installed and if yes, what version and if if it's enabled for execution
--Author: Saleem Hakani
Declare @PowerShell_Check Table 
    IsPowershellInstalled int null,
    IsExecutionPolicyEnabled Varchar(20) null,
    CheckPowerShellVersion Varchar(20) null
INSERT INTO @PowerShell_Check (IsPowershellInstalled) Exec master.sys.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\PowerShell\1'
INSERT INTO @PowerShell_Check (IsExecutionPolicyEnabled) EXEC master.dbo.xp_cmdshell N'%WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy"'
INSERT INTO @PowerShell_Check (CheckPowerShellVersion) EXEC master.dbo.xp_cmdshell N'%WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-Host | Format-Table -Property Version"'
If Exists (Select * from @PowerShell_Check where IsPowershellInstalled=1)
    INSERT INTO @PowerShell_Check (IsExecutionPolicyEnabled) EXEC master.dbo.xp_cmdshell N'%WINDIR%\System32\WindowsPowerShell\v1.0\powershell.exe -Command "Get-ExecutionPolicy"'
    If Not Exists (Select IsExecutionPolicyEnabled from @PowerShell_Check where IsExecutionPolicyEnabled in ('RemoteSigned','Unrestricted'))
        Print '[WARNING: Execution of Powershell scripts is disabled on this system]'
        Print 'To change the execution policy, type the following command in Powershell console: Set-ExecutionPolicy RemoteSigned'
        Select 'PowerShell version '+(Select Left(CheckPowerShellVersion,3) from @PowerShell_Check where CheckPowerShellVersion is not null and IsNumeric(CheckPowerShellVersion)=1)+' is installed and is enabled on this machine '+'('+@@ServerName+')'

Note: That the user running the above script must have sysadmin permissions to use both sys,xp_regread and xp_cmdshell.

Copying Database using AS COPY OF in SQL Azure

Copying databases from one server to the other has become the easiest with SQL Azure. Now you can copy the database simply using the CREATE DATABASE statement in SQL Azure.

CREATE DATABASE command using AS COPY OF in SQL Azure creates a snapshot of the source database as of the time of the copy request. You can select the same server or a different server, its service tier and compute size, or a different compute size within the same service tier (edition). After the copy is complete, it becomes a fully functional, independent database. At this point, you can upgrade or downgrade it to any edition. The logins, users, and permissions can be managed independently.

To create a copy of SQLCOMMUNITY database from production server instance called SOURCESQLSERVER to the local server instance as SQLCOMMUNITY_DBCOPY

Create Database <new_database_name> As Copy Of <source_sql_server_name>.<source_database_name>

<new_database_name> = This is the name of the database you will create a copy from the source database
<source_sql_server_name> = This is the name of the source SQL Azure database instance name from where the database will be copied
<source_database_name> = This is the name of the actual database that you want to make a copy from



Note: Always run the above statement from the destination server and make sure that the user is logged in using the same account as Source database instance and has permissions to create a database.