Find Queries using High CPU

by Saleem Hakani | Jan 31, 2021 | Uncategorized

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 (http://sqlcommunity.com)
--Query to find top 50 high CPU queries and it's details
 
SELECT TOP 50
	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
Go

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

How to create lightweight SQL Trace?

by Saleem Hakani | Jan 31, 2021 | Uncategorized

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
SET NOCOUNT ON
--Author:  Saleem Hakani
--This is a silent, light weight, server side trace that will capture RPC:Completed and SQL:BatchCompleted
	If (@Action='Stop')
	Begin 
		--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)
		Begin 
		Print 'This trace is not currently running.'
		Return
		End
		If (@StopTraceID is not null)
		Begin 
		exec sp_trace_setstatus @StopTraceID, 0
		exec sp_trace_setstatus @StopTraceID, 2
		Print 'Trace has been stopped.'
		Return
		End
	End
 
	If (@Action='Read')
	Begin
		Declare @TracePath Varchar(255)
		Select @TracePath=[Path] from Sys.Traces where [Path] like '%DBA_TraceManager%'
		Select * from fn_trace_gettable ( @TracePath,default )
	End
 
If (@Action ='Start')
Begin 
--Delete any existing trace
	If Exists (Select ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%')
	Begin 
		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
	End
	If Not Exists (Select ID from sys.traces where status=1 and [Path] like '%DBA_TraceManager%')
	Begin 
	-- 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
	End
 
	error: 
	select ErrorCode=@rc
	End
go

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?

by Saleem Hakani | Jan 31, 2021 | Uncategorized

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
SET NOCOUNT ON
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)
Begin
    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'))
    Begin
        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'
    End
    Else 
    Begin
        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+')'
    End
End

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

by Saleem Hakani | Jan 4, 2021 | Uncategorized

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

Syntax:
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

Example:

CREATE DATABASE [SQLCOMMUNITY_DBCOPY] AS COPY OF [SOURCESQLSERVER].[SQLCOMMUNITY];

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.