Hello Geeks and welcome to the Day 4 of the long series to come in One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
I wanted to complete the Index related DMVs today but did not want to rush things on Monday morning with the very important DMV sys.dm_db_index_operational_stats. Instead I will be starting with the less used but very helpful DMVs related to IO. Today I will be covering sys.dm_io_cluster_shared_drives. This DMV is going to be replaced with sys.dm_io_cluster_valid_path_names from 2014 and will be deprecated in future releases. The output has more information in the new DMV than the older version of it.
These DMVs are used for getting the list of drives which are part of the current cluster group on which SQL Server service is dependent on. sys.dm_io_cluster_shared_drives will return the below results if it has dependency on O, R and a mount point drives as per the below screenshot.
SELECT * FROM sys.dm_io_cluster_shared_drives
sys.dm_io_cluster_shared_drives can be used to determine which drives should be checked in a cluster but does not return the mount points. When you are running into space issues and run xp_fixeddrives but it returns all the drives active on that node in the cluster again excluding mount points. To determine the space issues with mount points there is no direct way. I have written a procedure which gets drive details including mount points and will post it in another blog soon.
In addition to the drive letter in the new DMV, sys.dm_io_cluster_valid_path_names it also provides the details like volume mount points or drive path, current owner and a bit which tells if the drive on which the drive path is located is clustered.
Tomorrow I will be covering sys.dm_db_index_operational_stats DMV. So, stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
