Profiler recommendations

<< Click to Display Table of Contents >>

Navigation:  Automation Server > Automation Server configuration and administration guide > System maintenance and monitoring > Database maintenance >

Profiler recommendations

Overview

When you need to troubleshoot and diagnose to follow up a specific issue on your SQL Server, you can use profiling tools as provided by the database engine vendor itself.

When using SQL Server, you can rely on its Profiler tool as available in the SQL Server Management Studio.

 

You can rely on a specialized tool for diagnostics.

For more information refer to Bizagi Diagnostics.

 

Recommendations

 

1. Do not execute the profiler tools from within the database server directly.

 

2. Similarly and when applicable, run a profiler trace on the database at times when the system is not extremely busy.

It may be useful to run a profiler as well during busy hours depending on the issue, but avoid affecting the system's performance and plan this accordingly (taking necessary precautions).

 

3. Filter the profiler appropriately.

 

4. Capture information from the profiler by enabling the relevant (and only those strictly needed) events.

For instance, for SQL Server you may consider RPC:Completed and TSQL-SQL:BatchCompleted.

 

5. Capture information from the profiler by enabling the relevant (and only those strictly needed) columns of information.

Analyze which aspects are key for reviewing database performance in your project.

For instance, for SQL Server you may want to consider: CPU usage, number of write and read instructions, duration, and start and end timestamps.

 

6. You can also use filters to narrow down the traces to what is really important.

For instance, you may want to record: only those transactions having a duration greater than a given threshold (i.e, 3 seconds), or only those transactions affecting a really large number of records.

 

Additional notes

In addition to database tuning and monitoring, consider monitoring tasks for other elements of your system architecture that may interfere with the adequate working of your database (such as domain configuration when used by your database cluster setup, the state of the network between your database servers and other elements of your system architecture such as the Bizagi server or the SAN, etc).

For more information refer to Monitoring.

 

note_pin

We do not recommended that you use antivirus software to scans physical database files.

This may generate locks and delays for applications persisting information into the database.