CLR mountpoint freespace query scaling *really bad* with high number of mountpoints

• Theo Ekelmans

  SSCarpal Tunnel

  Points: 4592

  More actions

  February 24, 2015 at 3:06 pm

  #304580

  *EDIT* Workaround available at the end of this discussion chain

  Hi All,

  In order to get to the size and free space info on mountpoints i used several variations on this theme:

  EXEC master..xp_cmdshell 'wmic logicaldisk where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") get deviceid,volumename /Format:csv'

  and then decode what came back, not pretty, but hey... it worked and was reasonably fast, about 100-400ms or so.

  But due to some weird DR STRETCHED SAN config fubar of our hosting supplier we found ourselves unable to expand mountpoints on the DR stretched 3 way cluster, and each time when we ran out of space we simply piled on more and more mountpoints and files to databases.

  Don't shoot the messenger guys.... i just inherited this setup and have to deal with it until it reaches it's intended lifespan.

  As the mountpoint number went up, the runtime of the xp_cmdshell / wmic took longer and longer to complete, up to several minutes, and i started to worry 🙁

  So convinced something had to be done, i grabbed a C# book and did some serious catching up on my aging 30 year old C knowledge (yeah, i still have the white/blue kernighan and richie book from my school days), and wrote my first CLR.

  After the struggling with hello world issues that come with using Visual Studio 2013 C# for the first time, i got a cmd window working with precisely what i needed. But only to find out that .NET SQL CLR is "some what limited" in the things you can use.

  Long story short, (and with many a thanks to all who share code snippets) this is what i came up with; a CLR that does 3 things:

  - Physical disk info (no mountpoint support)

  - Logical disk info (with mountpoint support)

  - and a SPLIT function.

  Here's my code (https://www.dropbox.com/s/jt8zvgk47ptql3q/CLR.sql?dl=0)

  ----------------------------------------------------------------------------------------------------------------------

  using System;

  using System.Collections; /// IEnumberable

  using System.Data;

  using System.Data.SqlClient;

  using System.Data.SqlTypes;

  using System.Diagnostics;

  using System.IO; /// DriveInfo

  using Microsoft.SqlServer.Server;

  public partial class StoredProcedures

  {

  //-------------------------------------------------------------------------------------

  // Logical volume info (works for mountpoints)

  //-------------------------------------------------------------------------------------

  /*

  AvailableFreeSpace Indicates the amount of available free space on a drive.

  DriveFormat Gets the name of the file system, such as NTFS or FAT32.

  DriveType Gets the drive type.

  IsReady Gets a value indicating whether a drive is ready.

  Name Gets the name of a drive.

  RootDirectory Gets the root directory of a drive.

  TotalFreeSpace Gets the total amount of free space available on a drive.

  TotalSize Gets the total size of storage space on a drive.

  VolumeLabel Gets or sets the volume label of a drive.

  */

  [Microsoft.SqlServer.Server.SqlProcedure]

  public static void spClrLogicalDriveInfo()

  {

  string serverName = Environment.MachineName;

  PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", serverName);

  SqlDataRecord record = new SqlDataRecord(

  new SqlMetaData("DriveLetter", SqlDbType.NVarChar, 256),

  new SqlMetaData("TotalSize", SqlDbType.BigInt),

  new SqlMetaData("TotalFreeSpace", SqlDbType.BigInt),

  new SqlMetaData("DriveFormat", SqlDbType.VarChar, 32),

  new SqlMetaData("DriveType", SqlDbType.VarChar, 32),

  new SqlMetaData("VolumeLabel", SqlDbType.VarChar, 20),

  new SqlMetaData("AvailableFreeSpace", SqlDbType.BigInt));

  SqlContext.Pipe.SendResultsStart(record);

  foreach (string instanceName in pcc.GetInstanceNames())

  {

  PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", instanceName, serverName);

  PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName);

  float percentfree = pcPercentFree.NextValue();

  if (percentfree == 0) { percentfree = 1; };

  float TotalFreeSpace = pcFreeMbytes.NextValue();

  float TotalSize = (TotalFreeSpace * 100) / percentfree;

  if (instanceName != "_Total")

  {

  record.SetSqlString(0, instanceName + @"\"); //DriveLetter

  record.SetSqlInt64 (1, Convert.ToInt64(TotalSize)); //TotalSize

  record.SetSqlInt64 (2, Convert.ToInt64(TotalFreeSpace)); //TotalFreeSpace

  record.SetSqlString(3, ""); //DriveFormat (not supported by PerfMon)

  record.SetSqlString(4, ""); //DriveType (not supported by PerfMon)

  record.SetSqlString(5, ""); //VolumeLabel (not supported by PerfMon)

  record.SetSqlInt64 (6, Convert.ToInt64(0)); //AvailableFreeSpace (not supported by PerfMon)

  SqlContext.Pipe.SendResultsRow(record);

  }

  }

  SqlContext.Pipe.SendResultsEnd();

  }

  //-------------------------------------------------------------------------------------

  // Performance counters

  //-------------------------------------------------------------------------------------

  // ToDo

  };

  public partial class UserDefinedFunctions

  {

  //-------------------------------------------------------------------------------------

  // Physical disk info (no mountpoint support)

  //-------------------------------------------------------------------------------------

  /*

  AvailableFreeSpace Indicates the amount of available free space on a drive.

  DriveFormat Gets the name of the file system, such as NTFS or FAT32.

  DriveType Gets the drive type.

  IsReady Gets a value indicating whether a drive is ready.

  Name Gets the name of a drive.

  RootDirectory Gets the root directory of a drive.

  TotalFreeSpace Gets the total amount of free space available on a drive.

  TotalSize Gets the total size of storage space on a drive.

  VolumeLabel Gets or sets the volume label of a drive.

  */

  [SqlFunction(FillRowMethodName = "FillRow",

  TableDefinition = "DriveLetter nvarchar(256)," +

  "TotalSize bigint null," +

  "TotalFreeSpace bigint null," +

  "DriveFormat nvarchar(32) null," +

  "DriveType nvarchar(32) null," +

  "VolumeLabel nvarchar(20) null," +

  "AvailableFreeSpace bigint null"

  )]

  public static IEnumerable fnClrDriveInfo()

  {

  return System.IO.DriveInfo.GetDrives();

  }

  public static void FillRow(Object obj

  , out string DriveLetter

  , out SqlInt64 TotalSize

  , out SqlInt64 TotalFreeSpace

  , out string DriveFormat

  , out string DriveType

  , out string VolumeLabel

  , out SqlInt64 AvailableFreeSpace

  )

  {

  DriveInfo drive = (DriveInfo)obj;

  DriveLetter = drive.Name;

  DriveType = drive.DriveType.ToString();

  // Check if the drive is ready (cdrom drives, SD cardreaders etc)

  if (drive.IsReady)

  {

  TotalSize = new SqlInt64(drive.TotalSize) / 1048576;

  TotalFreeSpace = new SqlInt64(drive.TotalFreeSpace) / 1048576;

  DriveFormat = drive.DriveFormat;

  VolumeLabel = drive.VolumeLabel;

  AvailableFreeSpace = drive.AvailableFreeSpace / 1048576;

  }

  else

  {

  TotalSize = new SqlInt64();

  TotalFreeSpace = new SqlInt64();

  DriveFormat = null;

  VolumeLabel = null;

  AvailableFreeSpace = new SqlInt64();

  }

  }

  //-------------------------------------------------------------------------------------

  // Split replacement

  //-------------------------------------------------------------------------------------

  [SqlFunction(Name = "fnClrSplit",

  FillRowMethodName = "FillSplitRow",

  TableDefinition = "txt nvarchar(10)")]

  public static IEnumerable fnClrSplit(SqlString str, SqlChars delimiter)

  {

  if (delimiter.Length == 0)

  return new string[1] { str.Value };

  return str.Value.Split(delimiter[0]);

  }

  public static void FillSplitRow(object row, out SqlString str)

  {

  str = new SqlString((string)row);

  }

  };

  ----------------------------------------------------------------------------------------------------------------------

  And after some testing i rolled it out, hoping my mountpoint info would be returned much faster...

  And murphy made damn sure it was'nt.... not even by a long shot :'(

  Having learned the hard way never to trust just one measurement, I tested it against 45 SQL instances, and then something caught my eye.....

  

  The more mountpoints there are, the longer it took, and the curve was *no way near linear*.

  So this is where i am now.... ,having almost run out of available letters of the alphabet, having no way to stop the grow of the mountpoints or databases, and fast loosing the ability to check to see if the mountpoints are running out of space from within SQL.

  So i'm hoping some fellow DBA out there, cursed by as many a mountpoint as i am had cracked a way to get stable performing free space info on mountpoints?

  Suggestions anyone?

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  February 25, 2015 at 7:41 am

  #1779912

  Theo Ekelmans (2/24/2015)

  ---

  After the struggling with hello world issues that come with using Visual Studio 2013 C# for the first time, i got a cmd window working with precisely what i needed. But only to find out that .NET SQL CLR is "some what limited" in the things you can use.

  Hi Theo. So, by the statement above, are you meaning that the console app runs the code quickly and that it is only slow when executed within SQL Server via SQLCLR?

  As far as I can see, your code for the two drive space methods looks fine.

  For the Split function there are two improvements that should be made to make it much faster:

  1) in the SqlFunction attribute, add the property: IsDeterministic=true (as in "SqlFunction(IsDeterministic = true)")

  2) You don't need SqlChars for the delimiter. You could use SqlString. But if you are going to use SqlChars:

  a) you might be better off referencing it via "Delimiter.Buffer[0]"

  b) If you are publishing via Visual Studio / SSDT, be sure to add a SqlFacet to the delimiter parameter to prevent it from automapping SqlChars to NVARCHAR(MAX) (since NVARCHAR(MAX) will slow down the operation so only use it when necessary): "[SqlFacet(MaxSize = 10)] SqlChars delimiter"

  Also, while not making it faster, you should map the output field of "txt" to NVARCHAR(4000) instead of NVARCHAR(10). There is no reason to not do this and it is more flexible as it can handle any string passed in, including a 4000 characters string with no delimiter.

  You can get some SQLCLR functionality (including the Split and GetDriveInfo functions) already done and for free via the SQL#[/url] library (which I am the author of).

  And for more info on working with SQLCLR in general, I am writing a Stairway series here on SQL Server Central: Stairway to SQLCLR[/url]

  Take care,

  Solomon...

  SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
  Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
  Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
  Info sites — Collations     •     Module Signing     •     SQLCLR

• Theo Ekelmans

  SSCarpal Tunnel

  Points: 4592

  More actions

  February 25, 2015 at 8:41 am

  #1779943

  Hi Solomon,

  Thanks for the all tips, and i will be sure to use them.

  By "But only to find out that .NET SQL CLR is "some what limited" in the things you can use." i ment that i got errors when i tried to use this construct in the CLR

  ManagementScope scope = new ManagementScope("\\\\.\\ROOT\\cimv2");

  ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Volume where Drivetype = 3");

  ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);

  ManagementObjectCollection queryCollection = searcher.Get();

  It seems that ManagementObjectSearcher is .NET 2 based an not allowed in a CLR.

  It would have solved my performance problem, because if i use it in my console app, it rips through all 139 mountpoints in 500 ms flat; hundreds of times faster then using the perfmon counter workaround.

  console app source

  -------------------------------------------------------------------------------------

  //-------------------------------

  // Win32_Volume version

  //-------------------------------

  using System;

  using System.Collections.Generic;

  using System.Text;

  using System.Management;

  using System.IO;

  namespace ConsoleApplication1

  {

  class Program

  {

  static void Main()

  {

  DateTime StartDT = DateTime.Now;

  DateTime StartStepDT;

  DateTime EndStepDT;

  double StepRuntimeMs;

  double EnumRuntimeMs;

  StartStepDT = DateTime.Now;

  ManagementScope scope = new ManagementScope("\\\\.\\ROOT\\cimv2");

  ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Volume where Drivetype = 3");

  ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);

  ManagementObjectCollection queryCollection = searcher.Get();

  EndStepDT = DateTime.Now;

  StepRuntimeMs = (EndStepDT - StartStepDT).TotalMilliseconds;

  Console.WriteLine("Init Runtime: {0, 8} ms", StepRuntimeMs);

  string sVolumeLabel = "";

  //string[,] saReturn = new string[queryCollection.Count, 7];

  int i = 0; // counter for foreach

  StartStepDT = DateTime.Now;

  foreach (ManagementObject m in queryCollection)

  {

  EndStepDT = DateTime.Now;

  EnumRuntimeMs = (EndStepDT - StartStepDT).TotalMilliseconds;

  StartStepDT = DateTime.Now;

  //if (string.IsNullOrEmpty(Convert.ToString(m["VolumeName"]))) { sVolumeLabel = "Local Disk"; } else { sVolumeLabel = Convert.ToString(m["VolumeName"]); } // Disk Label

  //string sSystemName = Convert.ToString(m["SystemName"]); // Name of computer

  string sDriveLetter = Convert.ToString(m["Name"]); // Drive Letter

  if (m["DeviceID"] != null)

  {

  decimal dSize = Math.Round((Convert.ToDecimal(m["Capacity"]) / 1048576), 2); //HDD Size in MB

  decimal dFree = Math.Round((Convert.ToDecimal(m["FreeSpace"]) / 1048576), 2); // Free Space in MB

  decimal dUsed = dSize - dFree; // Used HDD Space in MB

  //int iPercent = Convert.ToInt32((dFree / dSize) * 100); // Percentage of free space

  //saReturn[i, 0] = sSystemName;

  //saReturn[i, 1] = sDriveLetter;

  //saReturn[i, 2] = sVolumeLabel;

  //saReturn[i, 3] = Convert.ToString(dSize);

  //saReturn[i, 4] = Convert.ToString(dUsed);

  //saReturn[i, 5] = Convert.ToString(dFree);

  //saReturn[i, 6] = Convert.ToString(iPercent);

  Console.WriteLine("-----------------------------");

  //Console.WriteLine("SystemName : " + sSystemName);

  Console.WriteLine("DriveLetter : " + sDriveLetter);

  Console.WriteLine("VolumeLabel : " + sVolumeLabel);

  Console.WriteLine("Size : " + Convert.ToString(dSize));

  Console.WriteLine("Used : " + Convert.ToString(dUsed));

  Console.WriteLine("Free : " + Convert.ToString(dFree));

  i++; // increase counter. This will add the above details for the next drive.

  }

  else

  {

  Console.WriteLine("-----------------------------");

  //Console.WriteLine("SystemName : " + sSystemName);

  Console.WriteLine("DriveLetter : " + sDriveLetter);

  }

  EndStepDT = DateTime.Now;

  StepRuntimeMs = (EndStepDT - StartStepDT).TotalMilliseconds;

  Console.WriteLine(" Enum Runtime: {0, 8} ms", EnumRuntimeMs);

  Console.WriteLine(" Step Runtime: {0, 8} ms", StepRuntimeMs);

  StartStepDT = DateTime.Now;

  }

  Console.WriteLine("");

  DateTime EndDT = DateTime.Now;

  double RuntimeMs = (EndDT - StartDT).TotalMilliseconds;

  Console.WriteLine("");

  Console.WriteLine("Runtime: {0, 8} ms", RuntimeMs);

  }

  }

  }

  -----------------------------------------------------------------------------------------------------

  Since this runs really reliable in an command shell i'm tempted to get the data using xp_cmdshell and a 6 KB console app.

  Unless of course you know a way to get to get 2 things workin; mountpoint enumeration and getting size, free and volumename?

  Grtz, t.

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  February 25, 2015 at 9:39 am

  #1779971

  Theo Ekelmans (2/25/2015)

  ---

  By "But only to find out that .NET SQL CLR is "some what limited" in the things you can use." i ment that i got errors when i tried to use this construct in the CLR

  ManagementScope scope = new ManagementScope("\\\\.\\ROOT\\cimv2");

  ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Volume where Drivetype = 3");

  ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);

  ManagementObjectCollection queryCollection = searcher.Get();

  It seems that ManagementObjectSearcher is .NET 2 based an not allowed in a CLR.

  It would have solved my performance problem, because if i use it in my console app, it rips through all 139 mountpoints in 500 ms flat; hundreds of times faster then using the perfmon counter workaround.

  Since this runs really reliable in an command shell i'm tempted to get the data using xp_cmdshell and a 6 KB console app.

  Ok, so here's the dealio:

  .NET 2.0 has nothing to do with it. The problem with your original plan is that those classes (ManagementScope, ManagementObjectSearcher , etc.) are in the System.Management DLL, and that is not in the Supported .NET Framework Libraries list. So you have two options:

• Manually load System.Management.dll via CREATE ASSEMBLY. This will require you to most likely create it as UNSAFE, which in turn will require that your custom assembly be created as UNSAFE. And that will require you to also set the database to TRUSTWORTHY ON in order to get the Microsoft DLL to load as UNSAFE. Since you don't know what exactly is going on in the Microsoft DLL, it could be doing things that are fine for a single-threaded process but exhibit odd behavior in a shared environment such as SQLCLR. This is due to there being a single, shared App Domain (per database, per owner) in SQL Server. If the code was not expecting multiple users/processes (i.e. Sessions / SPIDS) to share static, class-level variables then there might be issues. So it just requires a bit of extra testing, but can certainly be done. I would recommend using a non-user-facing instance dedicated to internal maintenance jobs. I think SQL Server Express is great for this. I don't think it comes with SQL Agent, but it can be connected to via a Linked Server from an instance that is Standard or Enterprise and a SQL Agent job on one of those could exec a proc on the Express instance over a Linked Server rather easily. This set up would isolate any risk of instability of the UNSAFE code. HOWEVER, this won't work if System.Management is a "mixed" Assembly and not "pure" MSIL. And even if it is today, Microsoft might change it to "mixed" in the future which will mean that it won't be loadable into SQL Server and you will be forced to recode this functionality. That might never happen, but it could, and I have seen one question on Stack Overflow due to someone experiencing this very thing. The next Level of my Stairway to SQLCLR series covers all of this (should be published in 2 weeks).
• Use xp_cmdshell to run your console app. Using this method I would suggest formatting the output of the console app to be in XML. This should allow you to grab it from the result set ouptut of xp_cmdshell and then easily parse it using the built-in XML functions. I don't know if there is a maximum size of output that xp_cmdshell can handle, but if there is and this is too much, just have the console app save the XML to a file. And to make things a little safer, use the .NET method for generating a temp file name (in the File class I believe) and return that as output from the console app. Grab that temp file name via the result set output and then open that in your proc and get the XML. Of course, getting the file will require creating a SQLCLR function to File.ReadAllLines() or something like that, but that is supported and only requires EXTERNAL_ACCESS for the assembly. And since you can sign the assembly, you can create an Asymmetric Key from that DLL, a Login based on that Key, and then grant that Login the "EXTERNAL ACCESS ASSEMBLY" permission, all of which will allow you to set the Assembly to EXTERNAL_ACCESS without having to set the database to TRUSTWORTHY ON.

I hope this helps.

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations     •     Module Signing     •     SQLCLR