SQL Server Diagnostic Information Queries


 * https://dl.dropboxusercontent.com/u/13748067/SQL%20Server%202016%20Diagnostic%20Information%20Queries%20%28May%202016%29.sql

-- SQL Server 2016 Diagnostic Information Queries -- Glenn Berry -- May 2016 -- Last Modified: May 12, 2016 -- http://sqlserverperformance.wordpress.com/ -- http://sqlskills.com/blogs/glenn/ -- Twitter: GlennAlanBerry

-- Please listen to my Pluralsight courses -- http://www.pluralsight.com/author/glenn-berry

-- Please make sure you are using the correct version of these diagnostic queries for your version of SQL Server

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

SQL Server 2014 DMV Diagnostic Queries - Part 1
 * http://www.pluralsight.com/courses/sql-server-2014-dmv-diagnostic-queries-part1

SQL Server 2014 DMV Diagnostic Queries - Part 2
 * http://www.pluralsight.com/courses/sql-server-2014-dmv-diagnostic-queries-part2

SQL Server 2014 DMV Diagnostic Queries - Part 3
 * http://www.pluralsight.com/courses/sql-server-2014-dmv-diagnostic-queries-part3

Freetext query list
SQL and OS Version information for current instance (Query 1) (Version Info) Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts) Get selected server properties (Query 3) (Server Properties) Get instance-level configuration values for instance (Query 4) (Configuration Values) Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags) Returns status of instant file initialization (Query 6) (IFI Status) SQL Server Process Address space info (Query 7) (Process Memory) SQL Server Services information (Query 7) (SQL Server Services Info) Get SQL Server Agent jobs and Category information (Query 8) (SQL Server Agent Jobs) Get SQL Server Agent Alert Information (Query 9) (SQL Server Agent Alerts) Windows information (Query 10) (Windows Info) SQL Server NUMA Node information (Query 11) (SQL Server NUMA Info) Good basic information about OS memory amounts and state (Query 12) (System Memory) Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured (Query 13) (SQL Server Error Log) Get information about your cluster nodes and their status (Query 14) (Cluster Node Properties) Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster) Hardware information from SQL Server 2016 (Query 16) (Hardware Info) Get System Manufacturer and model number from SQL Server Error log (Query 17) (System Manufacturer) Get processor description from Windows Registry (Query 18) (Processor Description) See if buffer pool extensions (BPE) is enabled (Query 19) (BPE Configuration) Look at buffer descriptors to see BPE usage by database (Query 20) (BPE Usage) Get information on location, time and size of any memory dumps from SQL Server (Query 21) (Memory Dump Info) File names and paths for all user and system databases on instance (Query 22) (Database Filenames and Paths) Volume info for all LUNS that have database files on the current instance (Query 23) (Volume Info) Drive level latency information (Query 24) (Drive Level Latency) Calculates average stalls per read, per write, and per total input/output for each database file (Query 25) (IO Stalls by File) Look for I/O requests taking longer than 15 seconds in the five most recent SQL Server Error Logs (Query 26) (IO Warnings) Recovery model, log reuse wait description, log file size, log usage size (Query 27) (Database Properties) Missing Indexes for all databases by Index Advantage (Query 28) (Missing Indexes All Databases) Get VLF Counts for all databases on the instance (Query 29) (VLF Counts) Get CPU utilization by database (Query 30) (CPU Usage by Database) Get I/O utilization by database (Query 31) (IO Usage By Database) Get total buffer usage by database for current instance (Query 32) (Total Buffer Usage by Database) Isolate top waits for server instance since last restart or wait statistics clear (Query 33) (Top Waits) Get a count of SQL connections by IP address (Query 34) (Connection Counts by IP Address) Get Average Task Counts (run multiple times) (Query 35) (Avg Task Counts) Detect blocking (run multiple times) (Query 36) (Detect Blocking) Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 37) (CPU Utilization History) Get top total worker time queries for entire instance (Query 38) (Top Worker Time Queries) Page Life Expectancy (PLE) value for each NUMA node in current instance (Query 39) (PLE by NUMA Node) Memory Grants Pending value for current instance (Query 40) (Memory Grants Pending) Memory Clerk Usage for instance (Query 41) (Memory Clerk Usage) Find single-use, ad-hoc and prepared queries that are bloating the plan cache (Query 42) (Ad hoc Queries) Get top total logical reads queries for entire instance (Query 43) (Top Logical Reads Queries) Individual File Sizes and space available for current database (Query 44) (File Sizes and Space) Log space usage for current database (Query 45) (Log Space Usage) Get database scoped configuration values for current database (Query 46) (Database-scoped Configurations) I/O Statistics by file for the current database (Query 47) (IO Stats By File) Get most frequently executed queries for this database (Query 48) (Query Execution Counts) Top Cached SPs By Execution Count (Query 49) (SP Execution Counts) Top Cached SPs By Avg Elapsed Time (Query 50) (SP Avg Elapsed Time) Top Cached SPs By Total Worker time. Worker time relates to CPU cost (Query 51) (SP Worker Time) Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure (Query 52) (SP Logical Reads) Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure (Query 53) (SP Physical Reads) Top Cached SPs By Total Logical Writes (Query 54) (SP Logical Writes) Lists the top statements by average input/output usage for the current database (Query 55) (Top IO Statements) Possible Bad NC Indexes (writes > reads) (Query 56) (Bad NC Indexes) Missing Indexes for current database by Index Advantage (Query 57) (Missing Indexes) Find missing index warnings for cached plans in the current database (Query 58) (Missing Index Warnings) Breaks down buffers used by current database by object (table, index) in the buffer cache (Query 59) (Buffer Usage) Get Table names, row counts, and compression status for clustered index or heap (Query 60) (Table Sizes) Get some key table properties (Query 61) (Table Properties) When were Statistics last updated on all indexes? (Query 62) (Statistics Update) Look at most frequently modified indexes and statistics (Query 63) (Volatile Indexes) Get fragmentation info for all indexes above a certain size in the current database (Query 64) (Index Fragmentation) Index Read/Write stats (all tables in current DB) ordered by Reads (Query 65) (Overall Index Usage - Reads) Index Read/Write stats (all tables in current DB) ordered by Writes (Query 66) (Overall Index Usage - Writes) Get in-memory OLTP index usage (Query 67) (XTP Index Usage) Get lock waits for current database (Query 68) (Lock Waits) Look at UDF execution statistics (Query 69) (UDF Statistics) Get QueryStore Options for this database (Query 70) (QueryStore Options) Get highest aggregate duration queries over last hour (Query 71) (High Aggregate Duration Queries) Look at recent Full backups for the current database (Query 72) (Recent Full Backups)