Introduction
In part 3 of this series on mining performance data from the SQL Sentry database, we learned about several repository tables that store information about the storage schema for a server. This time, we’ll learn how to query for information pertaining specifically to SQL Server files.
What I want to end up with is a query that will tell me about any transaction logs with more than 100 virtual log files (VLFs). Note that I’m not saying that 100 is any sort of hard threshold. That really depends on a few things. For this post, I am just using that number as an example for my query.
We can, of course, get lots of other information about log and data files besides the VLF count. I’ll be providing table definitions and descriptions, just as in the earlier installments of this series, but the final query I provide will focus on VLF count.
SQL FILE GROUP
As we know, files in SQL Server are organized into groups (data files anyway). This table stores information at the connection (SQL Server instance) level for file groups:
dbo.PerformanceAnalysisSqlFileGroup
GroupName is self explanatory, while a couple of other columns could use some explanation. If you are familiar with the sysfilegroups or sys.filegroups views in SQL Server, you may recognize some of this data.
The Status column can be used to determine the default file group (16) or read only (8).
The AllocPolicy column is really only there for compatibility, so I wouldn’t plan on trying to use that for anything.
EventSourceConnectionID is important, because that is our foreign key back to the connections table.
SQL FILE
The file level table contains lots of information for database files.
dbo.PerformanceAnalysisSqlFile
As in the FileGroup table, we have a foreign key back to the EventSourceConnection table. This can be used in conjunction with the DatabaseID and GroupID to join to the FileGroup table.
Size and MaxSize represent the current and maximum file size, respectively, in 8k pages. the value of –1 for MaxSize indicates that the file can grow until the disk is full. A value of 268435456 for MaxSize indicates that the file will grow until it reaches 2TB.
Growth and Status work together in a way. Growth can either be percentage based, or a set number of pages, and you can use the Status column to tell which it is. If Status contains 0x100000, then growth is percentage based. If not, then it is a specific size in 8k pages. We’ve abstracted this out into another column for performance though, which is the IsPercentGrowth column, so I recommend using that to make this determination.
Don’t worry about the Perf column. It is another column that is really just there for compatibility.
Name is the logical name of the file, while FileName is actually the physical path to the file.
FileTotalSize is the size of the file on disk, in KB.
The MaximumInboundRate and MaximumOutboundRate columns are for internal use by SQL Sentry, so don’t worry too much about those.
FileUsedSize is the the used space in KB. Use this along with FileTotalSize to determine free space.
TotalVlfCount, ActiveVlfCount, AverageVlfFileSize, MaximumVlfFileSize and MinimumVlfFileSize are all collected or calculated by the SQL Sentry monitoring service. These are, of course, only available for transaction log files, so data files will always show 0 for these (along with any other VLF-related columns).
LastBackupTime and LastBackupType are useful for letting you know backups are (or are not) happening.
The Type column is a value indicating whether the file is a data file or log file. Data files are 0, while log files are 1.
Finally, LastModifiedUtc is just the UTC time of the last change for that file.
PUTTING IT ALL TOGETHER
Now, given these two tables, along with some information from earlier in the series on basic server information, I have enough to build my high VLF query.
Note that I’m not using the FileGroup table here, since transaction logs don’t have a file group.
And my results:
I’m sure you can think of lots of other useful things to do with this file data, so feel free to use it as you like. It is, after all, your data. 🙂
NEXT TIME
In Part 5, we’re going to start looking into the schema and various uses for “performance counters”; the reason I put that in quotes will become clear soon enough. For now, I hope you can get some good use out of what we’ve covered so far.
Click the links below to read the previous or next post in this series:
