{"id":874,"date":"2006-03-03T08:38:00","date_gmt":"2006-03-03T08:38:00","guid":{"rendered":"\/blogs\/bobb\/post\/Move-over-developers!-SQL-Server-XQuery-is-actually-a-DBA-tool.aspx"},"modified":"2013-01-07T09:00:23","modified_gmt":"2013-01-07T17:00:23","slug":"move-over-developers-sql-server-xquery-is-actually-a-dba-tool","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/","title":{"rendered":"Move over developers! SQL Server XQuery is actually a DBA tool"},"content":{"rendered":"

\nWhile teaching SQL Server 2005 to developers, I'd always point out the irony in the fact that the new EVENTDATA() function, used to return information in a DDL trigger or EVENT NOTIFICATION, returns XML and would be used almost exclusively by DBAs. This seems almost like a nefarious plot to force DBAs, who would be the most XML-phobic, to learn XML and XQuery\/XPath in order to deal with these in their daily jobs. In fact, I once wrote a SQL statement using XQuery<\/a> to coerce a common variant of EVENTDATA() output into tabular format.\n<\/p>\n

\nAfter spending the last few months wallowing in query plans, deadlock graphs, and the like, I've actually become convinced that XML and XQuery in SQL Server is *primarily a DBA tool*. I knew that would catch your attention. Besides event and query notifications, blocked process events, query plans, deadlock graphs, and EVENTDATA(), Database Tuning Advisor and new bulkload format files also use XML format. XML query plans can be studied by using XQuery\/XPath and can then be modified (slightly) for use in "plan forcing" where the XML plan is used as a "query hint", USE PLAN. And there's probably some uses that I missed. SSIS files and Reporting Services RDLs are XML format. I'm becoming convinced that T-SQL error messages should be in XML format too.\n<\/p>\n

\nSo DBAs: if you haven't yet learned XQuery\/XPath and the SQL Server XML methods, its not too late. It's there just to make you more productive (well, maybe "just" is an overstatement, but you get the point).\n<\/p>\n

\nJust to whet your appitite, here's a little stored procedure that uses dynamic management views, the new CROSS APPLY operator and XQuery to look for physical operations in query plans and correlate it with the SQL query. Could be useful, perhaps sorted by execution count. Cheers.\n<\/p>\n

\nCREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
\nAS
\nSELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.*
\nFROM sys.dm_exec_query_stats AS qs
\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) p
\nWHERE query_plan.exist('
\ndeclare default element namespace "http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan<\/a>";
\n\/ShowPlanXML\/BatchSequence\/Batch\/Statements\/\/RelOp\/@PhysicalOp[. = sql:variable("@op")]
\n') = 1
\nGO\n<\/p>\n

\nEXECUTE LookForPhysicalOps 'Clustered Index Scan'
\nEXECUTE LookForPhysicalOps 'Hash Match'
\nEXECUTE LookForPhysicalOps 'Table Scan'<\/p>\n","protected":false},"excerpt":{"rendered":"

While teaching SQL Server 2005 to developers, I'd always point out the irony in the fact that the new EVENTDATA() function, used to return information in a DDL trigger or EVENT NOTIFICATION, returns XML and would be used almost exclusively by DBAs. This seems almost like a nefarious plot to force DBAs, who would be […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,37],"tags":[],"class_list":["post-874","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server-xml"],"yoast_head":"\nMove over developers! SQL Server XQuery is actually a DBA tool - 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\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Move over developers! SQL Server XQuery is actually a DBA tool - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"While teaching SQL Server 2005 to developers, I'd always point out the irony in the fact that the new EVENTDATA() function, used to return information in a DDL trigger or EVENT NOTIFICATION, returns XML and would be used almost exclusively by DBAs. This seems almost like a nefarious plot to force DBAs, who would be […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2006-03-03T08:38:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-07T17:00:23+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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/\",\"name\":\"Move over developers! SQL Server XQuery is actually a DBA tool - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2006-03-03T08:38:00+00:00\",\"dateModified\":\"2013-01-07T17:00:23+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Move over developers! SQL Server XQuery is actually a DBA tool\"}]},{\"@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":"Move over developers! SQL Server XQuery is actually a DBA tool - 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\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/","og_locale":"en_US","og_type":"article","og_title":"Move over developers! SQL Server XQuery is actually a DBA tool - Bob Beauchemin","og_description":"While teaching SQL Server 2005 to developers, I'd always point out the irony in the fact that the new EVENTDATA() function, used to return information in a DDL trigger or EVENT NOTIFICATION, returns XML and would be used almost exclusively by DBAs. This seems almost like a nefarious plot to force DBAs, who would be […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/","og_site_name":"Bob Beauchemin","article_published_time":"2006-03-03T08:38:00+00:00","article_modified_time":"2013-01-07T17:00:23+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/","name":"Move over developers! SQL Server XQuery is actually a DBA tool - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2006-03-03T08:38:00+00:00","dateModified":"2013-01-07T17:00:23+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/move-over-developers-sql-server-xquery-is-actually-a-dba-tool\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"Move over developers! SQL Server XQuery is actually a DBA tool"}]},{"@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\/874","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=874"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/874\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=874"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=874"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=874"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}