SQL Server Diagnostic Information Queries Detailed–Recap

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 some useful information about how to interpret the results of each query.

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:

SQL Server Diagnostic Information Queries Detailed, Day 1

Version Info, Core Counts

SQL Server Diagnostic Information Queries Detailed, Day 2

Server Properties, Configuration Values

SQL Server Diagnostic Information Queries Detailed, Day 3

Global Trace Flags, Process Memory

SQL Server Diagnostic Information Queries Detailed, Day 4

SQL Server Services Info, SQL Server Agent Jobs

SQL Server Diagnostic Information Queries Detailed, Day 5

SQL Server Agent Alerts, Windows Info

SQL Server Diagnostic Information Queries Detailed, Day 6

SQL Server NUMA Info, System Memory

SQL Server Diagnostic Information Queries Detailed, Day 7

SQL Server Error Log, Cluster Node Properties, AlwaysOn AG Cluster

SQL Server Diagnostic Information Queries Detailed, Day 8

Hardware Info, System Manufacturer, Processor Description

SQL Server Diagnostic Information Queries Detailed, Day 9

BPW Configuration, BPE Usage

SQL Server Diagnostic Information Queries Detailed, Day 10

Memory Dump Info, Database Filenames and Paths

SQL Server Diagnostic Information Queries Detailed, Day 11

Volume Info, Drive-Level Latency, IO Stalls by File, IO Warnings

SQL Server Diagnostic Information Queries Detailed, Day 12

Database Properties, Missing Indexes All Databases, VLF Counts

SQL Server Diagnostic Information Queries Detailed, Day 13

CPU Usage by Database, IO Usage by Database, Total Buffer Usage by Database

SQL Server Diagnostic Information Queries Detailed, Day 14

Top Waits, Connection Counts by IP Address

SQL Server Diagnostic Information Queries Detailed, Day 15

Avg Task Counts, Detect Blocking

SQL Server Diagnostic Information Queries Detailed, Day 16

CPU Utilization History, Top Worker Time Queries

SQL Server Diagnostic Information Queries Detailed, Day 17

PLE by NUMA Node, Memory Grants Pending,

SQL Server Diagnostic Information Queries Detailed, Day 18

Memory Clerk Usage, Ad hoc Queries, Top Logical Reads Queries

SQL Server Diagnostic Information Queries Detailed, Day 19

File Sizes and Space, IO Stats by File

SQL Server Diagnostic Information Queries Detailed, Day 20

Query Execution Counts, SP Execution Counts

SQL Server Diagnostic Information Queries Detailed, Day 21

SP Avg Elapsed Time, SP Worker Time

SQL Server Diagnostic Information Queries Detailed, Day 22

SP Logical Reads, SP Physical Reads

SQL Server Diagnostic Information Queries Detailed, Day 23

SP Logical Writes, Top IO Statements

SQL Server Diagnostic Information Queries Detailed, Day 24

Bad NC Indexes, Missing Indexes, Missing Index Warnings

SQL Server Diagnostic Information Queries Detailed, Day 25

Buffer Usage, Table Sizes

SQL Server Diagnostic Information Queries Detailed, Day 26

Table Properties, Statistics Update

SQL Server Diagnostic Information Queries Detailed, Day 27

Volatile Indexes, Index Fragmentation

SQL Server Diagnostic Information Queries Detailed, Day 28

Overall Index Usage – Reads, Overall Index Usage – Writes

SQL Server Diagnostic Information Queries Detailed, Day 29

XTP Index Usage, Lock Waits

SQL Server Diagnostic Information Queries Detailed, Day 30

UDF Statistics, QueryStore Options

SQL Server Diagnostic Information Queries Detailed, Day 31

High Aggregate Duration Queries, Recent Full Backups

 

These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results.

SQL Server 2014 DMV Diagnostic Queries – Part 1

SQL Server 2014 DMV Diagnostic Queries – Part 2

SQL Server 2014 DMV Diagnostic Queries – Part 3