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