1. Determine where the performance issue is, i.e., front-end tools like Manager; IT Shop; dbqueue.
Turn on SQL logging:
How to Enable TRACE Logging
How to set up SQL-Logs, Job-Generation-Logs and Object-Logs for various Front Ends
2. Identity Manager Performance (including the web portal and DBQueue processing) is very often a result of database performance.
Is the Database server meeting the minimum system requirements? (Please refer to the Release Notes for the specific version)
Version 7.x and above
Execute Select * from qbmvsystemoverview to confirm database server statistics, as well as the One Identity Manager (1IM) version and module information. Please save as .csv file.
A SQL script (provided by Support) can be run to provide information on the SQL server (it won't work for Oracle) and what resources it has, I/O, etc.
3. Is there a long running query (or queries) in the SQL logs? What happens when this is run manually?
Create an actual execution plan for this query (see below).
Execution plans provide feedback for DBAs on why a query may be performing badly, how to improve this, i.e. indexes, defragmentation, etc. DBAs should review the actual execution plan and provide a copy to Support for review.
Ensure this is the actual execution plan: How to: Display an Actual Execution Plan
4. Is the SQL server running on a virtual system?
Identity Manager Database Virtualization Recommendations
Several factors can contribute to how long a synchronization with a target system takes to complete.
- Are there outstanding patches for the synchronization project? In the Synchronization Editor go to Edit | Update synchronization project...
then apply any applicable patches.
- Is the latest service pack installed? Ensure the latest service pack has been applied so that the synchronization is not affected by any resolved issues. Check for existing patches as well for the applicable version: Identity Manager - Download Software
- Support may request the synchronization project for further review: How to export the Synchronization Project shell from the Synchronization Editor
Run the provided Serverinfo.sql (not 1IM version specific), to gather performance values from the SQL server. Customers may send Support the output, and they should also review themselves and compare with the min. requirements for the database, as per the release notes. For version 7.x and above please run Select * from qbmvsystemoverview
The recommended setup values for DBServer are included in the overview where indicated by (rec.), for example:
- maximum degree of parallelism ( rec. 2 <= maxdop <= 2)
- optimize for ad hoc workload (rec. 1)
- Cost Threshold for Parallelism (rec. >= 60)
SQL Server is usually high in I/O activity and in most cases the database is larger than the amount of memory installed on a server and therefore SQL Server has to pull data from the disk to satisfy query requirements. For more information please see: Investigating I/O bottlenecks
Fragmented indexes can cause poor performance. Database Administrators (DBA) should look into any fragmented indexes on affected tables. See also, sys.dm_db_index_physical_stats (Transact-SQL)
The following query will display table indexes, ordered by the highest fragmented:
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC
SQL logs should ALWAYS be gathered for performance related issues. This will confirm if there is some long running query causing the slowdown.
SQL logs can be gathered in various places, depending on the issue. For example, slow response in the web portal requires gathering SQL logging from the web.config, which is not enabled by default. Please refer to the relevant documentation for the version of tool requiring SQL logging. See also How to Enable and Collect IT Shop Logs
Any long running SQL queries (as determined from the SQL logs) should be run manually. And from that the DBA should create an execution plan for analysis.
Execution Plan How-to Topics
Note: Ensure this is the actual execution plan: How to: Display an Actual Execution Plan.
SQL Server and Oracle Database servers should ALWAYS meet the minimum system requirements! Please refer to the system requirements for the specific version of One Identity Manager.
Identity Manager - Technical Documentation
Enabling Trace flag 2453 may improve overall database performance. The Database Journal may report this, "Trace flag 2453 should be enabled." Steps to enable this are included in Knowledge Base Article 226333, What does "Trace flag 2453 should be enabled." mean?