Hello All,
I'm experiencing persistent blocking issues in SQL Server 2022, specifically related to the sp_help_jobhistory
and sp_jobhistory_row_limiter
procedures. Here’s the background and the steps I've taken so far:
Background:
- SQL Agent History Settings: Maximum job history log size set to 9,999,999 rows and maximum job history rows per job set to 2,000 rows.
- High-Frequency Jobs: Several jobs run every minute or even more frequently, causing these procedures to run very often.
Issues:
- Blocking with sp_help_jobhistory: Frequent execution of this procedure is causing lead blocks, impacting performance.
- Blocking with sp_jobhistory_row_limiter: This procedure, which maintains job history within configured limits, is also causing blocks.
Steps Taken:
Reduced Job History Size:
EXEC msdb.dbo.sp_set_sqlagent_properties
@jobhistory_max_rows = 100000,
@jobhistory_max_rows_per_job = 1000;
Optimized Indexes:
CREATE INDEX idx_job_id ON msdb.dbo.sysjobsteps(job_id);
CREATE INDEX idx_job_id_step_id ON msdb.dbo.sysjobhistory(job_id, step_id);
CREATE INDEX idx_job_id_run_date ON msdb.dbo.sysjobhistory(job_id, run_date);
- Are there any additional optimizations or best practices to reduce blocking issues with
sp_help_jobhistory
and sp_jobhistory_row_limiter
?
- Is there a way to further optimize the job history maintenance process to avoid conflicts and improve performance?
- Are there any known issues or limitations with SQL Server 2022 related to these procedures?
Any insights or suggestions would be greatly appreciated!