Working with SQL Server Profiler
- The SQL Profiler is the utility that enables you to interact graphically with the SQL Trace application programming interface (API).
- SQL Trace exposes events that can be captured to audit actions, monitor an instance, examine baseline queries, and troubleshoot performance issues
- In the SQL Profiler we can specify the columns of data we wish to capture for a given event.
- Trace output can be limited by applying filters.
- Use SQL Trace for large and long-running traces.
- Use SQL Server Profiler to define traces and then script them for SQL Trace.
When troubleshooting a performance issue with a SQL Server instance such as query performance declining in a consistent pattern.
SQL Trace is primarily used for performance tuning and optimisation efforts. With SQL Trace, you could set up the trace to collect every T-SQL statement executed. With these data collected, you could analyse and view the results to see things such as how long the query took and under what security context it was running.This feature has been superceeded by Extended Events
Extended Events has a highly scalable and highly configurable architecture enabling users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.
The SQL Profiler enables you to capture the query activity on the instance and then correlate the queries with performance counters captured by using System Monitor.
The trace event used to determine when a T-SQL statement has completed are:
- SQL:StmtCompleted - indicates that a T-SQL statement completed.
- RPC:Completed - indicates a remote procedure call (stored procedure) completed.
The SQL Server Profiler events that identify the users involved in a deadlock
The Lock:Deadlock Chain event class is produced for each participant in a deadlock and the Lock:Deadlock event class which is produced when an attempt to acquire a lock is cancelled because the attempt was part of a deadlock and was chosen as the deadlock victim.
Useful Links: sqlauthority: detecting potential bottlenecks with the help of profiler