LinkedIn | FaceBook | Twitter
One of the most important factors in a database is the I/O subsystem. Whether you’re using a Storage Area Network (SAN), Just a bunch of disks (JBOD) or any other storage medium, you’re interested in two things: information about the storage system, and information about the storage transfer rates. You can find some of the transfer information in a few of the “Performance” reports I explained earlier, and we’ll see more in later reports.
This report, and the next few that follow, deal with the first question. You’ll get valuable information about how the data is laid out on the drive a some information about how much space is left, at least in some cases.
It’s important to understand how SQL Server uses files on the drive, especially the terminology. I won’t cover all that here, so if you’re not familiar with the file system in SQL Server, I suggest you check here and then refer to this report.
There are three main sections to this report: two tables and a couple of charts. Let’s take a quick look at each:
The first section is followed by two pie charts, which show more detail on the allocation within the data and transaction logs. For the data, the space is split between data, indexes, space that is in a contiguous block but not allocated to the SQL Server database, and the space allocated to the SQL Server database but not yet filled with anything.
Following those graphs is another table, showing the same space information, this time by the filegroups assigned to the database.
Filegroups might be new to you, but they are an integral part of the SQL Server storage story. Filegroups are simply a logical name for a physical location on the storage system. Every database has at least the PRIMARY filegroup, which is where all objects you create are stored by default. You can create more filegroups, placing them on any locally visible drive. No, you can’t store SQL Server files on a share, but you can store them on any device physically attached to SQL Server, whether that’s a SAN, NAS, JBOD and so on.
By creating new filegroups, you can spread out the data, indexes and so on. You do this for performance, security, maintenance and so on. You can read more about filegroups here. Here are the columns for this section of the report:
We’re in the first of the database-level reports in this series of the Standard Reports that ship with
It would be nice to know how to get the summary for all databases on a SQL server.