sys.dm_io_cluster_shared_drives – Day 4 – One DMV a Day

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.

Cluster_SQL_Properties

 

SELECT * FROM sys.dm_io_cluster_shared_drives

 

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *