• State Suggested Answer
  • Replies 1 reply
  • Answers 1 answer
  • Subscribers 99 subscribers
  • Views 260 views
  • Users 0 members are here
Options
Related

DBQueue Performance Tuning and SQL Server Parallelism Settings

kilitac889

Hello colleagues,

I'm seeking advice on performance tuning for One Identity Manager 9.2 and SQL Server to fully utilize our new hardware resources.

Environment:

One Identity Manager: 9.2
SQL Server: Microsoft SQL Server 2022 Enterprise Edition, version 6.0.4210.1.
Hardware: Dedicated database server. 2 sockets, 64 physical cores (128 logical threads).
Load Pattern: Average daily CPU utilization is 15-20%. Short peaks up to 45% (dynamic role calculation, bulk operations).

Situation:
After migrating the database to new, powerful hardware, we observed a performance improvement in DBQueue processing, but it's less significant than expected. The system does not leverage the full hardware potential. Our goal is to optimize settings for faster DBQueue processing and overall system throughput.

Current SQL Server Settings (based on System Information recommendations):

1. max degree of parallelism (MAXDOP): Set to 22. System Information recommendation: 27.

2. cost threshold for parallelism: Set to 60. System Information recommendation: >= 60.

My Questions:

1. MAXDOP: The KB article How to configure settings as per the System Information overview ( support.oneidentity.com/.../how-to-configure-settings-as-per-the-system-information-overview ) recommends a value of 2 - "The default value of zero for parallelism allows all processor cores to be utilized. This may lead to issues with the DBQueue processing.  A value of two (2) is recommended.", while our System Information recommends 27. How appropriate is our value of 22 for this configuration? Should we decrease it following the KB article's guidance or increase it to the recommended 27? How to find the balance between query parallelism and DBQueue efficiency?

2. Cost Threshold for Parallelism: How optimal is the value of 60 as a starting point for our powerful configuration? Should we consider increasing it?

3. Best Practices: What other SQL Server settings or optimization techniques have you found most effective for speeding up DBQueue and overall One Identity Manager performance on such high-end hardware?

Thank you in advance for sharing your insights and experience!

  • 0

    Great question i'm not an SQL expert by any means.
    But if you look at the sql logic behind the view: qbmvsystemoverview
    Then you will see that they use a simple formula for the sweet spot calculation:

    Example = 128 cpu

    DBServer level:
    Maxvalue = ceiling((cpucount + 1.0) * 0.4)
    MaxValue <= 3 THEN '2'
    ELSE MaxValue - 2) / 2 + 2)
    RecommendedValue = 27

    Database level:
    Maxvalue = ceiling((cpucount + 1.0) * 0.4)
    RecommendedValue = 52

    The comment about "A value of two (2) is recommended." in this case is only valid when your system has 6 or less cpus
    So I guess the KB needs to be updated.

    For the Cost settings it's just a fixed value of 60
    cpu count is not taken into account for this one.

    When you look at the system requirements for OneIM
    16 physical cores with 2.5 GHz+ frequency (production)
    Then you could question if the recommended values are also valid for systems with 17 cores and up.
    I guess they are, but maybe someone from quest can eleborate on this.

    All you ever wanted to know about: SQL Day 2023 - PARALLELISM IN MICROSOFT SQL SERVER