Denali CTP3 is already released and we have new DMV’s accompanied with this version which will be very useful for us. I’m going to cover the DMV that are all generic and not to a specific feature of Denali, I’ll will cover those DMV’s while writing article on that feature. There are around 8 DMV’s which I’m going to discuss it now.Below are the list of DMV’s that I’m talking about
- sys.dm_os_cluster_properties
- sys.dm_os_server_diagnostics_log_configurations
- sys.dm_os_windows_info*
- sys.dm_server_memory_dumps*
- sys.dm_server_registry*
- sys.dm_server_services*
- sys.dm_tcp_listener_states
- sys.dm_db_log_space_usage
*Available in SQL Server 2008 R2 SP1 also
Let’s discuss all these in detail
sys.dm_os_cluster_properties
This DMV will help you in obtaining cluster properties of that instance, it will be returning only one row. If you run a select on this DMV on an standalone box no rows are returned. For more information on the values that are returned by this DMV can be found at http://technet.microsoft.com/en-us/library/gg471591%28SQL.110%29.aspx. Based on this analysis you can set the values for cluster.
sys.dm_os_server_diagnostics_log_configurations
This DMV will give you cluster log file related information. It will return one row with current configuration for the SQL Server failover cluster diagnostic log.
Where is_enabled – states whether logging is turned on or not. Values will be 1 or 0
path – Path where the log files should be stored
max_size – Maximum size of the log file in MB
max_files – Maximum number of files that can be stored in the path before recycling.
sys.dm_os_windows_info
I don’t need to provide more description on this DMV as the name itself will give you an clear idea. Yes this DMV will give you information above the operation system, it’s service pack level, default language and it’s SKU ID value.
sys.dm_server_memory_dumps
This DMV gives you information about memory dumps generated in the server. It will give you the path, size and creation time of the memory dump so that it will be easy for the DBA to get the memory dump.
sys.dm_server_registry
Are you tired using xp_reg_read extended procedure? If yes then this wonderful DMV will replace it with all necessary registry information related to that instance. It’s really very easy to obtain all those registry information related to that instance with just select on this DMV. You can get almost all the info from registry.
sys.dm_server_services
This piece of DMV is helpful to DBA’s. I really heard from my DBA’s that they are not able to get SQL Server agent status properly using any T-SQL when they connected to SQL. Now it’s time, MS has developed this DMV. You can get excellent information about SQL Server services, very easy to obtain the status and property of the services.
sys.dm_tcp_listener_states
Not sure on which port does SQL Server listens? Do you require the port information from SQL? Now it’s very easy to get the TCP port details with this DMV. It will give you the details about all the ports that SQL Server is listening too.
sys.dm_db_log_space_usage
Name of the DMV itself is self descriptive! Yes this DMV will give you information related to the log file usage. You will be getting similar information when you retrieve the data using DBCC SQLPERF(‘logspace’). However this DBCC command will give you output for all the databases where as this DMV will give you output for the specific database as shown below
There are lot more DMV’s are there in Denali which is specific to a new feature added to denali, we will discuss those DMV’s in future articles. Hope these DMV’s will ease your work now!!
Leave a Reply to DotNetShoutout Cancel reply