New Dynamic Managemtn Views in SQL Server 2012
------------------------------
1) select * from sys.dm_server_services
------------------------------
1) select * from sys.dm_server_services
This query fetches information about the current SQL Server instance's Windows services, including information about whether the services are clustered, as shown in the following screenshot:
2) select * from sys.dm_server_registry
A. Display the SQL Server services
The following example returns registry key values for the SQL Server and SQL Server Agent services for the current instance of SQL Server.
A. Display the SQL Server services
The following example returns registry key values for the SQL Server and SQL Server Agent services for the current instance of SQL Server.
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N'%ControlSet%';
B. Display the SQL Server Agent registry key values The following example returns the SQL Server Agent registry key values for the current instance of SQL Server. |
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N'%SQLAgent%';
C. Display the current version of the instance of SQL Server |
The following example returns the version of the current instance of SQL Server.
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE value_name = N'CurrentVersion';
|
D. Display the parameters passed to the instance of SQL Server during startup
The following example returns the parameters that are passed to the instance of SQL Server during startup.
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%Parameters';
E.Return network configuration information for the instance of SQL Server
The following example returns network configuration values for the current instance of SQL Server. |
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%ControlSet%';
|
Finally there is a DMV which was quietly introduced in SQL Server 2008 R2, but is worthy of another mention here, as it returns information about the file system for any given file for any database.
This is particularly useful to the DBA who runs their SQL Server on a Storage Area Network (SAN), which is becoming more commonplace. The following example returns data about the log file (
file_id: 2) for the master database (database_id: 1):
SELECT * FROM sys.dm_os_volume_stats (1,2)
Run this query and you will see something similar to the following screenshot, which details the file type, size and how much space is remaining: