New and Modified DMVs in SQL Server 2012

 

Few changes have happened to DMVs. If you compare the number of DMVs with SQL Server 2008 R2 SP1 dmvs, there are 33 new DMVs.

New DMVs

In SQL Server 2012, you have 174 DMVs in total compared to 141 you have in SQL Server 2008 R2. Out of these 141 DMVs you have in R2 5 of them added by the SP1. So effectively you have 38 new DMVs in SQL Server 2012.

Let us start with those 5 DMVs.

dm_server_registry

Most of your SQL Server configurations are saved in the windows registry. So in case you want those to be read, you might need to read the registry.

In previous version you have to use xp_reg_read undocumented system stored procedures. Since this is an undocumented, many DBAs do not want to use in production environments.

However, with introduction of the DMV, dm_server_registry, you can simply query it with necessary where clause and you will be able to get the necessary configuration information from the registry.

dm_os_windows_info

How often you need windows information to capture in SQL Server? If so you may need to write powershell scripts. However, with the dm_os_windows_info, you can retrieve the information about Windows version, Service Pack level and Language of your operating system.

Insert Image 5528_09_06.png

dm_server_services

How often you need to check whether the SQL Server agent job is running? If you need to execute a job from application, if SQL Server Agent Job is stopped, it will fail. To do a clean task, it will better to verify whether the SQL Server Agent Job is running and give an appropriate error message so that users are attend to the correct issue.

With dm_server_services DMVs you can verify the all the SQL Server services and the startup type as well. Also, for trouble shooting purposes, you can find the last startup time as well.

dm_server_memory_dumps

This DMV returns a list of memory dump files generated by the SQL Server Database Engine along with creation time and the dump file size.

dm_os_volume_stats

This is a function where you need to provide database id and the file id of which need to check the volume stats.

This DMF will return the size of the volume and remaining space on the volume containing that file, file system type (NTFS or FAT) and few more details.

dm_db_log_space_usage

If you are a DBA, you don’t want your log files to auto grow as it would hamper the transaction performance. So you need to monitor the log file space. Most of the times, DBAs will use SQL Server Agent Alerts.

However problem with alert is that alert is event driven not time driven. In case, there is a long running transaction, log will not be cleared and during this time alerts will be received to the responders. So DBAs prefer to use SQL Server Agent Job so that they can schedule them according to the requirement.

In current version of SQL Server using DBCC SQLPERF you can do this using following script.

DBCC SQLPERF also has new column called Principal File Group Name is added in SQL Server 2012. So in case you need to run above script in SQL Server 2008 R2 or below, you need to comment out that column in the temporary table.

Things are easy in SQL Server 2012 with very simple DMV.

Similarly, you can view the used log space from the Disk Usage report available in the SSMS.

Report also has slight but very important change in SQL Server 2012. Now data label is also included in the report. Earlier, you don’t have the actual value and you need to guess it.

dm_exec_describe_first_result_set

This DMV gets T-SQL statement (this can be a procedure as well) and return the metadata of the first result set.

Similarly dm_exec_describe_first_result_set_for_object DMV will give you the same result but difference is you need to pass object id of the stored procedure or a trigger. If you passed ID of any other object such as a view, table, function, or CLR procedure, an error will be specified in the error columns of the result.

dm_db_database_page_allocations

How many times you used DBCC IND in production environments? DBCC IND and DBCC PAGE are undocumented but still many are using them in production. dm_db_database_page_allocations is replacement to DBCC IND. Dm_db_database_page_allocations which can be used to find allocated pages for the database object. This function takes 5 parameters, DatabaseId, tableID, indexID, partitionID and mode.

We have discussed furthermore new DMVs in Alwayson, Full Text Search, Waits and Contained database section.

Modified DMVs

Following DMVs are changed to increase the better usage.

sys.dm_exec_query_stats

sys.dm_exec_query_stats is often used DMV by joining few other DMVs to analyze badly behaving queries. Four additional columns are added to this DMV and they are,

total_rows	Total number of rows returned by query.
last_rows	Number of the rows return by the last execution of the query.
min_rows	Minimum numbers of the rows returned by the query after it is compiled.
max_rows	Maximum numbers of the rows returned by the query after it is compiled.

Following is the query you need to execute to analyze bad performing queries.

We testing for performance, DBAs check for page reads. However, if the page reads are high, it might be a bad query plan or it can be user has request for large data set. With these four columns, you can eliminate that dilemma and decide why you have high page reads.

Following DMVs are changed only by data length.

DMV	Column Name	Previous Length	Length in SQL Server 20012
sys.dm_os_memory_cache_entries	entry_data	nvarchar(2048)	nvarchar(3072)
sys.dm_os_ring_buffers	record	nvarchar(2048)	nvarchar(3072)
sys.dm_os_waiting_tasks	resource_description	nvarchar(2048)	nvarchar(3072)
sys.dm_xe_map_values	map_value	nvarchar(2048)	nvarchar(3072)
sys.dm_xe_object_columns	description	nvarchar(256)	nvarchar(3072)
sys.dm_xe_objects	description	nvarchar(256)	nvarchar(3072)
sys.dm_xe_packages	description	nvarchar(256)	nvarchar(3072)
sys.dm_xe_session_events	event_predicate	nvarchar(2048)	nvarchar(3072)
sys.dm_xe_session_object_columns	column_value	nvarchar(2048)	nvarchar(3072)
sys.syscacheobjects	cacheobjtype	nvarchar(17)	nvarchar(50)