{"id":1060,"date":"2016-01-31T13:43:38","date_gmt":"2016-01-31T21:43:38","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1060"},"modified":"2017-04-13T12:28:42","modified_gmt":"2017-04-13T19:28:42","slug":"sql-server-diagnostic-information-queries-detailedrecap","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed–Recap"},"content":{"rendered":"

Over the course of the month of January 2016, I wrote a series of daily blog posts that went through each of the 70 queries in the January 2016 version<\/a> of my SQL Server 2016 Diagnostic Information Queries, with documentation of the various views and functions behind each query, along with what I hope is some useful information about how to interpret the results of each query.<\/p>\n

Below are links to each of the daily blog posts for this series, along with a list of the queries that were covered that day:<\/p>\n

SQL Server Diagnostic Information Queries Detailed, Day 1<\/a><\/p>\n

\n

Version Info, Core Counts<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 2<\/a><\/p>\n

\n

Server Properties, Configuration Values<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 3<\/a><\/p>\n

\n

Global Trace Flags, Process Memory<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 4<\/a><\/p>\n

\n

SQL Server Services Info, SQL Server Agent Jobs<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 5<\/a><\/p>\n

\n

SQL Server Agent Alerts, Windows Info<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 6<\/a><\/p>\n

\n

SQL Server NUMA Info, System Memory<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 7<\/a><\/p>\n

\n

SQL Server Error Log, <\/font>Cluster Node Properties, <\/font>AlwaysOn AG Cluster<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 8<\/a><\/p>\n

\n

Hardware Info, System Manufacturer, Processor Description<\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 9<\/a><\/p>\n

\n

BPW Configuration, BPE Usage<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 10<\/a><\/p>\n

\n

Memory Dump Info, Database Filenames and Paths<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 11<\/a><\/p>\n

\n

Volume Info, Drive-Level Latency, IO Stalls by File, IO Warnings<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 12<\/a><\/p>\n

\n

Database Properties, Missing Indexes All Databases, VLF Counts<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 13<\/a><\/p>\n

\n

CPU Usage by Database, IO Usage by Database, Total Buffer Usage by Database<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 14<\/a><\/p>\n

\n

Top Waits, Connection Counts by IP Address<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 15<\/a><\/p>\n

\n

Avg Task Counts, Detect Blocking<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 16<\/a><\/p>\n

\n

CPU Utilization History, Top Worker Time Queries<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 17<\/a><\/p>\n

\n

PLE by NUMA Node, Memory Grants Pending,<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 18<\/a><\/p>\n

\n

Memory Clerk Usage, Ad hoc Queries, Top Logical Reads Queries<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 19<\/a><\/p>\n

\n

File Sizes and Space, IO Stats by File<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 20<\/a><\/p>\n

\n

Query Execution Counts, SP Execution Counts<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 21<\/a><\/p>\n

\n

SP Avg Elapsed Time, SP Worker Time<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 22<\/a><\/p>\n

\n

SP Logical Reads, SP Physical Reads<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 23<\/a><\/p>\n

\n

SP Logical Writes, Top IO Statements<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 24<\/a><\/p>\n

\n

Bad NC Indexes, Missing Indexes, Missing Index Warnings<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 25<\/a><\/p>\n

\n

Buffer Usage, Table Sizes<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 26<\/a><\/p>\n

\n

Table Properties, Statistics Update<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 27<\/a><\/p>\n

\n

Volatile Indexes, Index Fragmentation<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 28<\/a><\/p>\n

\n

Overall Index Usage \u2013 Reads, Overall Index Usage \u2013 Writes<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 29<\/a><\/p>\n

\n

XTP Index Usage, Lock Waits<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 30<\/a><\/p>\n

\n

UDF Statistics, QueryStore Options<\/font><\/p>\n<\/blockquote>\n

SQL Server Diagnostic Information Queries Detailed, Day 31<\/a><\/p>\n

\n

High Aggregate Duration Queries, Recent Full Backups<\/font><\/p>\n<\/blockquote>\n

  <\/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><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Over the course of the month of January 2016, I wrote a series of daily blog posts that went through each of the 70 queries in the January 2016 version of my SQL Server 2016 Diagnostic Information Queries, with documentation of the various views and functions behind each query, along with what I hope is […]<\/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-1060","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–Recap - 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-detailedrecap\/\" \/>\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–Recap - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Over the course of the month of January 2016, I wrote a series of daily blog posts that went through each of the 70 queries in the January 2016 version of my SQL Server 2016 Diagnostic Information Queries, with documentation of the various views and functions behind each query, along with what I hope is […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-31T21:43:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:42+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-detailedrecap\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed–Recap - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-31T21:43:38+00:00\",\"dateModified\":\"2017-04-13T19:28:42+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-detailedrecap\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/#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–Recap\"}]},{\"@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–Recap - 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-detailedrecap\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed–Recap - Glenn Berry","og_description":"Over the course of the month of January 2016, I wrote a series of daily blog posts that went through each of the 70 queries in the January 2016 version of my SQL Server 2016 Diagnostic Information Queries, with documentation of the various views and functions behind each query, along with what I hope is […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-31T21:43:38+00:00","article_modified_time":"2017-04-13T19:28:42+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-detailedrecap\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/","name":"SQL Server Diagnostic Information Queries Detailed–Recap - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-31T21:43:38+00:00","dateModified":"2017-04-13T19:28:42+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-detailedrecap\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailedrecap\/#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–Recap"}]},{"@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\/1060","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=1060"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1060\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1060"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1060"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1060"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}