{"id":537,"date":"2011-08-19T11:28:00","date_gmt":"2011-08-19T11:28:00","guid":{"rendered":"\/blogs\/bobb\/post\/Correlating-client-and-server-side-trace-in-SQL-Server-Denali.aspx"},"modified":"2014-01-20T12:15:02","modified_gmt":"2014-01-20T20:15:02","slug":"correlating-client-and-server-side-trace-in-sql-server-denali","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/","title":{"rendered":"Correlating client and server-side trace in SQL Server Denali"},"content":{"rendered":"<p>\nI&#39;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.\n<\/p>\n<p>\nI&#39;ve always been interested in (well, wrote a <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc765421(SQL.100).aspx\">whitepaper<\/a> 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&#39;s no client\/server correlation ID.\n<\/p>\n<p>\nWell, in SQL Server Denali, at least for some clients currently, there IS. Only the SNAC11 provider (that&#39;s ODBC and OLE DB) supports this. All the information you need to get you going (if you&#39;ve done this before, if not, read the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc765421(SQL.100).aspx\">whitepaper<\/a>), is doc&#39;d in BOL <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/hh213095(SQL.110).aspx\">here<\/a>. Two cavaets: the ETW trace for clients contains one (two in some cases) columns related to IDs and correlation. One&#39;s called ActivityID, the other RelatedActivityID. The correlation ID is NOT in these, but is in the field after the UserData field. You&#39;ll see it as &quot;ActivityID m_id:[some guid]&quot;. In the Extended Event (server) trace, it shows up as an &quot;Activity ID Transfer Event&quot;. The other cavaet is that its not on every client-side event, only some of them.\n<\/p>\n<p>\nI&#39;ll be showing client-server tracing at the SQLskills <a href=\"http:\/\/www.sqlskills.com\/immersioneventsschedule.asp\" class=\"broken_link\">immersion event in Bellevue<\/a>&nbsp;next week.\n<\/p>\n<p>\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#39;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&#39;ve always been interested in (well, wrote a whitepaper on) using [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,15,31],"tags":[],"class_list":["post-537","post","type-post","status-publish","format-standard","hentry","category-data-access","category-extended-events","category-sql-server-2012"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Correlating client and server-side trace in SQL Server Denali - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Correlating client and server-side trace in SQL Server Denali - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"I&#039;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&#039;ve always been interested in (well, wrote a whitepaper on) using [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2011-08-19T11:28:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-20T20:15:02+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/\",\"name\":\"Correlating client and server-side trace in SQL Server Denali - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2011-08-19T11:28:00+00:00\",\"dateModified\":\"2014-01-20T20:15:02+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Access\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Correlating client and server-side trace in SQL Server Denali\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Correlating client and server-side trace in SQL Server Denali - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/","og_locale":"en_US","og_type":"article","og_title":"Correlating client and server-side trace in SQL Server Denali - Bob Beauchemin","og_description":"I&#39;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&#39;ve always been interested in (well, wrote a whitepaper on) using [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/","og_site_name":"Bob Beauchemin","article_published_time":"2011-08-19T11:28:00+00:00","article_modified_time":"2014-01-20T20:15:02+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/","name":"Correlating client and server-side trace in SQL Server Denali - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2011-08-19T11:28:00+00:00","dateModified":"2014-01-20T20:15:02+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/correlating-client-and-server-side-trace-in-sql-server-denali\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Data Access","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/"},{"@type":"ListItem","position":3,"name":"Correlating client and server-side trace in SQL Server Denali"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/537","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=537"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/537\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=537"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=537"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=537"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}