SQL SERVER – Three DMVs – sys.dm_server_memory_dumps – sys.dm_server_services – sys.dm_server_registry

In this blog post we will see three new DMVs which are introduced in Denali. The DMVs are very simple and there is not much to describe them. So here is the simple game. I will be asking a question back to you after seeing the result of the each of the DMV and you help me to complete this blog post.

SELECT *
FROM sys.dm_server_memory_dumps

Above DMV returns following result.

SELECT *
FROM sys.dm_server_services

Above DMV returns following result.

SELECT *
FROM sys.dm_server_registry

Above DMV returns following result.

Now here is the question for you – how will you use this DMV in your application. One thing for sure is that this makes it easier to find out various information. We can easily know which services are running and what was the start time, etc. but also where exactly you will use this in production server?

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names.

Reference: Pinal Dave (https://blog.sqlauthority.com)