New SQL Server 2012 DMVs to get configuration, installation and memory dump information

By: Ashish Kumar Mehta   |   Updated: 2011-11-29   |   Comments (3)   |   Related: > Dynamic Management Views and Functions

Problem

SQL Server 2012 has a lot of new features to offer.  A new set of features include new DMVs that can return SQL Server configuration, installation and memory dump related information.

Solution

In this tip we will discuss how to utilize the below dynamic management views which are new in SQL Server 2012.  It is important to be aware that on every SQL Server service restart the data in these Dynamic Management Views is reset.

1. sys.dm_server_memory_dumps
2. sys.dm_server_services
3. sys.dm_server_registry

Using sys.dm_server_memory_dumps Dynamic Management View

The sys.dm_server_memory_dumps dynamic management view returns one row for each memory dump file that is generated by the SQL Server Database Engine. If you notice entries in this view then you should alert a Senior DBA so they can check what caused the memory dumps which could lead to data corruption.  I also recommend you read the following article Finding which queries were executing from a SQL Memory Dump which explains how to find the queries that were executing when the memory dump were created.

SELECT * FROM sys.dm_server_memory_dumps
GO

Using sys.dm_server_services Dynamic Management View

The sys.dm_server_services dynamic management view returns information with respect to startup type, running status, when service was last started, etc... for SQL Server, Full-Text, and SQL Server Agent services for the current SQL Server instance.

SELECT * FROM sys.dm_server_services 
GO

Using sys.dm_server_registry Dynamic Management View

The sys.dm_server_registry dynamic management view returns SQL Server Configuration and Installation information which is stored in the Windows Registry for the current instance of SQL Server. This is a very useful DMV which can quickly give you relevant information with respect to the Current Version, Path of Master Database, Error Log files, etc... as shown below.

SELECT * FROM sys.dm_server_registry 
GO

Next Steps

• Stay tuned for more tips on SQL Server 2012 and check out these related tips:
• Creating a SQL Server 2012 User Defined Server Level Role
• SQL Server 2012 Contained Database Feature
• Configuring Server Startup Parameters in SQL Server 2012

About the author

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips