How many times have you taken help from SQL Server DMVs?

How many of them are useful for your day-to-day data platform management?

Well, they are closely attached to my day-to-day work. I'm excited to get my hands on upcoming client's project about their SQL Server platform review on the aspects of providing roadmap for the data-tier form a performance & scalability aspects with an additional flavour of high availability. In such scenarios I always keep my DBA-toolkit (scripts & references) to map the design aspects by taking all the loose-end requirement to high-end deliverables. Such projects demand complete involvement of profiling SQL Server, collect hardware information, review existing data structs and provide recommendations by showing a sample results on improvement.

In this series and in parallel to subjec line I would like to go through an important DMV group: Operating system: "sys.dm_os_", these are associated with the SQL Server Operating System (SQLOS) which is responsible for managing operating system resources specific to SQL Server services. You may be aware that Dynamic Management Views (DMV) are started since inception of SQL Server 2005 that gives the DBA/Developer a snapshot about the current state of the SQL Server machine. DMVs are designed to be used instead of system tables and various other functions provided in SQL Server 2000, the 2types of DMvs server-scoped & database-scoped are much useful to obtain information without having to setup a PROFILER or PERFMON on immediate basis.

So put them into practice lets say you need to check memory usage on SQLOS level, here you can use SYS.DM_OS_SYS_MEMORY that gets you memory statistics which returns a system level information on total & available physical memory, total & available page memory, system cach and kernel space:

select total_physical_memory_kb, available_physical_memory_kb,total_page_file_kb, available_page_file_kb,
system_cache_kb, kernel_paged_pool_kb, kernel_nonpaged_pool_kb from sys.dm_os_sys_memory 

Further to that information I also collect the state of memory on operating system level. Below columns describe if the amount of memory at operating system level is low or high. 

select system_high_memory_signal_state, system_low_memory_signal_state,
system_memory_state_desc as system_memory_state from sys.dm_os_sys_memory

In my previous project I had to interact with SQL CAT folks on a peculiar system performance issue on CPU & Memory prospects where client has a Windows Server 2008 R2 & SQL Server 2008 R2 (new & upgraded) suffered performance issues. Initially they have queried the hardward information using SYS.DM_OS_SYS_INFO dmv, see below:(apologies for not remembering that gentleman's name who supplied following TSQL to get information):

-- Obtain HW information from SQL Server 2008 R2
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)],
affinity_type_desc, time_source_desc,
process_user_time_ms, 
CAST (CAST(process_user_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT)) 
* 100 AS DECIMAL(9,2)) AS [% SQL User Time],
process_kernel_time_ms,
CAST (CAST(process_kernel_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT)) 
* 100 AS DECIMAL(9,2)) AS [% SQL Kernel Time],
sqlserver_start_time
FROM sys.dm_os_sys_info; 
Then I asked what's new within SQL Server 2008 R2 for this specific DMV which confirms the below query to get affinity type & process kernel time: 

-- new columns in SQL Server 2008 R2
SELECT affinity_type, affinity_type_desc,  
time_source, time_source_desc,
process_kernel_time_ms, process_user_time_ms 
FROM sys.dm_os_sys_info;
 

This gave me a thought to revisit my first-hand source of information on SQL Server, BOL gets you a brief overview of SQLOS related DMVs that has full set of information which I use always as first hand resource:

sys.dm_os_buffer_descriptors (Transact-SQL)

sys.dm_os_memory_pools (Transact-SQL)

sys.dm_os_child_instances (Transact-SQL)

sys.dm_os_nodes (Transact-SQL)

sys.dm_os_cluster_nodes (Transact-SQL)

sys.dm_os_performance_counters (Transact-SQL)

sys.dm_os_dispatcher_pools (Transact-SQL)

sys.dm_os_process_memory (Transact-SQL)

sys.dm_os_hosts (Transact-SQL)

sys.dm_os_schedulers (Transact-SQL)

sys.dm_os_latch_stats (Transact-SQL)

sys.dm_os_stacks (Transact-SQL)

sys.dm_os_loaded_modules (Transact-SQL)

sys.dm_os_sys_info (Transact-SQL)

sys.dm_os_memory_brokers (Transact-SQL)

sys.dm_os_sys_memory (Transact-SQL)

sys.dm_os_memory_cache_clock_hands (Transact-SQL)

sys.dm_os_tasks (Transact-SQL)

sys.dm_os_memory_cache_counters (Transact-SQL)

sys.dm_os_threads (Transact-SQL)

sys.dm_os_memory_cache_entries (Transact-SQL)

sys.dm_os_virtual_address_dump (Transact-SQL)

sys.dm_os_memory_cache_hash_tables (Transact-SQL)

sys.dm_os_wait_stats (Transact-SQL)

sys.dm_os_memory_clerks (Transact-SQL)

sys.dm_os_waiting_tasks (Transact-SQL)

sys.dm_os_memory_nodes (Transact-SQL)

sys.dm_os_workers (Transact-SQL)

sys.dm_os_memory_objects (Transact-SQL)

Also a word of caution on other SQLOS DMVs  that are Identified for informational purposes only which means they are not supported/future compatibility is not guaranteed:

sys.dm_os_function_symbolic_name

sys.dm_os_ring_buffers

sys.dm_os_memory_allocations

sys.dm_os_sublatches

sys.dm_os_worker_local_storage

Also a further search of blogs along with BOL confirms that R2 introduces the new information to indicate what timer is currently in use and also the time_source_desc column that describes the use of Windows API to retreive further information. Here is a snippet of SQL Server 2008 R2 – DMV Changes and a reference that if you happen to manage the huge farm of SQL Server with very high-spec configuration such as running SQL Server 2008 R2 on Windows 2008 R2 with more than 64 CPUs which gives you another best practices recommendation from BOL - Running SQL Server on Systems that have mroe than 64 CPUs

Just a note that if you need to query a server scoped DMV then that database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV, the user must have SELECT privilege on VIEW DATABASE STATE.
  • GRANT VIEW SERVER STATE to <Login>
  • GRANT VIEW DATABASE STATE to <User>

If you want to deny a user permission to query certain DMVs, you can use the DENY command to restrict access to a specific DMV. Because security is also another important aspect that DBA should take care and not to give privlege to get server scope information for every SQL Server user, be strict than sorry.