A member of the SentryOne Product Advisory Council (PAC) was looking for a SentryOne database query that would return the growth of databases over time. I hadn’t seen anyone provide that query yet, so I thought I would build something and share it. This blog post outlines the loose process I followed and teaches you about the SentryOne database along the way.
SQL Servers, Databases, and Files
I decided to use the aggregate size of log and data files grouped by server and database. There are other ways to look at size, but this approach will show me the growth trend I want, and storage concepts are pretty much universally understood.
That means I needed to tie performance counter data on log and data file size back to the related server and database. The following tables provided the data entities and attributes I needed:
These are the high-level entities, which are represented by tables in the database, to start with:
- Device—One row for every target registered in SQL Sentry
- EventSourceConnection—Connection details for SQL Server instances and SSAS instances
- PerformanceAnalysisSqlDatabase—One row for each collected database (could be none but that’s not likely for an actively monitored SQL Server)
- PerformanceAnalysisSqlFile—One row for each data file or transaction log
Performance Counters
I sourced the counter values representing data and log file size from the PerformanceAnalysisData[Rollup#] tables. I needed to know which counters to use first. A simple query against performance counter categories searching for categories related to file size got me there.
The results, shown below, indicated I had a few categories to inspect.
Categories 24 and 25 were the ones I was looking for. They deal with data file and transaction log size, respectively. The following query provided a quick review of the counters for those two categories:
The results of the above query revealed that the counter IDs I was looking for were 251 and 253. They represent Data File Total Size MB and Log File Total Size MB. Although the counters related to Used Size are helpful in several ways, I didn’t need them at that moment.
I used the Rollup# table for 30-minute averages. According to the PerformanceAnalysisRollupLevel table, that is Rollup6. Using the rollup tables also prevents you from needing to build in logic to choose raw data versus aggregate data. There are many cases in which I might want to use the raw data, but for a long-term growth trend, the 30-minute rollup is fine.
Join on InstanceName
I cheated and looked at the SQL Sentry source code to learn what the instance name is composed of for these counter instances. It turned out to be EventSourceConnectionID, DatabaseID, FileID. These are in the tables I listed earlier, but I still needed to merge them into a matching format with the InstanceName.
I don’t “love” this join because it does introduce complexity that I would rather not have. However, I believe it works better than joining to an additional two to three tables.
Final Query
I then added variables for start and end dates. These variables are important because we can take advantage of indexes on the timestamp field in the PerformanceAnalysisData tables.
You can also see the use of functions in the SentryOne database for converting between dates and our timestamp format. If you’re going to be using SQL Sentry performance data outside of SQL Sentry, you’ll want to become familiar with these functions. The timestamp is proprietary, and these functions remove the need for others to understand the timestamp composition.
Summary
Here’s a chart I made from querying my SentryOne database for the first week in July 2020. It’s boring, it’s 2.4GB, and SQL Sentry keeps it that way unless something happens. I’m sure your environment is more interesting, but mine is doing exactly what I want it to do for now.
You can use this query as-is to perform some self-service analysis on storage trends for your databases. You can also use it as a jumping off point for automation, dashboarding, or reporting.
I encourage you to review the data, relationships, and particulars I’ve discussed here. With this information, you can expand the scope beyond these storage-related values. You could trend any counter or set of counters you might be interested in. There are hundreds of performance counters available in SQL Sentry, and you’re only moments away from discovering what you can learn about your data estate by pulling the data out of the SentryOne database and using it to tell your own story.
