Troubleshooting steps/questions: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 Logging2. 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)
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.
See also: How to configure settings as per the System Information Overview3. 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.Note:
Ensure this is the actual execution plan: Display an Actual Execution Plan
.4. Is the SQL server running on a virtual system?Identity Manager Database Virtualization RecommendationsSynchronization Performance
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 EditorDatabase Performance:
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)Input/Output (I/O):
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
The following query will provide latency values for databases. The Average Total Latency column represents the total latency.
SELECT DB_NAME(vfs.database_id) AS database_name ,physical_name AS [Physical Name],
size_on_disk_bytes / 1024 / 1024. AS [Size of Disk] ,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [Average Read latency] ,
CAST(io_stall_write_ms/(1.0 + num_of_writes) AS NUMERIC(10,1)) AS [Average Write latency] ,
CAST((io_stall_read_ms + io_stall_write_ms)
/(1.0 + num_of_reads + num_of_writes)
AS NUMERIC(10,1)) AS [Average Total Latency],
num_of_bytes_read / NULLIF(num_of_reads, 0) AS [Average Bytes Per Read],
num_of_bytes_written / NULLIF(num_of_writes, 0) AS [Average Bytes Per Write]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY [Average Total Latency] DESC
The following values may help to evaluate the disk performance against latency:
|Average Total Latency (ms)||Rating|
|<5 ms||Very good|
|<5 – 10 ms||Good|
|< 10 – 20 ms||Poor|
|< 20 – 100 ms||Bad|
|<100 ms -500 ms||Very Bad|
|> 500 ms ||Awful|
Please see SQL Server troubleshooting: Disk I/O problems
for more information.Fragmentation:
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.
Note: Ensure this is the actual execution plan: 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?