#0236 – SQL Server – SQL OS DMVs – dm_os* | SQLTwins by Nakul Vachhrajani

SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. DMVs expose vital diagnostic information which are useful to indicate the overall health of the SQL Server. The list of DMVs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

The SQL Server Operating System (SQLOS) is a layer that lies between the host operating system (Windows OS) and the SQL Server. All interactions of the SQL Server with the underlying operating system are carried out through the SQLOS. SQLOS manages all the operating system services such as memory management, processor and disk interaction, buffer management, hosting external components (e.g. SQL-CLR) etc. Today, we will look at the available SQLOS DMVs and study in detail, the ones introduced in SQL Server 2008 R2/2012.

A full list of documented SQL Server 2012 SQLOS related DMVs is available at: http://msdn.microsoft.com/en-us/library/ms176083.aspx. The DMVs introduced in SQL Server 2008 R2/2012 are:

sys.dm_os_cluster_properties
sys.dm_os_memory_broker_clerks
sys.dm_os_server_diagnostics_log_configurations
sys.dm_os_windows_info

sys.dm_os_cluster_properties

Introduced In: SQL Server 2012

Purpose: Returns one record with the current SQL Server resource properties (failover response & logging, detection time, etc). When executed on a stand-alone instance (as my test environment is), no records are returned.

Required Permissions: VIEW SERVER STATE

BOL Link: http://msdn.microsoft.com/en-us/library/gg471591.aspx

sys.dm_os_memory_broker_clerks

Introduced In: SQL Server 2012

Purpose: Memory brokers are internal memory management objects that help the SQL Server regulate the memory allocations across all SQL Server components. Please note that the memory allocations are done by the SQLOS – the memory brokers only document and track memory allocations. Generically speaking, the components that consume memory are called memory clerks. This DMV therefore provides details about the memory allocations of SQL Server’s internal memory consumers.

Required Permissions: VIEW SERVER STATE

BOL Link: N/A – undocumented.

SELECT mbc.clerk_name,
       mbc.total_kb,
       mbc.simulated_kb,
       mbc.simulation_benefit,
       mbc.internal_benefit,
       mbc.external_benefit,
       mbc.value_of_memory,
       mbc.periodic_freed_kb,
       mbc.internal_freed_kb
FROM sys.dm_os_memory_broker_clerks AS mbc;

sys.dm_os_server_diagnostics_log_configurations

Introduced In: SQL Server 2012

Purpose: Returns one record for the SQL Server failover cluster diagnostic log. By default, the log is available in the SQL Server default log folder and the DMV also indicates whether diagnostic logging is enabled, number of logs and default size.

Even on a standalone server, one record is returned.

Required permissions: VIEW SERVER STATE

BOL Link: http://msdn.microsoft.com/en-us/library/gg471697.aspx

SELECT dlc.is_enabled,
       dlc.path,
       dlc.max_size,
       dlc.max_files
FROM sys.dm_os_server_diagnostics_log_configurations AS dlc;

sys.dm_os_windows_info

Introduced In: SQL Server 2008 R2

Purpose: Returns the service pack/release information of the underlying operating system.

Required Permissions: VIEW SERVER STATE

BOL Link: http://technet.microsoft.com/en-us/library/hh204565.aspx

SELECT wi.windows_release,
       wi.windows_service_pack_level,
       wi.windows_sku,
       wi.os_language_version
FROM sys.dm_os_windows_info AS wi;

Here’s the output from my Windows 8 test system: