Increasing 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

This article outlines performance tuning recommendations for Microsoft SQL Server databases supporting IDM (Identity Manager).

These configurations address issues where slow database response times result in tasks becoming 'stuck in progress'

Environment

Identity Suite 14.x

Cause

Database performance bottlenecks—such as improper memory allocation, tempdb contention, or non-optimal power settings—can cause transaction delays, leading to IDM tasks failing to process within expected timeframes.

Resolution

Optimize your MS SQL configuration by following these recommended practices:

Memory Configuration

1. Enable Lock Pages in Memory: This setting prevents the operating system from paging memory to disk. See Enable Lock Pages in Memory (Microsoft).

Database and TempDB Optimization

1. Optimize tempdb: Increase the number of tempdb data files to reduce contention. If your logical processors are ≤ 8, use the same number of data files as processors. If > 8, use 8 files. Increase by multiples of 4 if contention persists. See Recommendations to reduce allocation contention.
2. Enable Instant File Initialization: This accelerates the creation of database files. See Database Instant File Initialization (Microsoft) and TempDB Configuration (Microsoft).

Server Power Settings

1. Set Power Plan: Ensure the Windows Power Plan is set to 'High Performance' to prevent CPU throttling. See Slow performance on Windows Server (Microsoft).

Additional Information

To speak with a customer representative or a Support Engineer see Contact Support. Scroll to the bottom of the page and click on your respective region.