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

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:



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:



SPStmtCompleted



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



SPStmtCompletedOutput



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



Allevents



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



AlleventsResBatch



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:



AlleventsSPOutput



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.

Comments

Popular posts from this blog

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…

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…

How to Setup Kerberos Correctly

David was in Copenhagen this weekend delivering his Kerberos talk Taming the Beast: Kerberos for the SQL DBA to SQL Saturday Denmark. I have had a quick chat with him via email since he got back and he said he had a great time. The event was very well attended with 280+ attendees and his talk was well attended.

I think David is planning submitting a few sessions to SQL Saturday events in Europe in the next few months so look out for him there and we'll keep you posted as to his whereabouts when schedules get finalised later in the year.

David has pre-recorded his Kerberos talk. You can watch on you tube and I have also embedded it in this post if you want to see what his kerberos talk  covers...



If we can help you with a SQL Sever problem visit our SQL Server Consulting page or contact us