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

 

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.