-- 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
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
/****** 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
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