Three DMVs that arrived with SQL Server 2008 R2 SP1.
Applies: SQL Server 2008 R2 SP1, SQL Server 2012, SQL Server 2014.
Service Pack 1 for Microsoft SQL Server 2008 R2 brought 3 dynamic
management views (DMVs), and all of them require the VIEW SERVER STATE
permission. The DMVs are explained below.
1. sys.dm_server_memory_dumps.
This new DMV displays the location, date and size of dumps created by SQL Server
dumps. A full memory dump is expected to have the size of the buffer pool or the
size of the RAM of a server, while a filtered dump (same as a full dump but
excluding the buffer pool) should have 3-4 GBs of disk space. (See references
for more information).
Here is the output of this new DMV:
SELECT * FROM sys.dm_server_memory_dumps
2. sys.dm_server_services.
This new DMV provides information about SQL Server Services. On SQL Server 2008
R2, this DMV provides information about SQL Server Service and the SQL Server
Agent service only, as shown in the image below.
ON SQL Server 2012, the sys.dm_server_services DMV provides information about
SQL Server service, SQL Server Agent and the Full-text search service.
3. sys.dm_server_registry.
You can use the sys.dm_server_registry DMV to retrieve all configuration and
installation information that is stored in the Windows registry for a SQL Server
instance.
On the below you can see all the information related to the TCP configuration
of a specific SQL instance.
References.
Intro to Debugging a Memory Dump.
![[Company Logo Image]](images/SQLCofee.jpg)
