Useful Events in Profiler

By : Kasim Wirama, MCDBA

Profiler is excellent tool provided to monitor SQL Server performance and troubleshoot SQL Server database objects. If you open profiler, you will be presented with many events, you don’t have the use most of the events to do particular task. I will show you most used events here :

1.       Errors and Warnings event category

To get to know whether there are time out connections, you can choose Attention event. If you need to have errors raised from stored procedure/function/SQL batch, choose Exception and User Error Message event.

2.       Locks event category

If you want to monitor whether deadlock happens on your SQL, choose DeadLock Chain event and DeadLock Graph (only in SQL Server 2005). Deadlock graph provides visual representation of deadlock, I see that the visual representation provides valuable information and it is nice feature. If you need to get information how long a resource is held before deadlock occurs and kinds of lock during the deadlocked transaction, you can install Lock:Acquired, Lock:Released, Lock:Escalation events.

3.       Performance event category

In terms of performance tuning, to get statistics information and number of actual row counts, ShowPlan XML Statistics Profile event is the most useful one.

4.       Security Audit event category

For security monitoring, such as failed login attempts, access to specific database objects, or restarting server, use events in the category. The other nice thing that SQL Server 2005 provides is that the capability to check whether a connection is coming from pooled connection, by looking at value 2 at EventSubClass of Event Login and Event Logout.

5.       Stored Procedures event category

With point 3 and to capture RPC (remote procedure call) information, you need to install RPC:Starting and RPC:Completed. And to trace nested and complex stored procedure, it is necessary to install SP:StmtStarting and SP:StmtCompleted. Be careful these 2 events, they will burden I/O because they produces a bunch of information especially in busy server.

To get to know whether you SQL Server suffers under excessive recompilation and the stored procedures that dominate recompilation, you can look recompilation information from SP:Recompile.

6.       TSQL event category

To get how much a query batch consuming CPU and I/O read-write and how long it completes the execution, you can get the information from SQL:BatchStarting and SQL:BatchCompleted events and include Duration, Reads, Writes and CPU column.

7.       Transactions event category

To track that transaction information such as no commit/rollback to the corresponding begin transaction with given spid, you can look at SQLTransaction event and it gives the information at EventSubClass column with 0,1 or 2 (begin transaction, commit or rollback transaction).