How to increase performance of MS SQL to prevent tasks stuck in progress

Products

• CA Identity Suite
• CA Identity Manager
• CA Identity Portal
• CA Identity Governance

Issue/Introduction

Tasks are not being processed quickly enough within IDM resulting in tasks becoming stuck in progress.

Environment

Release : 14.X

Component : CA IDENTITY SUITE (VIRTUAL APPLIANCE)

Cause

Inefficiencies with the MS SQL Database

Resolution

Enable Lock Pages in memory - Locking pages in memory may boost performance when paging memory to disk is expected.

• https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver15

The tempdb database has only 1 data file - Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. As a rule, if the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors. If the number of logical processors is greater than eight (8), use eight data files. If contention continues, increase the number of data files by multiples of four (4) up to the number of logical processors until the contention is reduced to acceptable levels. Alternatively, make changes to the workload or code.

• https://docs.microsoft.com/en-us/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

Enable Database Instant File Initialization

• https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver15
• https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15

Check the Power Plan of the server, it should be configured as High Performance:

• https://docs.microsoft.com/en-us/troubleshoot/windows-server/performance/slow-performance-when-using-power-plan