Correlating client and server-side trace in SQL Server Denali

I'm not usually one for repeating stuff you can find in BOL. Usually, a waste of your time and mine. But this feature, for SQL Server Denali, is pretty well buried; well at least from me, and I knew what I was looking for.

I've always been interested in (well, wrote a whitepaper on) using client-side/network trace (known as BID trace) with server-side trace and coordinating/merging the traces using ETW. Extended Events and even, although its not recommended, even trace events (but DO use Extended Events), support ETW.The biggest drawback to this is that, in order to see the correlation you almost have to be running your traces in a vacuum. There's no client/server correlation ID.

Well, in SQL Server Denali, at least for some clients currently, there IS. Only the SNAC11 provider (that's ODBC and OLE DB) supports this. All the information you need to get you going (if you've done this before, if not, read the whitepaper), is doc'd in BOL here. Two cavaets: the ETW trace for clients contains one (two in some cases) columns related to IDs and correlation. One's called ActivityID, the other RelatedActivityID. The correlation ID is NOT in these, but is in the field after the UserData field. You'll see it as "ActivityID m_id:[some guid]". In the Extended Event (server) trace, it shows up as an "Activity ID Transfer Event". The other cavaet is that its not on every client-side event, only some of them.

I'll be showing client-server tracing at the SQLskills immersion event in Bellevue next week.

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.