INTRODUCTION
Last time 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:
| ID | Name |
|---|---|
| 1 | SCSI |
| 2 | IDE |
| 3 | USB |
| 4 | IEEE1394 |
| 5 | PCI-E |
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:
| Name |
|---|
| \\.\PHYSICALDRIVE0 |
| \\.\PHYSICALDRIVE1 |
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.
| Name |
|---|
| Disk #0, Partition #0 |
| Disk #0, Partition #1 |
| Disk #1, Partition #0 |
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 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:
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.
Click the links below to read the previous or next post in this series:
