Getting Drive Info Part 3, CLR

In the first two parts, (Part 1, Part 2), of the Getting Drive Info series the techniques to gather drive info with methods that will work on SQL Server 2000 were presented. Now it is time to move on to look at the options that the newer versions of SQL Server can use. In this article the drive info will be retrieved via the CLR. Andy Novick (Blog) wrote the code to accomplish this in his article, CLR Function to return free space for all drives on a server, on MSSQLTips.com. Security and environment set up were not covered in this article. Rather than reinvent the wheel, the code from the referenced article will be used. I will discuss security and the setup I created to run it .

The first attempt was to build and deploy to the master database. This resulted in the following error:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate “drive_info”:
System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.IO.Directory.GetLogicalDrives()
at System.IO.DriveInfo.GetDrives()
at UserDefinedFunctions.drive_info()

After getting the above error and not really understanding the ramifications of an unsafe assembly, I decide it was time to do some reading. I found that the book, A Developers Guide to SQL Server 2005, had an excellent discussion of the CLR and the different assembly security types. Now I was ready to create an environment for this function.

Since this was a function for DBAs to use for system monitoring, I created a new database named DBA_Tools and set the owner to sa. In order to let an Unsafe assembly execute, the TRUSTWORTHY option needs to be enabled. Turn on Trustworthy with this command.

ALTER DATABASE DBA_Tools SET TRUSTWORTHY ON

Make sure that the CLR server option is enabled, otherwise you can’t execute the CLR function. This can be accomplished with this script.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXECUTE sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

One thing that I did notice is having the CLR server option enabled or disabled had no impact on deploying the function. This is a great feature if you typically don’t want the CLR enabled all of the time. The CLR can be enabled by the script that runs drive_info() function and then be disabled immediately after.

Moving on to the Visual Studio setup, make sure the connection in the CLR database project points to the DBA_Tools database. Set the Assembly name property on the Application tab of the Solution property page to something more appropriate.

The CLR function performed as advertised after setting everything up correctly. The function was built in both Visual Studio 2005 and 2008 and deployed to the corresponding versions of SQL Server. The setup worked on both SQL Server 2005 and 2008. Here is a sample output.

Utilizing the table, DriveInfo, that was used in the previous installments, the script to populate it would be:

INSERT INTO DriveInfo
SELECT letter   AS Drive
     , total_mb AS DriveSize
     , free_mb  AS FreeSpace 
  FROM drive_info()
 WHERE type = 'Fixed'

Although this solution works well enough, there is some risk with this approach. Based on how an Unsafe assembly is handled with in the SQLOS, there is a risk for memory leaks when an unhandled exception occurs. If a solution can be created that uses alternative SQL Server features rather than an Unsafe CLR assembly, I would choose the alternative. Speaking of alternatives, that where the next installments come in. They will cover using SSIS to get the drive info via the Script Component and WMI.

sp_help_partition

If you are using partitioned tables, this may come in handy. I’ll keep it short and just post the code. Let me know if there are errors or you have improvements.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_help_partition]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_help_partition]
GO

CREATE PROCEDURE dbo.sp_help_partition @OBJECT_NAME sysname = NULL
AS
-------------------------------------------------------------------------------
-- 
-- Proc: sp_help_partition
-- Author: Norman Kelm, Gerasus Software, (C) 2010
--
-- Like sp_help, but for partitioned table partition info.
--
-- Usage:
-- Return a list of table that are partitioned
-- EXECUTE sp_help_partition
--
-- Return the partitioning information for a table
-- EXECUTE sp_help_partition 'Production.TransactionHistory'
--
-- Revision History:
-- 08/06/2010 - 1.0 - Original version
--
-------------------------------------------------------------------------------
DECLARE @schema sysname
      , @TABLE_NAME sysname
      
SELECT @schema = CASE WHEN PARSENAME(@OBJECT_NAME,2) IS NULL THEN 'dbo' ELSE PARSENAME(@OBJECT_NAME,2) END
     , @TABLE_NAME = PARSENAME(@OBJECT_NAME,1)

IF @OBJECT_NAME IS NULL
BEGIN
  -- List all paritioned tables when no paramters
  SELECT DISTINCT SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId')) + '.' + OBJECT_NAME(si.object_id) AS [Name]
    FROM sys.partition_schemes AS ps
   INNER JOIN
         sys.indexes AS si
      ON ps.data_space_id = si.data_space_id
END
ELSE
BEGIN
  IF EXISTS(SELECT 1 
              FROM sys.partition_schemes AS ps
             INNER JOIN
                   sys.indexes AS si
                ON ps.data_space_id = si.data_space_id
               AND OBJECT_ID(@OBJECT_NAME) = si.[object_id])
  BEGIN
  
    -- Borrowed from sp_help
    SELECT [Name]  = o.name
         , [Owner] = USER_NAME(OBJECTPROPERTY(object_id, 'ownerid'))
         , [Schema] = SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId'))
         , [Type]  = substring(v.name,5,31)
         , [Created_datetime] = o.create_date  
     FROM sys.all_objects o
        , master.dbo.spt_values v  
    WHERE o.object_id = OBJECT_ID(@OBJECT_NAME)
      AND o.type = substring(v.name,1,2) collate database_default
      AND v.type = 'O9T'  
 
   SELECT COUNT(*) AS NumberOfPartitions
     FROM sys.partitions  p
    INNER JOIN
          sys.indexes i
       ON p.object_id = i.object_id
      AND p.index_id = i.index_id
    WHERE OBJECT_ID(@OBJECT_NAME) = p.[object_id]  
      AND i.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
       
    SELECT [Scheme] = ps.name 
      FROM sys.partition_schemes AS ps
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
      
    SELECT [Function] = pf.name
         , [Type]     = pf.type_desc
         , [fanout]   = pf.fanout 
         , [boundary_value_on_right] = pf.boundary_value_on_right
         , [create_date]             = pf.create_date
         , [modify_date]             = pf.modify_date
      FROM sys.partition_functions AS pf
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function Parameters] = pf.name
         , [parameter_id] = pp.parameter_id
         , [Type] = st.name
         , pp.max_length
         , pp.precision
         , pp.scale
         , pp.collation_name
      FROM sys.partition_parameters AS pp
     INNER JOIN
           sys.partition_functions AS pf
        ON pf.function_id = pp.function_id
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
     INNER JOIN
           sys.types AS st
        ON pp.system_type_id = st.system_type_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function Range Values] = pf.name
         , prv.boundary_id
         , prv.value         
      FROM sys.partition_range_values AS prv
     INNER JOIN
           sys.partition_parameters AS pp
        ON prv.function_id = pp.function_id
       ANd prv.parameter_id = pp.parameter_id
     INNER JOIN
           sys.partition_functions AS pf
        ON pf.function_id = pp.function_id
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
     INNER JOIN
           sys.types AS st
        ON pp.system_type_id = st.system_type_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
     ORDER BY
           prv.boundary_id
           
     SELECT tc.CONSTRAINT_NAME
          , cc.CHECK_CLAUSE
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
       JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
         ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
        AND tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
      WHERE tc.TABLE_SCHEMA = @schema
        AND tc.TABLE_NAME   = @TABLE_NAME
        AND tc.CONSTRAINT_TYPE = 'CHECK'
   END
   ELSE
   BEGIN
     DECLARE @error NVARCHAR(255)
     SELECT @error = @OBJECT_NAME + ' is not partitioned!!!'
     RAISERROR (@error, 16, 1)
   END
END
GO

Getting Drive Info Part 1, sp_OA

This blog series will cover several different methods to collect drive information from a Windows server that is running SQL Server. I’ve answered several requests in forums, news groups and twitter in the past about how to get the drive size of all of the hard drives on the server that houses a SQL Server instance. These people found the undocumented system stored procedure xp_fixeddrives which returns a result set of the drives on the server along with free space. They were looking for a way to get the drive size as well. This would be a useful addition to xp_fixeddrives to create a disk growth monitoring system. This blog series will also show methods that are specific to SQL Server 2000 as well as methods that utilize the newer features available in versions 2005 and 2008 in future posts. The techniques presented can be modified to get just about any information about the Windows server that houses the SQL Server instance.

Retrieving Drive Size Using sp_OA

The first method discussed is old to say the least as I have been using it since SQL Server 7.0 and there are still plenty of SQL Server 2000 instances running that can make use of this method. It utilizes the sp_OA suite of stored procedures. I have used this method to gather data for an automated SQL Server status report daily for years on SQL Server 7.0, 2000 and 2005. Although there are reports that the sp_OA stored procedures can have memory problems and are a security risk, I have yet to have an issue. They are also set to be deprecated at some point. These points are something that needs to be considered before using this solution. The sp_OA suite of stored procedures allows the use of COM objects from within Transact-SQL. Yes, in order to get the drive size you need to do a little bit of object oriented programming. Much like xp_cmdshell, this suite of store procedures has security risks. Microsoft recognized this and disabled them by default in SQL Server 2005 and 2008. They need to be enabled by running the script (the following is not required on SQL Server 2000):

-- For SQL Server 2005 and 2008 only
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXECUTE sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE

If you are really worried about the security aspects of having these stored procedures enabled continuously, the above script could be made part of the entire drive info script. This would enable the store procedures for only as long as necessary to get the drive information. The Ole Automation Procedures configuration option could be disabled at the end of the drive size script.

In order to get the drive size a new instance of the Windows Scripting Host File System Object has to be created. The GetDrive method has to be called and then the property TotalSize is retrieved. Here is the script for the C: drive.

DECLARE @rs INTEGER
      , @fso INTEGER
      , @drv INTEGER
      , @drivesize VARCHAR(20)

-- Create a new Windows Scripting Host File System Object
EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

-- Get the drive information for the C: drive
IF @rs = 0
  EXEC @rs = sp_OAMethod @fso, 'GetDrive("C:\")', @drv OUTPUT

-- Get the size of this drive
IF @rs = 0
  EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
IF @rs<> 0
  SET @drivesize = '0'

-- Clean up
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso

SELECT CAST( @drivesize AS FLOAT ) / 1024.0 / 1024.0

The next step in the process is to include xp_fixeddrives to get a list of all the drives on a server and iterate through the result set of drives to get the drive size. A table is populated with all the fixed drive info. This data is iterated and the drive size for each drive is collected from the file system object. Here is the script.

DECLARE @rs INTEGER
      , @fso INTEGER
      , @drv INTEGER
      , @drivesize VARCHAR(20)
      , @drive VARCHAR(255)
      , @GetDrive VARCHAR(255)

DECLARE @DriveInfo TABLE(Drive VARCHAR(255), DriveSize FLOAT, FreeSpace FLOAT)

-- Get all the drives and free space in MB
INSERT INTO @DriveInfo(Drive, FreeSpace) EXECUTE xp_fixeddrives
-- Convert Free Space to GB if applicable to the environment
UPDATE @DriveInfo SET FreeSpace = FreeSpace / 1024.

SELECT TOP 1 @drive = Drive FROM @DriveInfo WHERE DriveSize IS NULL

WHILE @drive IS NOT NULL
BEGIN

  -- Create a new Windows Scripting Host File System Object
  EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

  -- Get the drive information for the drive
  SELECT @GetDrive = 'GetDrive("'+ @drive + ':\")'
  IF @rs = 0
    EXEC @rs = sp_OAMethod @fso, @GetDrive, @drv OUTPUT

  -- Get the size of this drive
  IF @rs = 0
    EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
  IF @rs<> 0
    SET @drivesize = '0'

  UPDATE @DriveInfo SET DriveSize = CAST(@drivesize AS FLOAT) / 1024.0 / 1024.0 WHERE Drive = @drive
  -- Convert Drive Size to GB if applicable to the environment
  UPDATE @DriveInfo SET DriveSize = DriveSize / 1024. WHERE Drive = @drive

  SELECT @drive = NULL
  SELECT TOP 1 @drive = Drive FROM @DriveInfo WHERE DriveSize IS NULL

END

SELECT * FROM @DriveInfo

-- Clean up
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso

The data in the @DriveInfo table can be stored permanently in a persisted table for size monitoring and historical reporting.

This concludes the first part of Drive Info series. The sp_OA stored procedures can be used to gather all kinds of information. There are many more properties to look at in the Drive object, go here <add link to Drive docs> for more info. Other COM objects could be instantiated via sp_OA and used to gather server information.

In the next installment of the drive info series, I will show another method supported by SQL Server 2000 and collect the same information using DTS.

SQL This, Not That – Episode 2

When creating a table with a column that will hold the name of a stored procedure that feeds an EXEC command, make sure the data type of this column is sysname. The data type of this column should not be VARCHAR(50) or some other arbitrary data type that should just do the job. This will also be true for columns that hold table and column names. Any column in a user table that holds meta data from a Sql Server system table should match the data type of the source system table column.

SQL This, Not That – Episode 1

I’ve been working on performance tuning a stored procedure that has many optional parameters and is dynamicized by using the always optimizer confusing COALESCE in the WHERE clause (along with other SARG hiding constructs that cause indexes to be ignored).

The stored procedure is called with a date range defined in the parameters @StartDate and @EndDate which are optional.

<code snippet>
, @StartDate DATETIME = NULL
, @EndDate DATETIME = NULL
</code snippet>

The parameters are used in the WHERE clause with a COALESCE.

<code snippet>
WHERE StartDate >= COALESE(@StartDate, StartDate)
AND EndDate <= COALESE(@EndDate, EndDate)
</code snippet>

Using this technique causes the query optimizer to ignore the indexes that have been created on the StartDate and EndDate columns.

One method to fix this problem is initialize the @StartDate and @EndDate with some minimum and maximum dates that make sense for the data being queried as the parameter defaults.

<code snippet>
, @StartDate DATETIME = '01/01/2009'
, @EndDate DATETIME = '01/01/2010'
</code snippet>

Another way is to initialize the parameters in the procedure body for the case where the default dates need to be calculated.

<code snippet>
-- If the date parameters are not supplied,
-- make them 1 month in the past and today
-- to make the query optimizer happy
IF @StartDate IS NULL
SET @StartDate = DATEADD(MONTH, -1, GETDATE())

IF @EndDate IS NULL
SET @EndDate = GETDATE()
</code snippet>

The WHERE clause for either of these cases would change.

<code snippet>
WHERE StartDate >= @StartDate
AND EndDate <= @EndDate
</code snippet>

Now the query optimizer has good SARGs and will do a much better job utilizing those indexes that are using all that space in the database.

Using this technique resulted in a 400% performance improvement for the stored procedure that was being tuned.