About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

New Dynamic Management Views (DMVs)

New Dynamic Managemtn Views in SQL Server 2012
------------------------------
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.

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%';















The new OS Volume Stats DMV

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: 








































Labels:
,
,