CPU Utilization for last 30 minutes

-- Get CPU Utilization History for last 30 minutes (SQL 2008)

SET ANSI_NULLS, QUOTED_IDENTIFIER ON -- Used for when it's in a SQL Agent Job

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

SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],

               SystemIdle AS [System Idle Process],

               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

               DATEADD(ms, -1 * (@ts_now - [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 '%<SystemHealth>%') AS x

         ) AS y

ORDER BY record_id DESC;

--https://social.msdn.microsoft.com/Forums/sqlserver/en-US/38f34421-60b8-4bf4-b779-56e2f1d70cc0/how-to-check-cpu-usage-by-sql?forum=sqldatabaseengine



SQL Job to record CPU Utilization and Number of Users per DB 


USE [msdb]

GO

/****** Object:  Job [Query DB's for connectio usage for further aggregation of data]    Script Date: 10/1/2019 12:38:31 PM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 10/1/2019 12:38:31 PM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Query DB''s for connectio usage for further aggregation of data',

              @enabled=1,

              @notify_level_eventlog=0,

              @notify_level_email=0,

              @notify_level_netsend=0,

              @notify_level_page=0,

              @delete_level=0,

              @description=N'Want to see what the load is like over a weeks time. This job will query the number of users, every 15 mins',

              @category_name=N'[Uncategorized (Local)]',

              @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Query DB's and number of users]    Script Date: 10/1/2019 12:38:31 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Query DB''s and number of users',

              @step_id=1,

              @cmdexec_success_code=0,

              @on_success_action=4,

              @on_success_step_id=2,

              @on_fail_action=2,

              @on_fail_step_id=0,

              @retry_attempts=0,

              @retry_interval=0,

              @os_run_priority=0, @subsystem=N'TSQL',

              @command=N'INSERT INTO

master.dbo.connection_research

SELECT

    DB_NAME(dbid) as DBName,

    COUNT(dbid) as NumberOfConnections,

    loginame as LoginName,GETDATE()

      

FROM

    sys.sysprocesses

WHERE

    dbid > 0

GROUP BY

    dbid, loginame',

              @database_name=N'master',

              @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Query CPU]    Script Date: 10/1/2019 12:38:31 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Query CPU',

              @step_id=2,

              @cmdexec_success_code=0,

              @on_success_action=1,

              @on_success_step_id=0,

              @on_fail_action=2,

              @on_fail_step_id=0,

              @retry_attempts=0,

              @retry_interval=0,

              @os_run_priority=0, @subsystem=N'TSQL',

              @command=N'SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

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

INSERT INTO master.dbo.cpu_usage

SELECT TOP(1) SQLProcessUtilization AS [SQL Server Process CPU Utilization],

               SystemIdle AS [System Idle Process],

               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

               DATEADD(ms, -1 * (@ts_now - [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 ''%<SystemHealth>%'') AS x

         ) AS y

ORDER BY record_id DESC',

              @database_name=N'master',

              @flags=4

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 15 mins',

              @enabled=1,

              @freq_type=4,

              @freq_interval=1,

              @freq_subday_type=4,

              @freq_subday_interval=15,

              @freq_relative_interval=0,

              @freq_recurrence_factor=0,

              @active_start_date=20170918,

              @active_end_date=99991231,

              @active_start_time=0,

              @active_end_time=235959,

              @schedule_uid=N'5efba1ce-feca-419d-b48f-c79e8f673d4e'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

 

USE [master]

GO

/****** Object:  Table [dbo].[connection_research]    Script Date: 10/1/2019 12:39:04 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[connection_research](

       [DBName] [nvarchar](128) NULL,

       [NumberOfConnections] [int] NULL,

       [LoginName] [nchar](128) NOT NULL,

       [dDateTime] [datetime] NULL

) ON [PRIMARY]

GO

 

USE [master]

GO

/****** Object:  Table [dbo].[cpu_usage]    Script Date: 10/1/2019 12:39:17 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[cpu_usage](

       [SQL Server Process CPU Utilization] [int] NULL,

       [System Idle Process] [int] NULL,

       [Other Process CPU Utilization] [int] NULL,

       [Event Time] [datetime] NULL

) ON [PRIMARY]

GO

PIVOT table to retrieve the info in a friendly format

/****** Script for SelectTopNRows command from SSMS  ******/

/*SELECT TOP 1000 [SQL Server Process CPU Utilization]

      ,[System Idle Process]

      ,[Other Process CPU Utilization]

      ,[Event Time]

  FROM [master].[dbo].[cpu_usage]

  */

  SELECT *

FROM (

    SELECT

        'HillRom - Day (AVG CPU)' as [Desc],left(datename(DAY,[Event Time]),3)as [Day],

        [SQL Server Process CPU Utilization] as Amount

    FROM [cpu_usage]

) as s

PIVOT

(

    AVG([Amount])

   

       FOR [Day] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])

)AS pvt

  SELECT *

FROM (

    SELECT

        DBName,left(datename(DAY,[dDateTime]),3)as [Day],

        [NumberofConnections] as Amount

    FROM [connection_research]

) as s

PIVOT

(

    AVG([Amount])

   

       FOR [Day] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])

)AS pvt

Pivot with Weeknum

SELECT *

FROM (

    SELECT

        DATEPART( wk, convert(nvarchar(max),[dDateTime],101)) as WeekNum, DBName as [year],left(datename(DAY,dDateTime),3)as [Day],

        NumberOfConnections as Amount

    FROM connection_research

) as s

PIVOT

(

    MAX(Amount)

    --FOR [month] IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])

       FOR [Day] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])

)AS pvt