SQL Server Diagnostic Information Queries

From SQL Server Wiki
Jump to navigation Jump to search
-- 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

Query List[edit]

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



Learn more[edit]

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

SQL Server 2014 DMV Diagnostic Queries - Part 2

SQL Server 2014 DMV Diagnostic Queries - Part 3


Freetext query list[edit]

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)