DMV's

Sys.dm_os_wait_stats is the DMV that contains wait statistics, which are aggregated across all session ids since the last restart of SQL Server or since the last time that the wait statistics were reset manually using DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR).  Resetting wait statistics can be helpful before running a test or workload.

Anytime a session_id waits for a resource, the session_id is moved to the waiter list along with an associated wait type.  The DMV sys.dm_os_waiting_tasks shows the waiter list at a given moment in time.  Waits for all session_ids are aggregated in sys.dm_os_wait_stats. 

The stored procedures track_waitstats_2005 and get_waitstats_2005 can be used to measure the wait statistics for a given workload.

What are DMVs
Dynamic Management Views are views and functions introduced in sql server 2005 for monitoring and tuning sql server performance Two types of dynamic management views:

1. Server-scoped DMV: Stored in Master Database
2. Database-scoped DMV: Specific to each database

Permission to Execute DMV [Security]

To query a server scoped DMV, the 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.

All the DMVs exits in SYS schema and their names start with DM_. So when you need to query a DMV, you should prefix the view name with SYS. As an example, if you need to see the total physical memory of the SQL Server machine;

SELECT

(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb

FROM sys.dm_os_sys_info

how many DMV/DMF are there in SQL Server, to get that information (see Pinal's post)

SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name

or

SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm[_]%' ORDER BY name

Frequently used

1. SQL Server related [Hardware Resources] DMV
2. Database related DMV
3. Index related DMV
4. Execution related DMV

1. SQL Server Related DMV

This section details the DMVs associated with SQL Server system. SQL DMV is responsible to manage server level resources specific to a SQL Server instance.

This section covers DMVs related to OS, Disk and Memory.

a. sys.dm_os_sys_info

This view returns the information about the SQL Server machine, available resources and the resource consumption.

This view returns information like the following:

1. CPU Count: Number of logical CPUs in the server
2. Hyperthread-ratio: Ratio of logical and physical CPUs
3. Physical_memory_in_bytes: Amount of physical memory available
4. Virtual_memory_in_bytes: Amount of virtual memory available
5. Bpool_commited: Committed physical memory in buffer pool
6. OS_Priority_class: Priority class for SQL Server process
7. Max_workers_thread: Maximum number of workers which can be created

b. sys.dm_os_hosts

This view returns all the hosts registered with SQL Server 2005. This view also provides the resources used by each host.

1. Name: Name of the host registered
2. Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
3. Active_tasks_count: Number active tasks host placed
4. Active_ios_count: I/O requests from host waiting

c. sys.dm_os_schedulers

Sys.dm_os_schedulers view will help you identify if there is any CPU bottleneck in the SQL Server machine. The number of runnable tasks is generally a nonzero value; a nonzero value indicates that tasks have to wait for their time slice to run. If the runnable task counts show high values, then there is a symptom of CPU bottleneck.

SELECT 

scheduler_id,current_tasks_count,runnable_tasks_count 

FROM sys.dm_os_schedulers 

WHERE scheduler_id < 255

The above query will list all the available schedulers in the SQL Server machine and the number of runnable tasks for each scheduler.

d. sys.dm_io_pending_io_requests

This dynamic view will return the I/O requests pending in SQL Server side. It gives you information like:

1. Io_type: Type of pending I/O request
2. Io_pending: Indicates whether the I/O request is pending or has been completed by Windows
3. Scheduler_address: Scheduler on which this I/O request was issued

e. sys.dm_io_virtual_file_stats

This view returns I/O statistics for data and log files [MDF and LDF file]. This view is one of the commonly used views and will help you to identify I/O file level. This will return information like:

1. Sample_ms: Number of milliseconds since the instance of SQL Server has started
2. Num_of_reads: Number of reads issued on the file
3. Num_of_bytes_read: Total number of bytes read on this file
4. Io_stall_read_ms: Total time, in milliseconds, that the users waited for reads issued on the file
5. Num_of_writes: Number of writes made on this file
6. Num_of_bytes_written: Total number of bytes written to the file
7. Io_stall_write_ms: Total time, in milliseconds, that users waited for writes to be completed on the file
8. Io_stall: Total time, in milliseconds, that users waited for I/O to be completed
9. Size_on_disk_bytes: Number of bytes used on the disk for this file

f. sys.dm_os_memory_clerks

This DMV will help how much memory SQL Server has allocated through AWE.

SELECT 

SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 

FROM sys.dm_os_memory_clerks

The same DMV can be used to get the memory consumption by internal components of SQL Server 2005.

SELECT TOP 10 type, 

SUM(single_pages_kb) as [SPA Mem, Kb] 

FROM sys.dm_os_memory_clerks 

GROUP BY type 

ORDER BY SUM(single_pages_kb) DESC

g. sys.dm_os_ring_buffers

This DMV uses RING_BUFFER_RESOURCE_MONITOR and gives information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state.

SELECT 

Record FROM sys.dm_os_ring_buffers 

WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

The output of the above query will be in XML format. The output will help you in detecting any low memory notification.

RING_BUFFER_OOM: Ring buffer oom contains records indicating server out-of-memory conditions.

SELECT

record FROM sys.dm_os_ring_buffers 

WHERE ring_buffer_type = 'RING_BUFFER_OOM'

2. Database Related DMV

This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, partition usages, session information usages, etc...

a. sys.dm_db_file_space_usage

This DMV provides the space usage information of TEMPDB database.

b. sys.dm_db_session_space_usage

This DMV provides the number of pages allocated and de-allocated by each session for the database

c. sys.dm_db_partition_stats

This DMV provides page and row-count information for every partition in the current database.

The below query shows all counts for all partitions of all indexes and heaps in the MSDB database:

USE MSDB;

GO

SELECT * FROM sys.dm_db_partition_stats;

The following query shows all counts for all partitions of Backup set table and its indexes

USE MSDB

GO

SELECT * FROM sys.dm_db_partition_stats 

WHERE object_id = OBJECT_ID('backupset');

d. sys.dm_os_performance_counters

Returns the SQL Server / Database related counters maintained by the server.

The below sample query uses the dm_os_performance_counters DMV to get the Log file usage for all databases in KB.

SELECT instance_name

,cntr_value 'Log File(s) Used Size (KB)'

FROM sys.dm_os_performance_counters 

WHERE counter_name = 'Log File(s) Used Size (KB)'

3. INDEX Related DMV

This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, Partition usages, Session information usages, etc.

a. sys.dm_db_index_usage_stats

This DMV is used to get useful information about the index usage for all objects in all databases. This also shows the amount of seeks and scan for each index.

SELECT object_id, index_id, user_seeks, user_scans, user_lookups 

FROM sys.dm_db_index_usage_stats 

ORDER BY object_id, index_id

All indexes which have not been used so far in as database can be identified using the below Query:

SELECT object_name(i.object_id), 

i.name, 

s.user_updates, 

s.user_seeks, 

s.user_scans, 

s.user_lookups

from sys.indexes i 

left join sys.dm_db_index_usage_stats s 

on s.object_id = i.object_id and 

i.index_id = s.index_id and s.database_id = 5

where objectproperty(i.object_id, 'IsIndexable') = 1 and

s.index_id is null or

(s.user_updates > 0 and s.user_seeks = 0 

and s.user_scans = 0 and s.user_lookups = 0)

order by object_name(i.object_id)

Replace the Database_id with the database you are looking at.

4. Execution Related DMV

Execution related DMVs will provide information regarding sessions, connections, and various requests which are coming into the SQL Server.

a. sys.dm_exec_sessions

This DMV will give information on each session connected to SQL Server. This DMV is similar to running sp_who2 or querying Master..sysprocesses table.

SELECT

session_id,login_name,

last_request_end_time,cpu_time

FROM sys.dm_exec_sessions

WHERE session_id >= 51 – All user Sessions

b. sys.dm_exec_connections

This DMV shows all the connection to SQL Server. The below query uses sys.dm_exec_connections DMV to get connection information. This view returns one row for each user connection (Sessionid > =51).

SELECT

connection_id,

session_id,client_net_address,

auth_scheme

FROM sys.dm_exec_connections

c. sys.dm_exec_requests

This DMV will give details on what each connection is actually performing in SQL Server.

SELECT

session_id,status,

command,sql_handle,database_id

FROM sys.dm_exec_requests

WHERE session_id >= 51

d. sys.dm_exec_sql_text

This dynamic management function returns the text of a SQL statement given a SQL handle.

SELECT 

st.text

FROM

sys.dm_exec_requests r 

CROSS APPLY 

sys.dm_exec_sql_text(sql_handle) AS st

WHERE r.session_id = 51

Conclusion

Dynamic Management views (DMV) and Dynamic Management Functions (DMF) in SQL Server 2005 give a transparent view of what is going on inside various areas of SQL Server. By using them, we will be able to query the system for information about its current state in a much more effective manner and provide solutions much faster. DMVs can be used to performance tune and for troubleshooting server and queries. This article has shown an overview of what they are and how we can use them.

Diagnosing problems in SQL Server 2000 has always been a point of concern from both developers and DBA's. More often than not we would have had a need to use undocumented and DBCC commands which are sometimes very difficult to understand too. SQL Server 2005 on the contrary is like a open book, no need to use bit based operations and undocumented column values. Welcome the introduction of Dynamic Management Views and Fuctions a.k.a DMV's and DMF's.

From the basic definition these dynamic management views and functions very much replace all the DBCC command outputs and the pseudo table outputs. Hence it is far more easier to detect the health of SQL Server using these views and functions. All these are defined in the sys schema. There are two scope for these views and function: Server scoped and Database scoped. Incidentally unlike in SQL Server 2000 now to view these objects the user needs to have SELECT permissions and VIEW SERVER/DATABASE STATE permissions. Now that I mentioned about SQL Server 2000, try this yourself, create a readonly user in a database and select the sysobjects table and check the results returned in SQL Server 2000 and SQL Server 2005.

There are multiple categories in which these views and functions have been organized. The below table shows the split:  

Categories	Count
dm_broker*	4
dm_clr*	4
dm_db*	12
dm_exec*	14
dm_fts*	5
dm_io*	4
dm_os*	27
dm_qn*	1
dm_repl*	4
dm_tran*	10

So we have 85 of these views and function. To give a further split, 76 of these are views and 9 of them are functions. So these information can be queried from the system_objects system catalog table. A typical query I used was:

select * from sys.system_objects

Where name like 'dm_%' Order by 1

Each of these views and functions have different parameters or output columns and in the next couple of queries we will try to find out how to get these values.

-- Getting the column details of the DMV's

Select o.name, c.name, t.name, c.column_id, c.max_length, c.precision, c.scale

FROM sys.system_columns c

INNER JOIN sys.system_objects o

ON c.object_id = o.object_id

INNER JOIN sys.types t

ON c.user_type_id = t.user_type_id

Where o.name = 'dm_os_loaded_modules'

order by 1

In the above query we query we get the output columns for the DMV (dm_os_loaded_modules) using the system objects. In the above query we get details like name of the output column, datatype and other length specific values. Even though this will not get us the values for the table valued functions. We will have to tweak the above query for DMF's.

-- Getting the column details of the DMF's

Select o.name, t.name, p.*

FROM sys.system_parameters p

INNER JOIN sys.system_objects o

ON p.object_id = o.object_id

INNER JOIN sys.types t

ON p.user_type_id = t.user_type_id

Where o.name = 'dm_exec_sql_text'

order by 1

In the above query we try to get the parameters for the DMF (dm_exec_sql_text) using the systtem_parameters system catalog. So the output would show the above DMF has a parameter @handle. So if we queried this function for the sql text for a given query in the cache. The handle can be got from dm_exec_query_stats or other related views.

Conclusion

This is just a starter to understanding the different views and functions available in SQL Server 2005. As said we have just touched the surface of what these views can give us. In the future articles we will drill a step deep into these views and functions.