I will continue to explain how to Troubleshoot Performance problems of SQL Server in this Article Series.
Read previous article before this.
Examine Real time activities in a database using SQL Server Profiler
Using the SQL Server Profiler tool, you can determine what the problem within the SQL Server Database. The following issues can be detected with the SQL ServerProfiler tool.
- TOP Queries
- Trace Database
- Trace Sessions
- Monitoring the status of running Batch jobs such as Stored Procedure
You can run SQL Server Profiler as follows and start the Trace.
Type Trace Name as follows and you can use the old template. And also you can save this Trace file to Disk or Database.
All events that you can trace are like following.
You can trace Database, Locks,Performance, T-SQL, Stored Procedure and more.
For example; If you want to trace SQL Statements, you can enable this event by clicking related CheckBox like following.
Or If you want to trace Locks, then Click Locks section and enable related events by clicking related CheckBox like following.
You can also trace Stored Procedures.
And you can trace all TSQL Statements running at the database by clicking related CheckBox.
You can also filter this Trace result. I recommended you that make sure using Filter to see just only related results.
If you don’t use Filter, then you will see all results. Mostly I am using Duration Filter,DBUserName,DatabaseName and ApplicationName to trace just only see healthy results.
For example, I want to see all Statements that duration is greater than or equal to 5000 (ms). I will trace only statements that duration is greater than 5 sec in this case.
You can trace TOP SQL and duration of Statements using Filter.
When you Click OK button and start Run Trace, Statements and Stored Procedure events will be traced like following.
I will continue to explain SQL Server Performance Troubleshooting in the next article.