Return
-
Title
Troubleshooting Performance Issues -
Description
Some initial steps and information to gather to assist with troubleshooting performance issues.
Please provide all relevant information to Support when opening a new service request. -
Resolution
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 Logging
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)
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 Overview
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.
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 Recommendations
Synchronization 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 Editor
Database 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 vfsJOIN sys.master_files AS mfON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_idORDER BY [Average Total Latency] DESC
The following values may help to evaluate the disk performance against latency:Average Total Latency (ms) Rating <1 ms Excellent <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:SELECT OBJECT_NAME(ips.OBJECT_ID),i.NAME,ips.index_id,index_type_desc,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER 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 Logging:
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.
Execution Plan
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 Plans
Note: Ensure this is the actual execution plan: Display an Actual Execution Plan.
System Requirements
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
Trace Flag
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? -
Attachments
-
Additional Information
Further reading: Are I/O latencies killing your performance? Do Not Forget to Maintain Your Indexes Improve SQL performance – find your missing indexes Regarding Serverinfo.sql: Please note: this applies to MS SQL Server only! If there are column errors, e.g.: Msg 207, Level 16, State 1, Line 11 Invalid column name 'physical_memory_in_bytes'. Msg 207, Level 16, State 1, Line 19 Invalid column name 'virtual_memory_in_bytes'. Run select * from sys.dm_os_sys_info i to confirm the correct column names.