I answered a forum post the other day over on SSC on the differences between  the trace events stmtcompleted and batchcompleted. In hindsight I’m not sure that I made a very good job of it, so I thought I would write a blog post to re-enforce and confirm my understanding.

The question went something like this…

“Can you explain the difference between BatchCompleted and StmtCompleted events.”

So I will start by defining the type of events that these could relate too.

T-SQL Events

A category called T-SQL exists in SQL Server profiler and amongst others contains the following two events:

• SQL:StmtCompleted
• SQL:BatchCompleted

T-SQL events…“Includes event classes produced by the execution of Transact-SQL statements passed to an instance of SQL Server from the client.” The following definitions are taken from the help text you get when you hover over each event in profiler:

SQL:StmtCompleted: Occurs when the Transact-SQL statement has completed.

SQL: BatchCompleted: Occurs when the Transact-SQL statement has completed.

From the description here you might be confused that these two events are exactly the same…The demo below shows the difference between these two events.

So what is a batch, a batch is described as one or multiple T-SQL statements executed by SQL Server as a single unit. A batch is separated by GO statements

By definition then a SQL:BatchCompleted event gest captured by the trace when a batch of T-SQL completes

A SQL:stmtCompleted event get captured when one of the  T-SQL statements in a batch completes.

So for example say you have 3 SELECT statements submitted to SQL Server as part of the same batch we will get 3 SQL:StmtCompleted events captured in the trace and 1 SQL:BatchCompleted events.

The demo below demonstrates this

Demo

I have a database I use to hold some contact details of members of my cricket club, mainly used to send email mail shots and news letters. So I have used that for this example. I wrote a simple T-SQL statement that returns members names that I have no email address for and the second T-SQL statement pulls out club members that I have no mobile telephone number for.

The script looks like this:

select name from PlayerContacts
where EMAIL is null

select name from PlayerContacts    
where Mobile is null 

Before we run this code I will setup a trace, using the profiler GUI to trap the execution of this script. I select two T-SQL events from profiler:

With the profiler trace running I executed the SQL script above once and I got the following results recorded in my profiler trace:

As you can see we have two SQL:stmtcompleted events and although its not obvious from the above screenshot each row relates to one of the two SELECT statements above, the TSQL was submitted as one unit of code and was made up of two different SELECT statements, when each individual statement completes the SQL:StmtCompleted event fired.

We have one SQL:BatchCompleted entry in the trace. Both statements were submitted in the same unit of code with no GO statements separating them when the script was executed and hence were part of the same batch. If you look at the TextData column you will see the text of the entire batch - both statements that were executed as part of the same batch of code. So when the query completed we got one SQL:BatchCompleted entry in the trace.

In this case SQL:StmtCompleted captured the completion of each select statement individually. The SQL:BatchCompleted event captured the entire batch (both queries) as a whole.

Stored Procedure Events

There is a set of trace events that relate to and captures the execution of Stored Procedures. The profiler help text provides the following definition

“Includes event classes produced by the execution of stored procedures.”

There is an SP event called SP:stmtCompleted. The help text offers the following information on this event “Indicates that a Transact-SQL statement within a stored procedure has completed.”

We start a new trace with just the SP:StmtCompleted event selected:

We turn the above T-SQL statements into a stored procedure called usp_TestProfiler

We then execute the stored procedure

exec usp_TestProfiler

and we get the following captured in the trace

As you can see, we also have two sp:StmtCompleted events in the trace, again once for each statement that makes up the stored proc.

I’ll now setup a trace that has all three of the events discussed here selected

I will then the run the two select statements as a batch, and we get the following profiler output:

As you can see the results are the same as our first example.

If we execute the stored procedure we get the following profiler output:

As you can see with all three events selected in the trace, the execution of the Stored Procedure results in four events in the profiler trace. We have two SP:StmtCompleted events for the two SELECT statements in the SP. The batch submitted  consists of one TSQL statement executed in management studio So we get one SQL:Stmtcompleted event and one batch completed event, the batch was made up of one statement. That called a Stored procedure, made up of two TSQL statements.