Troubleshooting Performance Issues (201696)

Return

Feedback submitted.

Was this article helpful?

[Select Rating]

Title

Troubleshooting Performance Issues
Description

Some initial steps and information to gather to assist with troubleshooting performance issues.
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

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.

Version 6.1.x

The serverinfo SQL script we have (attached) will provide information on their 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.

Note: 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

Please note that there are great improvements with version 6.1.3 (when upgrading from an earlier 6.1.x build) in performance.

Database Performance:

Run the attached, 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.

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.

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_count
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

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.

The recommended setup values for DBServer are:

- Maximum degree of parallelism: 2
- Minimum size of server memory MB: approx 50% of RAM
- Maximum size of server memory MB: instead of 'all', better to give RAM minus 1GB reservation for OS
- optimize for ad hoc workload: enable
- Cost Threshold for Parallelism: 60

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 Plan How-to Topics

Note: Ensure this is the actual execution plan: How to: 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.

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 Knowledgebase Article 226333, What does "Trace flag 2453 should be enabled." mean?
Attachments

Serverinfo
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