{"id":1057,"date":"2016-01-29T08:27:42","date_gmt":"2016-01-29T16:27:42","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1057"},"modified":"2017-04-13T12:28:54","modified_gmt":"2017-04-13T19:28:54","slug":"sql-server-diagnostic-information-queries-detailed-day-29","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 29"},"content":{"rendered":"

For Day 29 of this series, we start out with Query #65<\/strong>, which is XTP Index Usage. This query retrieves information from the sys.dm_db_xtp_index_stats<\/a> dynamic management view and the sys.indexes<\/a> object catalog view about the overall in-memory OLTP index usage in the current database, ordered by object name. Query #65 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get in-memory OLTP index usage (Query 65) (XTP Index Usage)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> OBJECT_NAME(i.[object_id]) AS<\/span> [Object<\/span> Name], i.index_id, i.name, i.type_desc,<\/pre>\n
<\/p>\n
   3:<\/span>        xis.scans_started, xis.scans_retries, xis.rows_touched, xis.rows_returned <\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.dm_db_xtp_index_stats AS<\/span> xis WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   5:<\/span> INNER<\/span> JOIN<\/span> sys.indexes AS<\/span> i WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   6:<\/span> ON<\/span> i.[object_id] = xis.[object_id] <\/pre>\n
<\/p>\n
   7:<\/span> AND<\/span> i.index_id = xis.index_id <\/pre>\n
<\/p>\n
   8:<\/span> ORDER<\/span> BY<\/span> OBJECT_NAME(i.[object_id]) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   9:<\/span>  <\/pre>\n
<\/p>\n
  10:<\/span> -- This gives you some index usage statistics for in-memory OLTP<\/span><\/pre>\n
<\/p>\n
  11:<\/span> -- Returns<\/span> no<\/span> data<\/span> if<\/span> you are<\/span> not<\/span> using<\/span> in<\/span>-memory OLTP<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #65 XTP Index Usage<\/strong><\/p>\n
If you are using in-memory OLTP<\/a> (aka Hekaton), then this query will show how your in-memory OLTP indexes are being used. Perhaps because this is an Enterprise-only feature and perhaps because it has some limitations in SQL Server 2014, I have not seen this feature being used that much out in the field yet. I think the adoption rate will improve with SQL Server 2016.<\/p>\n
 <\/p>\n
Query #66<\/strong> is Lock Waits. This query retrieves information from the sys.dm_db_index_operational_stats<\/a> dynamic management function, the sys.objects<\/a> object catalog view, and the sys.indexes<\/a> object catalog view about the cumulative lock waits in the current database. Query #66 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get lock waits for current database (Query 66) (Lock Waits)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> o.name AS<\/span> [table_name], i.name AS<\/span> [index_name], ios.index_id, ios.partition_number,<\/pre>\n
<\/p>\n
   3:<\/span>         SUM<\/span>(ios.row_lock_wait_count) AS<\/span> [total_row_lock_waits], <\/pre>\n
<\/p>\n
   4:<\/span>         SUM<\/span>(ios.row_lock_wait_in_ms) AS<\/span> [total_row_lock_wait_in_ms],<\/pre>\n
<\/p>\n
   5:<\/span>         SUM<\/span>(ios.page_lock_wait_count) AS<\/span> [total_page_lock_waits],<\/pre>\n
<\/p>\n
   6:<\/span>         SUM<\/span>(ios.page_lock_wait_in_ms) AS<\/span> [total_page_lock_wait_in_ms],<\/pre>\n
<\/p>\n
   7:<\/span>         SUM<\/span>(ios.page_lock_wait_in_ms)+ SUM<\/span>(row_lock_wait_in_ms) AS<\/span> [total_lock_wait_in_ms]<\/pre>\n
<\/p>\n
   8:<\/span> FROM<\/span> sys.dm_db_index_operational_stats(DB_ID(), NULL<\/span>, NULL<\/span>, NULL<\/span>) AS<\/span> ios<\/pre>\n
<\/p>\n
   9:<\/span> INNER<\/span> JOIN<\/span> sys.objects AS<\/span> o WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  10:<\/span> ON<\/span> ios.[object_id] = o.[object_id]<\/pre>\n
<\/p>\n
  11:<\/span> INNER<\/span> JOIN<\/span> sys.indexes AS<\/span> i WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  12:<\/span> ON<\/span> ios.[object_id] = i.[object_id] <\/pre>\n
<\/p>\n
  13:<\/span> AND<\/span> ios.index_id = i.index_id<\/pre>\n
<\/p>\n
  14:<\/span> WHERE<\/span> o.[object_id] > 100<\/pre>\n
<\/p>\n
  15:<\/span> GROUP<\/span> BY<\/span> o.name, i.name, ios.index_id, ios.partition_number<\/pre>\n
<\/p>\n
  16:<\/span> HAVING<\/span> SUM<\/span>(ios.page_lock_wait_in_ms)+ SUM<\/span>(row_lock_wait_in_ms) > 0<\/pre>\n
<\/p>\n
  17:<\/span> ORDER<\/span> BY<\/span> total_lock_wait_in_ms DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  18:<\/span>  <\/pre>\n
<\/p>\n
  19:<\/span> -- This query is<\/span> helpful for<\/span> troubleshooting blocking and<\/span> deadlocking issues<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #66 Lock Waits<\/strong><\/p>\n
If you are seeing symptoms of locking\/blocking\/deadlocks (such as high average task counts or actual deadlock errors), then this query can show which tables and indexes are seeing the most lock waits, which can often help you troubleshoot and resolve your blocking issues.<\/p>\n
These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results. <\/p>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 1<\/a><\/p>\n<\/blockquote>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 2<\/a><\/p>\n<\/blockquote>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 3<\/a><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
For Day 29 of this series, we start out with Query #65, which is XTP Index Usage. This query retrieves information from the sys.dm_db_xtp_index_stats dynamic management view and the sys.indexes object catalog view about the overall in-memory OLTP index usage in the current database, ordered by object name. Query #65 is shown in Figure 1. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[272],"class_list":["post-1057","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-dmv-diagnostic-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 29 - Glenn Berry<\/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\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 29 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 29 of this series, we start out with Query #65, which is XTP Index Usage. This query retrieves information from the sys.dm_db_xtp_index_stats dynamic management view and the sys.indexes object catalog view about the overall in-memory OLTP index usage in the current database, ordered by object name. Query #65 is shown in Figure 1. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-29T16:27:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:54+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 29 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-29T16:27:42+00:00\",\"dateModified\":\"2017-04-13T19:28:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 29\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 29 - Glenn Berry","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\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 29 - Glenn Berry","og_description":"For Day 29 of this series, we start out with Query #65, which is XTP Index Usage. This query retrieves information from the sys.dm_db_xtp_index_stats dynamic management view and the sys.indexes object catalog view about the overall in-memory OLTP index usage in the current database, ordered by object name. Query #65 is shown in Figure 1. […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-29T16:27:42+00:00","article_modified_time":"2017-04-13T19:28:54+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 29 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-29T16:27:42+00:00","dateModified":"2017-04-13T19:28:54+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-29\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 29"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1057","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1057"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1057\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1057"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1057"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1057"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}