Skip to main content

SQL Server Trace Events

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


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:

TSQL events

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

Profiler results

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.


Popular posts from this blog

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

SQL Server Express v SQL Server Developer Edition

SQL Server Express v  SQL Server Developer Edition
Over the weekend I received the following in an email from Ali Ahmad who asked me some questions about learning SQL Server. We exchanged a couple of emails so I have boiled this down to the most salient points.

Goal: I want to learn SQL Server inside out… for career progression as DBA/BI/data mining. I'm a data analyst and want to learn inside out about data warehousing.
•I understand relational database concepts...
•I have SQL Server 2014 express installed…
•I need to download the adventure works sample in order to play with it.
•So much knowledge on Microsoft website it's easy to get lost… where do I begin?

SQL Server Express v SQL Server Developer
This is interesting. If you want to learn SQL Server inside out including the Business Intelligence suite of applications I would suggest downloading the developer edition of SQL Server which since 2016 has been made available free of charge. Prior to that there was a f…