Improving performance of SQL Agent job history sampling
Published 28 March 2024
Maintenance of msdb.dbo.sysjobhistory is a routine part of administering a SQL Server instance. If you have opted to maintain very large volumes of job history the following index could help to improve the performance of Redgate Monitor's query running on the monitored server:
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE NONCLUSTERED INDEX [rg_job_history] ON [msdb].[dbo].[sysjobhistory]
(
[job_id] ASC,
[instance_id] ASC,
[step_id] ASC
)
WHERE ([step_id]=(0))
GO
You may change the name of the index, Redgate Monitor does not use index hints for this index.