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.





