SQL Script to Monitor CPU Utilization

This post helps you in understanding and using SQL Script to Monitor CPU utilization. There are certain scenarios where we can quickly use this script to get SQL Server CPU utilization.

• When there is a performance issue, and you need to quickly check the CPU usage
• When doing Root Cause Analysis
• When we only have access to SQL Server not to Windows

SQL Server CPU Utilization history report for last N minutes:

Below is the SQL Script to Monitor CPU utilization. This script captures the CPU usage history report from last 10 min. This can be customized by changing the variable “@lastNmin” value.

/***** Script: SQL Server CPU Utilization report from last N minutes *****/

/***** Support: SQL Server 2008 and Above *****/

/***** Tested On: SQL Server 2008 R2 and 2014 *****/

/***** Output: 

SQLServer_CPU_Utilization: % CPU utilized from SQL Server Process

System_Idle_Process: % CPU Idle - Not serving to any process 

Other_Process_CPU_Utilization: % CPU utilized by processes otherthan SQL Server

Event_Time: Time when these values captured

*****/

DECLARE @ts BIGINT;

DECLARE @lastNmin TINYINT;

SET @lastNmin = 10;

SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info); 

SELECT TOP(@lastNmin)

SQLProcessUtilization AS [SQLServer_CPU_Utilization], 

SystemIdle AS [System_Idle_Process], 

100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization], 

DATEADD(ms,-1 *(@ts - [timestamp]),GETDATE())AS [Event_Time] 

FROM (SELECT record.value('(./Record/@id)[1]','int')AS record_id, 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')AS [SystemIdle], 

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')AS [SQLProcessUtilization], 

[timestamp]      

FROM (SELECT[timestamp], convert(xml, record) AS [record]             

FROM sys.dm_os_ring_buffers             

WHERE ring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR'AND record LIKE'%%')AS x )AS y 

ORDER BY record_id DESC; 

SQL Server Database wise CPU Utilization

From above script we come to know that SQL Server is utilizing high CPU, now the next step is to find out which database from the SQL instance is causing the high CPU utilization. Below is the SQL Script to Monitor CPU Utilization database wise.

/***** Script: Database Wise CPU Utilization report *****/

/***** Support: SQL Server 2008 and Above *****/

/***** TestedOn: SQL Server 2008 R2 and 2014 *****/

/***** Output: 

SNO: Serial Number

DBName: Databse Name 

CPU_Time(Ms): CPU Time in Milliseconds

CPUPercent: Let’s say this instance is using 50% CPU and one of the database is      using 80%. It means the actual CPU usage from the database is calculated as: (80 / 100) * 50 = 40 %

*****/

WITH DB_CPU AS

(SELECT DatabaseID, 

DB_Name(DatabaseID)AS [DatabaseName], 

SUM(total_worker_time)AS [CPU_Time(Ms)] 

FROM sys.dm_exec_query_stats AS qs 

CROSS APPLY(SELECT CONVERT(int, value)AS [DatabaseID]  

FROM sys.dm_exec_plan_attributes(qs.plan_handle)  

WHERE attribute =N'dbid')AS epa GROUP BY DatabaseID) 

SELECT ROW_NUMBER()OVER(ORDER BY [CPU_Time(Ms)] DESC)AS [SNO], 

DatabaseName AS [DBName], [CPU_Time(Ms)], 

CAST([CPU_Time(Ms)] * 1.0 /SUM([CPU_Time(Ms)]) OVER()* 100.0 AS DECIMAL(5, 2))AS [CPUPercent] 

FROM DB_CPU 

WHERE DatabaseID > 4 -- system databases 

AND DatabaseID <> 32767 -- ResourceDB 

ORDER BY SNO OPTION(RECOMPILE); 

SQL Server Query Wise CPU Utilization

From the above scripts you confirmed that there is a huge CPU utilization from one of the SQL Instance and you identified the database which is causing high CPU. Now the next step is to identify top 10 queries which are causing high CPU utilization. Here is the SQL Script to Monitor CPU Utilization query level. This is a wonderful script provided by SQLBlog and SQLKnowlwdge.

Note: Remember that it returns the list of costly queries which causes high CPU utilization when only the CPU usage is >=80% from last 10 Min, otherwise it returns nothing. You can modify the script as per your needs.

/***** Script: Top 10 queries that causes high CPU Utilization *****/

/***** Support: SQL Server 2008 and Above *****/

/***** TestedOn: SQL Server 2008,R2 and 2014 *****/

/***** Output: All query related details *****/

/***** Note: This script returns list of costly queries when CPU utilization is >=80% from last 10 min ****/

SET NOCOUNT ON

DECLARE @ts_now bigint 

DECLARE @AvgCPUUtilization DECIMAL(10,2) 

SELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info

-- load the CPU utilization in the past 10 minutes into the temp table, you can load them into a permanent table

SELECT TOP(10) SQLProcessUtilization AS [SQLServerProcessCPUUtilization]

,SystemIdle AS [SystemIdleProcess]

,100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPU Utilization]

,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime] 

INTO #CPUUtilization

FROM ( 

      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 

            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 

            AS [SystemIdle], 

            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 

            'int') 

            AS [SQLProcessUtilization], [timestamp] 

      FROM ( 

            SELECT [timestamp], CONVERT(xml, record) AS [record] 

            FROM sys.dm_os_ring_buffers 

            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 

            AND record LIKE '%<SystemHealth>%') AS x 

      ) AS y 

ORDER BY record_id DESC

-- check if the average CPU utilization was over 80% in the past 10 minutes

SELECT @AvgCPUUtilization = AVG([SQLServerProcessCPUUtilization] + [OtherProcessCPU Utilization])

FROM #CPUUtilization

WHERE EventTime > DATEADD(MM, -10, GETDATE())

IF @AvgCPUUtilization >= 80

BEGIN

SELECT TOP(10)

CONVERT(VARCHAR(25),@AvgCPUUtilization) +'%' AS [AvgCPUUtilization]

, GETDATE() [Date and Time]

, r.cpu_time

, r.total_elapsed_time

, s.session_id

, s.login_name

, s.host_name

, DB_NAME(r.database_id) AS DatabaseName

, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,

((CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2

ELSE r.statement_end_offset

END - r.statement_start_offset)/2) + 1) AS [IndividualQuery]

, SUBSTRING(text, 1, 200) AS [ParentQuery]

, r.status

, r.start_time

, r.wait_type

, s.program_name

INTO #PossibleCPUUtilizationQueries

FROM sys.dm_exec_sessions s

INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id

INNER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

WHERE s.session_id > 50

AND r.session_id != @@spid

order by r.cpu_time desc

-- query the temp table, you can also send an email report to yourself or your development team

SELECT *

FROM #PossibleCPUUtilizationQueries

END

-- drop the temp tables

IF OBJECT_ID('TEMPDB..#CPUUtilization') IS NOT NULL

drop table #CPUUtilization

IF OBJECT_ID('TEMPDB..#PossibleCPUUtilizationQueries') IS NOT NULL

drop table #PossibleCPUUtilizationQueries

Script to find Top 20 Costliest Stored Procedures – High CPU:

 This script sourced from here. It results a list of stored procedures which are utilizing high CPU. This script goes through the buffer cache and find out these results based on Total and Average worker thread times. Below is the SQL Script to Monitor CPU Utilization from the stored procedure point of view.

/***** Script: Top 20 Stored Procedures using High CPU *****/

/***** Support: SQL Server 2008 and Above *****/

/***** Tested On: SQL Server 2008 R2 and 2014 *****/

/***** Output: 

SP Name: Stored Procedure Name

TotalWorkerTime: Total Worker Time since the last compile time

AvgWorkerTime: Average Worker Time since last compile time

execution_count: Total number of execution since last compile time

Calls/Second: Number of calls / executions per second

total_elapsed_time: total elapsed time

avg_elapsed_time: Average elapsed time

cached_time: Procedure Cached time

*****/

SELECT TOP (20) 

  p.name AS [SP Name], 

  qs.total_worker_time AS [TotalWorkerTime], 

  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 

  qs.execution_count, 

  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],

  qs.total_elapsed_time, 

  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 

  qs.cached_time

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

Script to find Top 20 Costliest Queries – High CPU

 This script sourced from here. It results a list of queries which are utilizing high CPU. Below is the SQL Script to Monitor CPU Utilization from Ad-hoc queries.

Script to find Top 20 Costliest Queries – High CPU

Transact-SQL

/***** Script: Top 20 Stored Procedures using High CPU *****/

/***** Support: SQL Server 2008 and Above *****/

/***** Tested On: SQL Server 2008 R2 and 2014 *****/

/***** Output: Queries, CPU, Elapsed Times, Ms and S ****/

SELECT TOP (20)

    st.text AS Query,

    qs.execution_count,

    qs.total_worker_time AS Total_CPU,

    total_CPU_inSeconds = --Converted from microseconds

    qs.total_worker_time/1000000,

    average_CPU_inSeconds = --Converted from microseconds

    (qs.total_worker_time/1000000) / qs.execution_count,

    qs.total_elapsed_time,

    total_elapsed_time_inSeconds = --Converted from microseconds

    qs.total_elapsed_time/1000000,

    qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

Multiple SQL Query Output in CSV

Approach: We will create a table with multiple columns of any name with datetype as varchar to store any value. Once table is created we will insert the columns header of  the output of your first query then insert the results of the first query. Follow same steps for other queries you want to execute.

For better understanding please check below queries

CREATE TABLE [dbo].[tblABC](
[A] [varchar](200) NULL, [B] [varchar](200) NULL, [C] [varchar](200) NULL, [D] [varchar](200) NULL, [E] [varchar](200) NULL,
[F] [varchar](200) NULL, [G] [varchar](200) NULL, [H] [varchar](200) NULL, [I] [varchar](200) NULL, [J] [varchar](200) NULL,
[K] [varchar](200) NULL, [L] [varchar](200) NULL, [M] [varchar](200) NULL, [N] [varchar](200) NULL, [O] [varchar](200) NULL,
[P] [varchar](200) NULL, [Q] [varchar](200) NULL, [R] [varchar](200) NULL, [S] [varchar](200) NULL, [T] [varchar](200) NULL,
[U] [varchar](200) NULL, [V] [varchar](200) NULL, [W] [varchar](200) NULL, [X] [varchar](200) NULL, [Y] [varchar](200) NULL,
[Z] [varchar](200) NULL
) ON [PRIMARY]
GO


Declare @aa varchar(100)
set @aa =cast(getdate() as varchar)

INSERT INTO [dbo].[tblABC] (A)
(Select 'Report Generated on  '+ @aa)


INSERT INTO [dbo].[tblABC] (A) Values
('Server :  ' + @@SERVERNAME)
INSERT INTO [dbo].[tblABC] (A) Values
('Instance  :  '+ @@SERVICENAME)

INSERT INTO [dbo].[tblABC] (A) values
(' ')

INSERT INTO [dbo].[tblABC] (A) values
('>>>>>>>>>>>>>Instance Logins<<<<<<<<<<<<<<<')

INSERT INTO [dbo].[tblABC] (A) values
(' ')

INSERT INTO [dbo].[tblABC] (A,B,C) Values
('name','dbname','language')

INSERT INTO [dbo].[tblABC] (A,B,C)
(select name,dbname,language  from syslogins)

INSERT INTO [dbo].[tblABC] (A) values
(' ')

INSERT INTO [dbo].[tblABC] (A) values
('>>>>>>>>>>>>>Database details<<<<<<<<<<<<<<<')

INSERT INTO [dbo].[tblABC] (A) values
(' ')

INSERT INTO [dbo].[tblABC] (A,B,C,D,E,F,G,H,I,J) Values
('name','database_id','create_date','compatibility_level','collation_name','user_access_desc','state_desc',
'snapshot_isolation_state','recovery_model','recovery_model_desc')

INSERT INTO [dbo].[tblABC] (A,B,C,D,E,F,G,H,I,J)
(Select name,database_id,create_date,compatibility_level,collation_name,user_access_desc,state_desc,snapshot_isolation_state,
recovery_model,recovery_model_desc from sys.databases)

Inserted Data will look like this

Select * from [dbo].[tblABC]

To avoid null run below query

SELECT isnull([A] ,'') as A      ,isnull([B] ,'') as B      ,isnull([C] ,'') as C      ,isnull([D] ,'') as D      ,isnull([E] ,'') as E
      ,isnull([F] ,'') as F      ,isnull([G] ,'') as G      ,isnull([H] ,'') as H      ,isnull([I] ,'') as I      ,isnull([J] ,'') as J
      ,isnull([K] ,'') as K      ,isnull([L] ,'') as L      ,isnull([M] ,'') as M      ,isnull([N] ,'') as N      ,isnull([O] ,'') as O
      ,isnull([P] ,'') as P      ,isnull([Q] ,'') as Q      ,isnull([R] ,'') as R      ,isnull([S] ,'') as S      ,isnull([T] ,'') as T
      ,isnull([U] ,'') as U      ,isnull([V] ,'') as V      ,isnull([W] ,'') as W      ,isnull([X] ,'') as X      ,isnull([Y] ,'') as Y
      ,isnull([Z] ,'') as Z
  FROM [master].[dbo].[tblABC]

Output will be like this

Now run below query in PowerShell which will export above data to CSV and skip the columns named A,B,C,D......

$Query = "
SELECT isnull([A] ,'') as A      ,isnull([B] ,'') as B      ,isnull([C] ,'') as C      ,isnull([D] ,'') as D
      ,isnull([E] ,'') as E      ,isnull([F] ,'') as F      ,isnull([G] ,'') as G      ,isnull([H] ,'') as H
      ,isnull([I] ,'') as I      ,isnull([J] ,'') as J      ,isnull([K] ,'') as K      ,isnull([L] ,'') as L
      ,isnull([M] ,'') as M      ,isnull([N] ,'') as N      ,isnull([O] ,'') as O      ,isnull([P] ,'') as P
      ,isnull([Q] ,'') as Q      ,isnull([R] ,'') as R      ,isnull([S] ,'') as S      ,isnull([T] ,'') as T
      ,isnull([U] ,'') as U      ,isnull([V] ,'') as V      ,isnull([W] ,'') as W      ,isnull([X] ,'') as X
      ,isnull([Y] ,'') as Y      ,isnull([Z] ,'') as Z
  FROM [master].[dbo].[tblABC]
"
$filee="C:\"+"$(get-date -f MM-dd-yyyy)_Report.csv"
write-host $filee
$results = Invoke-SQLcmd -Server CHRV4204 -Database master -Query $Query |
ConvertTo-Csv -NoTypeInformation |Select-Object -Skip 1 |Set-Content -Path $filee

Above script can be execute as job in SQL Server as below

To send attachment via email you can use below script

declare @aa varchar(333) set @aa= convert(varchar, getdate(), 110) select @aa = 'C:\Data\' + @aa + '_Report.csv' select @aa EXEC msdb.dbo.sp_send_dbmail @profile_name = 'TestMailprofile', @recipients ='youremail@gmail.com', @subject = 'test', @body = 'hi', @importance = 'HIGH', @file_attachments = @aa, @body_format ='HTML' --because

In Excel your output will be like this

Find SQL Server Port

DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER'))

--For SQL Server 2000
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8
BEGIN
if @InstanceName='MSSQLSERVER'
Begin
SET @RegKey='SOFTWARE\Microsoft\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\'
END
ELSE
BEGIN
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\'
END

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'TcpPort',
  @value = @value OUTPUT

Select @@SERVERNAME as ServerName,@value as PortNumber
END

--For SQL Server 2005 and up
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_InstanceName,
  @value_name = @InstanceName,
  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'TcpPort',
  @value = @value OUTPUT

Select @@SERVERNAME as ServerName,@value as PortNumber
END

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
OR


DECLARE @InstName VARCHAR(16)

DECLARE @RegLoc VARCHAR(100)

SELECT @InstName = @@SERVICENAME

IF @InstName = 'MSSQLSERVER'
  BEGIN
    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
  END
 ELSE
  BEGIN
   SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
  END

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'

Important Links

Powershell

https://www.simple-talk.com/sql/database-administration/the-posh-dba-accessing-sql-server-from-powershell/

To run 70 queries on server to check everything

http://www.sqlstad.nl/

To find linked server password

https://github.com/NetSPI/Powershell-Modules/blob/master/Get-MSSQLLinkPasswords.psm1

awesome free tools

https://dbatools.io/getting-started/

5 minutes SSAS Admin for SQL DBAs

Often, a SQL DBA has to administer SSAS. Here’s a quick 5 mins guide.

1. How to process a cube?In SSMS, right click cube name, process, OK.

2. When was the cube last processed?File, New, DMX query, select  * from $system.mdschema_cubes

3. Who is using the cube right now?File, New, DMX query, select * from $system.discover_connection

4. How do I give a user access to the cube?Put the user into a new AD group. In SSMS, + on CubeName, right click Role, new role, Membership, Add, type the AD group name, click Cubes on the left pane, Access = Read

5. How big is the cube?The size of this folder: C:\Program Files\Microsoft SQL Server\MSAS11.SSAS_MD\OLAP\Data\CubeName.0.db
   Easier but less accurate: in SSMS, right click cube name, properties, Estimated Size.

6. How do I deploy cube changes to production?In Dev server, right click the Cube name, script database as, create, new window. Change the <Database Name? to “x_New”, and the connection string (Search “ConnectionString” to find it). Save it as x.xmla. Change connection to Production, F5 to execute the script. Right click the new cube, Process, OK. After the new cube is processed, delete the old cube and rename the new cube.
   If you have secondary Prod server, use Synchronize: right click the cube on SSMS, Synchronize. This will copy SSAS files from Secondary Server to Primary Server incrementally (only changed files are copied). You need to process the cube first in the Secondary Server.

7. How long does it take to process the cube?Open SQL profiler, File, new trace, specify SSAS server, Event: Progress Report Begin and End, column filter: DatabaseName like xxx.

8. How do I process a cube every night?In SSMS, right click cube name, process, script. Create a new SQL Agent job, new job step, type = AS Command, command = paste the script.

9. How do I track who’s using the cube every day?Setup query log table. See this article: link

10. Server spec for SSAS?Start with 16 GB, quad core, 100 GB disk. That should be ok for cube size 5-10 GB.

11. How do I limit what each user can see in the cube?In SSMS, + on Cube Name, right click Role, new role, Dimension Data, select the dimension, tick the number you want the users to see.

12. Error when processing the cube: the attribute key was not found.In SSDT, in Solution Explorer pane, double click the Dimension, look at the DSV panel. Make sure all the dimension key values in the fact table exist in the dimension table. If your dimension is made up of 2 tables chained together, make sure the referenced value exist in the referenced table.

13. How do I find out the MDX of the query?Install OLAP Pivot Table Extension: link (thanks to Greg Galloway)

14. How do I browse a cube?
    In SSMS, right click the cube name (not the database name), Browse.
    Better: open Excel, Data menu, From Other Sources, Analysis Services, type the SSAS Server Name, Next, select the cube name, Finish.

15. How do I query the cube data using SQL?
    select * from openquery(LinkedServer1, ‘select Measure1 on columns from Cube1′).
    LinkedServer1 is setup as Provider: OLE DB for SSAS 11, Product Name: MSOLAP.5, Data source: AS Server Name, Catalog: AS DB Name.

16. The cube is very slow
    Put a trace on SQL Profiler to capture all MDX that hit the cube. Find the long running MDX. Optimize using cube partitioning, aggregation, look at cell-by-cell calculation, check cell-based security, check server memory, use cache warming, don’t process the cube during office hours.