The query processing team – query optimization & execution – providing tips, tricks, advice and answers to freqeuently-asked questions in a continued effort to make your queries run faster and smoother in SQL Server.

Using ETW for SQL Server 2005

ETW stands for “Event Tracing for Windows” and it is used by many Windows applications to provide debug trace functionality.This “wide” availability is a key point of using ETW because it can help to track certain activities from end to end.For example, you can literally track a request coming from IIS, passing through protocol layer, and then finally handled by database engine in single trace file.Unfortunately, not many people know that SQL Server 2005 provides a full ETW functionality which can output most of SQL Trace events available for SQL Server Profiler.In this article, I will explain how to use SQL ETW with examples.

First of all, how do I know if SQL ETW is really active on my machine?The following shows output on my server which has SQL Server 2005 Standard Edition as 2nd instance (named Yukon).

My other server has a default instance of SQL Server 2005 and it shows something like this:

MSSQLSERVER Trace{2373A92B-1C1C-4E71-B494-5CA97F96AA19}

Is there a connection between these 2 examples?Yes, the SQL ETW provider name appears to be a combination of “Instance name” and “Trace”, and its GUID is unique across instances.You can use either provider name or GUID to start and stop ETW session.

Please note this ETW functionality is not available for SQL Express, because, well…, you get what you paid for J.If you don’t see SQL ETW provider name, and you are sure you have a regular SQL Server 2005, then you may be able to fix it yourself.Here’s a short list of instructions.

Now I will describe how to configure SQL ETW to collect interesting traces.Go to the same directory described in the above instruction and find a file called “etwcnf.xml”.This is your configuration file and you can modify it with any text editor. There should several entries already defined in this file for your examples.See the following example picked out of etwcls.xml file.

This defines a tracing template called “TSQL replay” with template ID = 1.It contains a list of events with ID = 11, 13, and so forth.This event ID is matched to SQL Trace Event Class.By consulting BOL documentation on available SQL Trace events, you can find “RPC:Starting” event has ID of 11, and “SQL:BatchStarting” event has ID of 13.Yes, now you know how to change this template to suite your need!

Now let’s try to get some actual trace events.I’m using “logman.exe” and “tracerpt.exe” which should be available on most Windows platform.

First, create a text file as shown in the following example.You can activate multiple providers by listing them in separate lines.For simplicity, let’s enable only SQL ETW.

C:\etw>type prov.txt"YUKON Trace" 1 0

As you have figured out already, the first column is SQL ETW provider name (you can use GUID also).The next number (1) is called “enable flag” and should match the template ID in etwcls.xml file.The 3rd number (0) is called “enable level” and should be kept as zero for SQL ETW.The meaning of these 2 flags depends on providers and you should consult appropriate documentations for other providers.

Now you are ready to start your 1st SQL ETW session!In my example, I decided to give a very creative/original name: “mytrace”.Here’s a screen dump from my machine.

To test if this ETW session can actually receive something from SQL Server, let’s issue “select * from sys.dm_exec_requests”.After that, you can stop the tracing as shown in the following example.

C:\etw>logman stop mytrace -etsThe command completed successfully.

Look for newly created file in the working directory (mytrace.etl). Unfortunately, this is a binary trace file and you will need some help to crack it open.“Tracerpt.exe” does some rudimentary job of converting this binary trace file into human-readable form.

Now we know this ETW session received “SQL:BatchStarting” event correctly, and its TextData column contains correct TSQL statement.

OK, you know how to start and stop SQL ETW.But with very little UI support compared to the pretty looking SQL Server Profiler, why even bother to consider ETW if I don’t need end-to-end tracing?Well, one good thing about SQL ETW is that your ETW session survives SQL Server restart, unlike ordinary SQL Trace.