SQL Server 2011 “Denali” CTP3 introduced couple of new Dynamic Management Views (DMV). However, documentation says that some of them were already introduced in SP1 of SQL Server 2008 R2 but it was only two month ago (July 2011). I believe that these views were developed in new release (“Denali”) but were so impactless that they were inserted to the latest SP of 2008 R2. Here are four of them which I find quite useful:

1. sys.dm_os_windows_info
   This DMV displays information about Windows version, Service Pack level and Language of your operating system.
   
   
2. sys.dm_server_registry

   This is replacement for xp_reg_read, which is used for reading SQL Server instance-related Windows registry entries.

3. sys.dm_server_services
   This is very useful – it returns state of Windows services related to SQL Server instance. You can find out whether SQL Server Agent or Full Text indexing service are running.
4. sys.dm_tcp_listener_states
   You can query which ports SQL Service listens on your server.

Tags: denali, DMV, listener, management views, operating system, ports, server agent, service pack level, SQL, SQL Server, SQL Server 2008 R2, sql server 2011, windows registry, windows version, xp reg