Mining Performance Data from SQL Sentry (Part 3)

Introduction

In  Mining Performance Data from SQL Sentry (Part 2)  we took a quick look at a table that lets us query some information about the Operating System from the SQL Sentry repository database. In this post, we're going to kick things up a notch, and go over some tables that will give us a picture of our storage layout.

The disk system for a Windows server is a fairly complicated hierarchy of objects, so although I promised to only introduce 1 or 2 tables at a time, it would be very difficult to split this topic into separate posts. As a result, we're going to be covering several tables in this post.

Controller Type

Disk schema starts with different types of controllers. The names of these are kept in a small type table. While small, it is still important for our purposes here. For example, it would probably be useful for you to know the difference between a set of disks on a SCSI controller vs. those on a USB controller.

PerformanceAnalysisDeviceDiskControllerType

Rows in this table are currently static, and list out as follows:

Controller

The disk controller table lists out all the disk controllers discovered via WMI and/or VDS queries. There are a few bits of information that may be useful, like the manufacturer and inbound and outbound maximum rates, but remember to take these values for what they are, which is what is reported back from the WMI repository from their providers. The accuracy of the information is tied to how much care and feeding those WMI providers were given by the manufacturer.

PerformanceAnalysisDeviceDiskController

Note the foreign key back to the controller type table. This is important for our queries, so that we can narrow down the working set by entire types of controllers.

Disk Drive

The disk drive table represents the "physical" disk. We shouldn't take the term "physical" literally, as we're really just talking about a unit of space carved out for the server to use. It could be a physical disk, but it could also be a VHD, Mount Point, LUN carved out from a SAN or whatever else you can think of.

PerformanceAnalysisDeviceDiskDrive

We're finally starting to get a familiar piece of data, as this is where we can see the size of the disk. This one isn't necessarily the one we want to use for a query, for reason that we'll see in a bit, but it's still important to know the actual capacity of the disk itself.

We can't quite use the name yet, as by querying the table, you'll see the name is basically the DeviceID from WMI:

We're getting closer though!

Partition

Whenever we get a nice chunk of space, we can carve it up into partitions, right? This table lists those partitions, simple as that.

PerformanceAnalysisDeviceDiskPartition

Again, here size is somewhat useful, but the name isn't good for reporting out just yet.

We store these items, as they are used internally for displaying the various screens in SQL Sentry, but we probably won't see the "name" most folks are familiar with until the next table.

Logical Disk

At last, the part we've been waiting for. The location of the volume names we're familiar with along with their size, free space, status and a few other goodies.

PerformanceAnalysisDeviceLogicalDisk

After everything else, this is where I'm trying to get to so that I can build a simple query that reports on low free space globally by server.

There is one issue though. This maps back to disk, but not to partition. If I want to include partition data with this, then I need some sort of mapping.

Why is this? Think for a moment, and you probably already know the answer. We can have volumes that span multiple partitions. That's why partition to logical disk needs a mapping table.

Luckily, the last table we're going to look at today, does just that.

Logical Disk to Partition Mapping

As I stated above, logical disks can span multiple partitions, so we need a mapping table for this in order to be able to create an accurate picture of the disk system.

Once in a blue moon, we'll find that the system is reporting incorrect information from WMI about what logical disks map to what partitions, and this mapping table will be missing some rows. If that happens, you'll not be able to see these logical disks in the Disk Activity or Disk Space views of Performance Advisor. As such, this is one of the tables our support engineers look to if someone reports that something is missing in one of those views.

In addition, letter drives (C:, E:, Z:) will have a NULL PerformanceAnalysisDeviceDiskDriveID in the PerformanceAnalysisDeviceLogicalDisk table. This is because they map to partitions directly. Mount points have a value for PerformanceAnalysisDeviceDiskDriveID so we can easily tell what drive they are mounted on. Because of this, we need to join to this mapping table to make sure we're getting all of our logical disks in a query.

PerformanceAnalysisDeviceLogicalDiskToPartition

Note that this is a standard "many to many" mapping type table with its own surrogate key, and foreign keys back to the logical disk and partition tables.

Putting it all Together

Using the tables we've discussed here, along with some information from Mining Performance Data from SQL Sentry (Part 1)  of this series, we might build the following query that shows us all volumes on every monitored server that have less than 10% free space:

SELECT *
FROM (
     SELECT d.FullyQualifiedDomainName
          , typ.Name AS ControllerType
          , ctrl.Name AS ControllerName
          , logicalDisk.Name AS LogicalDiskName
          , logicaldisk.FileSystem
          , logicaldisk.Size / 1048576 AS SizeMB
          , logicaldisk.FreeSpace / 1048576 AS FreeSpaceMB
          , (logicaldisk.FreeSpace * 100) / logicaldisk.Size AS PctFreeSpace
     FROM dbo.Device d
     INNER JOIN dbo.PerformanceAnalysisDeviceDiskController ctrl
          ON ctrl.DeviceID = d.ID
     INNER JOIN dbo.PerformanceAnalysisDeviceDiskControllerType typ
          ON typ.ID = ctrl.PerformanceAnalysisDeviceDiskControllerType
     INNER JOIN dbo.PerformanceAnalysisDeviceDiskDrive diskdrive
          ON diskdrive.PerformanceAnalysisDeviceDiskControllerID = ctrl.ID
     INNER JOIN dbo.PerformanceAnalysisDeviceDiskPartition part
          ON part.PerformanceAnalysisDiskDriveID = diskdrive.ID
     INNER JOIN dbo.PerformanceAnalysisDeviceLogicalDiskToPartition disktopart
          ON disktopart.PerformanceAnalysisDeviceDiskPartitionID = part.ID
     INNER JOIN dbo.PerformanceAnalysisDeviceLogicalDisk logicaldisk
          ON logicaldisk.ID = disktopart.PerformanceAnalysisDeviceLogicalDiskID
     
     UNION
     
     SELECT d.FullyQualifiedDomainName
          , typ.Name AS ControllerType
          , ctrl.Name AS ControllerName
          , logicalDisk.Name AS LogicalDiskName
          , logicaldisk.FileSystem
          , logicaldisk.Size / 1048576 AS SizeMB
          , logicaldisk.FreeSpace / 1048576 AS FreeSpaceMB
          , (logicaldisk.FreeSpace * 100) / logicaldisk.Size AS PctFreeSpace
     FROM dbo.Device d
     INNER JOIN dbo.PerformanceAnalysisDeviceDiskController ctrl
          ON ctrl.DeviceID = d.ID
     INNER JOIN dbo.PerformanceAnalysisDeviceDiskControllerType typ
          ON typ.ID = ctrl.PerformanceAnalysisDeviceDiskControllerType
     INNER JOIN dbo.PerformanceAnalysisDeviceDiskDrive diskdrive
          ON diskdrive.PerformanceAnalysisDeviceDiskControllerID = ctrl.ID
     INNER JOIN dbo.PerformanceAnalysisDeviceLogicalDisk logicaldisk
          ON logicaldisk.PerformanceAnalysisDeviceDiskDriveID = diskdrive.ID
     ) disks
WHERE (FreeSpaceMB * 100) / SizeMB <= 10
ORDER BY (FreeSpaceMB * 100) / SizeMB
     , FullyQualifiedDomainName;

Note that we did need to have two queries with the union because of the difference in how mount points are stored vs. regular volumes with drive letters.

On a test server at SQL Sentry HQ, I can see results like this:

Sample results from a test server

Looks like the QA team is chewing through storage like Pac-Man at a lemon drop factory! For shame…

Next Time

In Part 4, we will build on what we've learned here, and take a look at data and log file details for SQL Server instances that are monitored with Performance Advisor.

Mining Performance Data Series

• Part 1: Mining Performance Data from SQL Sentry (Part 1) 
• Part 2: Mining Performance Data from SQL Sentry (Part 2) 
• Part 3: Mining Performance Data from SQL Sentry (Part 3) ← You are here.
• Part 4: Mining Performance Data from SQL Sentry (Part 4) 
• Part 5: Mining Performance Data from SQL Sentry (Part 5) 
• Part 6: Mining Performance Data from SQL Sentry (Part 6)