By: Ashish Kumar Mehta | 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.
- sys.dm_server_memory_dumps
- sys.dm_server_services
- 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:
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
