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 […]

SQL Server Diagnostic Information Queries Detailed, Day 31

For Day 31 of this series, we start out with Query #69, which is High Aggregate Duration Queries. This query retrieves information from the sys.query_store_query_text query store catalog view, the sys.query_store_query query store catalog view, the sys.query_store_plan query store catalog view, the sys.query_store_runtime_stats query store catalog view, and the sys.query_store_runtime_stats_interval query store catalog view about […]

SQL Server Diagnostic Information Queries Detailed, Day 30

For Day 30 of this series, we start out with Query #67, which is UDF Statistics. This query retrieves information from the sys.dm_exec_function_stats dynamic management view about aggregate runtime metrics for user-defined functions in the current database, ordered by object name. Query #67 is shown in Figure 1. 1: — Look at UDF execution statistics […]

SQL Server Diagnostic Information Queries Detailed, Day 29

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. […]

SQL Server Diagnostic Information Queries Detailed, Day 28

For Day 28 of this series, we start out with Query #63, which is Overall Index Usage – Reads. This query retrieves information from the sys.indexes object catalog view, and the sys.dm_db_index_usage_stats dynamic management view about the overall index usage in the current database, ordered by reads. Query #63 is shown in Figure 1. 1: […]

SQL Server Diagnostic Information Queries Detailed, Day 27

For Day 27 of this series, we start out with Query #61, which is Volatile Indexes. This query retrieves information from the sys.objects object catalog view, the sys.stats object catalog view, and the sys.dm_db_stats_properties dynamic management function about the most volatile indexes and statistics in the current database. Query #61 is shown in Figure 1. […]

SQL Server Diagnostic Information Queries Detailed, Day 26

For Day 26 of this series, we start out with Query #59, which is Table Properties. This query retrieves information from the sys.tables object catalog view, and the sys.partitions object catalog view about various table properties in the current database. Query #59 is shown in Figure 1. 1: — Get some key table properties (Query […]

SQL Server Diagnostic Information Queries Detailed, Day 25

For Day 25 of this series, we start out with Query #57, which is Buffer Usage. This query retrieves information from the sys.allocation_units object catalog view, the sys.dm_os_buffer_descriptors dynamic management view and the sys.partitions object catalog view about buffer pool usage in the current database. Query #57 is shown in Figure 1. 1: — Breaks […]

SQL Server Diagnostic Information Queries Detailed, Day 24

For Day 24 of this series, we start out with Query #54, which is Bad NC Indexes. This query retrieves information from the sys.dm_db_index_usage_stats dynamic management view and the sys.indexes object catalog view about non-clustered indexes that have more writes than reads in the current database. Query #54 is shown in Figure 1. 1: — […]

SQL Server Diagnostic Information Queries Detailed, Day 23

For Day 23 of this series, we start out with Query #52, which is SP Logical Writes. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the cached stored procedures that have the highest cumulative total logical writes in the current database. Query #52 is shown in […]

SQL Server Diagnostic Information Queries Detailed, Day 22

For Day 22 of this series, we start out with Query #50, which is SP Logical Reads. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the cached stored procedures that have the highest cumulative total logical reads in the current database. Query #50 is shown in […]

SQL Server Diagnostic Information Queries Detailed, Day 21

For Day 21 of this series, we start out with Query #48, which is SP Avg Elapsed Time. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the cached stored procedures that have the highest average elapsed time in the current database. Query #48 is shown in […]

SQL Server Diagnostic Information Queries Detailed, Day 20

For Day 20 of this series, we start out with Query #46, which is Query Execution Counts. This query retrieves information from the sys.dm_exec_query_stats dynamic management view, the sys.dm_exec_sql_text dynamic management function, and the sys.dm_exec_query_plan dynamic management function about the most frequently executed cached queries in the current database. Query #46 is shown in Figure […]

SQL Server Diagnostic Information Queries Detailed, Day 19

After eighteen days of queries, we have made it through all of the instance-level queries in this set. Now, we move on to the database-specific queries in the set. For these queries, you need to be connected to a particular database that you are concerned with, rather than the master system database. For Day 19 […]

CPU-Z Benchmark Survey

The latest version of the free CPU-Z utility has a quick CPU benchmark test that just takes a couple of minutes to run. As part of a personal project that I am working on (which I think will be very interesting and beneficial to the SQL Server community), I am trying to collect as many […]

SQL Server Diagnostic Information Queries Detailed, Day 18

For Day 18 of this series, we start out with Query #41, which is Memory Clerk Usage. This query retrieves information from the sys.dm_os_memory_clerks dynamic management view about total memory usage by your active memory clerks. Query #41 is shown in Figure 1. 1: — Memory Clerk Usage for instance (Query 41) (Memory Clerk Usage) […]

SQL Server Diagnostic Information Queries Detailed, Day 17

For Day 17 of this series, we start out with Query #39, which is PLE by NUMA Node. This query retrieves information from the sys.dm_os_performance_counters dynamic management view about your page life expectancy (PLE) by NUMA node. Query #39 is shown in Figure 1. 1: — Page Life Expectancy (PLE) value for each NUMA node […]

SQL Server Diagnostic Information Queries Detailed, Day 16

For Day 16 of this series, we start out with Query #37, which is CPU Utilization History. This query retrieves information from the somewhat undocumented sys.dm_os_ring_buffers dynamic management view about recent CPU utilization by SQL Server. Query #37 is shown in Figure 1. 1: — Get CPU Utilization History for last 256 minutes (in one […]

SQL Server Diagnostic Information Queries Detailed, Day 15

For Day 15 of this series, we start out with Query #35, which is Avg Task Counts. This query retrieves information from the sys.dm_os_schedulers dynamic management view about the current average load across your SQL OS schedulers. Query #35 is shown in Figure 1. 1: — Get Average Task Counts (run multiple times) (Query 35) […]

SQL Server Diagnostic Information Queries Detailed, Day 14

For Day 14 of this series, we start out with Query #33, which is Top Waits. This query retrieves information from the sys.dm_os_wait_stats dynamic management view about the cumulative wait statistics for the instance since the last time it was restarted (or the wait statistics were manually cleared). Query #33 is shown in Figure 1. […]

SQL Server Diagnostic Information Queries Detailed, Day 13

For Day 13 of this series, we start out with Query #30, which is CPU Usage by Database. This query retrieves information from the sys.dm_exec_query_stats dynamic management view and from the sys.dm_exec_plan_attributes dynamic management function about total CPU usage by database for cached query plans. Query #30 is shown in Figure 1. 1: — Get […]

SQL Server Diagnostic Information Queries Detailed, Day 12

For Day 12 of this series, we start out with Query #27, which is Database Properties. This query retrieves information from the sys.databases catalog view, and from the sys.dm_os_performance_counters dynamic management view. Query #27 is shown in Figure 1. 1: — Recovery model, log reuse wait description, log file size, log usage size (Query 27) […]

SQL Server Diagnostic Information Queries Detailed, Day 11

Today, we will go through four queries that are related to storage. We start out with Query #23, which is Volume Info.  This query retrieves information from the sys.master_files catalog view and the sys.dm_os_volume_stats dynamic management function about any operating system volumes or mount points on your server where you have any SQL Server database […]

SQL Server Diagnostic Information Queries Detailed, Day 10

For Day 10 of this series, we start out with Query #21, which is Memory Dump Info.  This query retrieves information about any SQL Server memory dumps that have occurred from the sys.dm_server_memory_dumps dynamic management view. Query #21 is shown in Figure 1. 1: — Get information on location, time and size of any memory […]

SQL Server Diagnostic Information Queries Detailed, Day 9

For Day 9 of this series, we start out with Query #19, which is BPE Configuration.  This query retrieves information about your buffer pool extension (BPE) configuration from the sys.dm_os_buffer_pool_extension_configuration dynamic management view. Query #19 is shown in Figure 1. 1: — See if buffer pool extensions (BPE) is enabled (Query 19) (BPE Configuration) 2: […]

SQL Server Diagnostic Information Queries Detailed, Day 8

For Day 8 of this series, we start out with Query #16, which is Hardware Info.  This query retrieves some hardware-related information from the sys.dm_os_sys_info dynamic management view. Query #16 is shown in Figure 1. 1: — Hardware information from SQL Server 2016 (Query 16) (Hardware Info) 2: SELECT cpu_count AS [Logical CPU Count], scheduler_count, […]

SQL Server Diagnostic Information Queries Detailed, Day 7

Note: The three queries I am discussing today are not particularly interesting or useful if you are not using either a traditional, failover cluster instance (FCI), or an AlwaysOn Availability Group. For Day 7 of this series, we start out with Query #13, which is SQL Server Error Log. This query retrieves information from the […]

SQL Server Diagnostic Information Queries Detailed, Day 6

For Day 6 of this series, we start out with Query #11, which is SQL Server NUMA Info. This query retrieves information from the sys.dm_os_nodes dynamic management view about the NUMA node structure that has been created by the SQLOS. By default, this will mimic the hardware processor locality (whether it is the actual hardware […]

SQL Server Diagnostic Information Queries Detailed, Day 5

For Day 5 of the series, we have Query #9, which is SQL Server Agent Alerts. This query looks at the sysalerts table on the msdb system database. Query #9 is shown in Figure 1. 1: — Get SQL Server Agent Alert Information (Query 9) (SQL Server Agent Alerts) 2: SELECT name, event_source, message_id, severity, […]

SQL Server Diagnostic Information Queries Detailed, Day 4

Continuing the series, Query #7 is SQL Server Services Info. It retrieves this information from the sys.dm_server_services DMV. Query 7 is shown in Figure 1. 1: — SQL Server Services information (Query 7) (SQL Server Services Info) 2: SELECT servicename, process_id, startup_type_desc, status_desc, 3: last_startup_time, service_account, is_clustered, cluster_nodename, [filename] 4: FROM sys.dm_server_services WITH (NOLOCK) OPTION […]

SQL Server Diagnostic Information Queries Detailed, Day 3

For Day 3 of this series, we are up to Query #5, which is Global Trace Flags. This query simply runs a DBCC command to determine what global trace flags are currently in effect for your current instance of SQL Server. Query #5 is shown in Figure 1. 1: — Returns a list of all […]

SQL Server Diagnostic Information Queries Detailed, Day 2

Query #3 is Server Properties. This query simply makes multiple calls to the T-SQL SERVERPROPERTY metadata function to retrieve quite a bit of useful information about the current instance of SQL Server that you are connected to. Query #3 is shown in Figure 1. 1: — Get selected server properties (Query 3) (Server Properties) 2: […]

SQL Server Diagnostic Information Queries Detailed, Day 1

Since I have not learned my lesson from doing a daily blog post for a month from the previous several times that I have done it in the past, I am going to start a new blog series for the month of January 2016. This one will discuss each the queries in the January 2016 […]