Introduction
One thing I am known for in the SQL Server Community are my free SQL Server Diagnostic Information Queries. I first made these queries public back in 2009, and I make improvements to them every month. These queries are available here. Now that SQL Server 2022 has been announced, it seems like a good time to make a public plea for my SQL Server 2022 DMV Wish List.
You can upvote and comment on my Azure Feedback idea here: Diagnostic DMV Improvements in SQL Server 2022
SQL Server 2022 DMV Wish List
My SQL Server 2022 DMV Wish List is a general request to add some new DMVs and/or add some columns to some existing DMVs to fill some gaps in SQL Server 2019 and older versions. Microsoft could decide to address some or all of these items in SQL Server 2022. They might even decide to backport some of them to SQL Server 2019 and SQL Server 2017.
My wish list is broken down into five different areas:
- Reading from the Windows Registry
- Reading from the SQL Server Error Log
- New columns for sys.dm_os_sys_info
- Fixing or replacing sys.dm_os_ring_buffers
- New DMV to replace DBCC TRACESTATUS (-1)
Reading from the Windows Registry
Reading from the Windows Registry is obviously not going to work with a Linux host or with Azure SQL Database. In my Diagnostic Queries, I read from the Windows Registry to get the processor description and the main system BIOS date.
The processor description (the make and model number) is very relevant and useful information in any environment. This is true whether it is on premises or in the cloud, whether you are running on Windows or Linux. It would make sense to add this information as a new column to sys.dm_os_sys_info or somewhere else where it would just work in any environment.
The main system BIOS date is mainly useful for bare metal installations, although it can also give you some idea about what version hypervisor you are running on.
Reading from the SQL Server Error Log
Reading from the SQL Server Error Log can be problematic for two main reasons. First, if the error log is very large, it can take quite a bit of time to read the entire thing searching for a text string. Second, if the log has been recycled, a lot of the most valuable information won’t be present in the currently active error log.
I get the system manufacturer and model number from the SQL Server Error Log. This will either tell you the actual make and model of your server (with bare metal) or the type of hypervisor you are running in. I also get the socket and core counts that SQL Server recognizes, along with the core count that SQL Server is actually using.
This helps you catch problems where people have installed SQL Server Standard Edition on a machine (physical or virtual) that exceeds the SQL Server Standard Edition license limits. Both of these could probably be new columns in sys.dm_os_sys_info.
New Columns in sys.dm_os_sys_info
It would also be very useful to get the RAM type and speed in sys.dm_os_sys_info. This information is shown in Windows Task Manager, and it is also available in Linux.
Fixing or Replacing sys.dm_os_ring_buffers
The unsupported sys.dm_os_ring_buffers DMV is often used to get recent CPU utilization figures for the SQL Server process and for all other processes combined, in one minute increments. This is very useful information, but sometimes sys.dm_os_ring_buffers returns negative values or values over 100.
Unfortunately, this has been a problem for many years. It is most common on systems that have more than 64 cores. It would be great if Microsoft would either fix sys.dm_os_ring_buffers or create a new DMV that works more reliably.
New DMV to replace DBCC TRACESTATUS (-1)
Knowing what global trace flags are enabled is very useful for diagnostic purposes. Calling DBCC TRACESTATUS (-1) is sort of kludgy, and it would be nice to have a DMV that exposed this information.
Here are the problematic Diagnostic queries from my SQL Server 2019 Diagnostic Queries. These are what prompted this post.
-- Glenn Berry's DMV Wish List for SQL Server 2022
-- Reading from the Windows Registry ***********************************************************************
-- Adding these helps make Linux feature equivalent to Windows. It also is a better solution than reading from the Registry
-- Get BIOS date from Windows Registry (Query 19) (BIOS Date)
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';
-- Helps you understand whether the main system BIOS is up to date, and the possible age of the hardware
-- Not as useful for virtualization
-- Does not work on Linux
-- Get processor description from Windows Registry (Query 20) (Processor Description)
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
------
-- Gives you the model number and rated clock speed of your processor(s)
-- Your processors may be running at less than the rated clock speed due
-- to the Windows Power Plan or hardware power management
-- Does not work on Linux
-- Reading the SQL Server Error Log ************************************************************************
-- These take a long time with a very large SQL Server error log and they return no results if the
-- SQL Server Error log has been recycled
-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
-- This query might take a few seconds depending on the size of your error log
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
-- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not
-- It can also help you confirm your SQL Server licensing model
-- Be on the lookout for this message "using 40 logical processors based on SQL Server licensing"
-- (when you have more than 40 logical cores) which means grandfathered Server/CAL licensing
-- This query will return no results if your error log has been recycled since the instance was last started
-- Get System Manufacturer and model number from SQL Server Error log (Query 18) (System Manufacturer)
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
------
-- This can help you determine the capabilities and capacities of your database server
-- Can also be used to confirm if you are running in a VM
-- This query might take a few seconds if you have not recycled your error log recently
-- This query will return no results if your error log has been recycled since the instance was started
-- Request for new DMV column(s) **************************************************************************************
-- How about getting the processor description here?
-- How about getting RAM type (DDR4, DDR5, etc.) and RAM speed (DDR4-3200) here?
-- Windows Task Manager has this information
-- Hardware information from SQL Server 2019 (Query 17) (Hardware Info)
SELECT cpu_count AS [Logical CPU Count], scheduler_count,
(socket_count * cores_per_socket) AS [Physical Core Count],
socket_count AS [Socket Count], cores_per_socket, numa_node_count,
physical_memory_kb/1024 AS [Physical Memory (MB)],
max_workers_count AS [Max Workers Count],
affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time],
DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [SQL Server Up Time (hrs)],
virtual_machine_type_desc AS [Virtual Machine Type],
softnuma_configuration_desc AS [Soft NUMA Configuration],
sql_memory_model_desc,
container_type_desc -- New in SQL Server 2019
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
-- Unsupported/Buggy DMV *************************************************************************************************
-- This often does not work on high core count systems, returning negative numbers or numbers over 100%
-- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 43) (CPU Utilization History)
DECLARE @ts_now bigint = (SELECT ms_ticks FROM sys.dm_os_sys_info WITH (NOLOCK));
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
AS [SQLProcessUtilization], [timestamp]
FROM (SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%') AS x) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
------
-- Look at the trend over the entire period
-- Also look at high sustained 'Other Process' CPU Utilization values
-- Note: This query sometimes gives inaccurate results (negative values)
-- on high core count (> 64 cores) systems
-- Request for new DMV ***********************************************************************************************
-- Exposing this in a DMV would make it easier to work with programatically
-- Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags)
DBCC TRACESTATUS (-1);
How Can You Help?
The best way for you to help make this happen is to vote up my request here:
If a request like this gets enough upvotes and supporting comments, it greatly increases the chances that Microsoft will respond to it in a positive fashion.
Final Words
I think it is still early enough in the SQL Server 2022 development cycle that Microsoft could make some of these DMV improvements. You can help convince Microsoft to do this work by amplifing this request.
If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!
2 thoughts on “SQL Server 2022 DMV Wish List”