<\/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":"\n