Introducing Storage Forecasting with SQL Server Machine Learning Services

Steven Wright

Published On: November 6, 2018

Categories: SQL Server 2016, Storage Forecasting, Windows 0

For quite some time now, SentryOne has promoted a methodology of Monitor, Diagnose, and Optimize for SQL Server and the entire Microsoft Data Platform. Our primary driver with this methodology is to enable our customers to get into a proactive, rather than reactive, stance. Being able to identify and resolve issues before they affect the end user gives you the capacity to drive the business forward and dedicate efforts to more strategic initiatives.

With that in mind, SentryOne has been working toward integrating advanced predictive analytics into our products. SentryOne 18.5 includes the first feature set to make use of advanced analytics and machine learning technology—introducing advanced Storage Forecasting.

In its most advanced configuration, SentryOne now applies machine learning algorithms to produce daily usage forecasts for all logical disks on your Windows servers. A forecast is customized for each volume, and each day the system will analyze its previous forecasts to learn how to adjust with each new run. This is done by generating forecasts using multiple algorithms and combining the results into an ensemble forecast. Each day, the system will review how each component forecast performed and weigh it proportionally within the ensemble. Over time, the system learns the unique workload of each volume and fine-tunes its forecasts accordingly.

There are several places within the SentryOne client where you can review and act on this information to prevent outages or perform capacity planning. Let’s start with the Disk Space tab.

The SentryOne Disk Space Tab

On the Disk Space tab, you’ll notice some new information is available. If you click on any logical disk, a new chart appears at the bottom of the view. By default, this chart will provide a history of available free disk space for the volume for the past 180 days. If a forecast is available, you also get the next forecasted 180 days of consumption. In the screenshot below, you can see a green line representing historical data, and a yellow line indicating the forecasted free disk space remaining, surrounded by the hashed 70 percent prediction interval. The prediction interval provides an estimate of where future observations will fall within a 70 percent probability. Note the further into the future you predict, the wider the prediction interval.

In the upper left corner of the chart as well as in the grid pane next to Logical Disk name and size, you’ll find the actual forecasted Resource Exhaustion Date. If the volume is predicted to run out of space within the forecasted time, it will be displayed here. Also notice that the cell will be color coded; orange if the date is within 180 days and red if within 90 days.


Similar disk space information can be found in the Windows Disk Forecast report.

However, there are some important differences. First, the default behavior is to generate a report that shows all volumes in your monitored environment that are forecast to run out of space within the selected time frame. This gives you an immediate view across your entire monitored estate. While the default is to forecast forward 180 days, you’ll see in the Forecast Settings that you can generate forecasts up to three years into the future.

You can also generate a report for all volumes on a particular server. In addition to showing the forecasts for all the volumes on that server, you have the option to include a capacity planning forecast. This can be useful to see beyond the current forecasted exhaustion date to what your longer-term storage needs will be. This helps you make informed decisions about how much more space to allocate to ensure you’ll be meeting your storage needs sufficiently into the future.


So, what if you don’t care to run a report every day to check on your storage needs, but just want to receive an email or other notification if a drive is forecast to run out of space soon? You’re in luck. Forecasted disk exhaustion dates are available within SentryOne Advisory Conditions, too. We'll add them to our downloadable Advisory Condition pack in the future, but for now I'll provide you with access to two Advisory Conditions that you can download and import into your system today after you’ve installed or upgraded to SentryOne 18.5. Both Advisory Conditions can be modified to meet your personal needs.

The first Advisory Condition will be triggered if any volumes are forecast to run out of space in the next 90 days: Windows Volume Forecasted Exhaustion Within 90 Days.

The second Advisory Condition, Windows Volume Exhaustion Date Changed, will be triggered if a forecasted exhaustion date changes by more than a month earlier than previously forecasted. This can help identify situations in which a new database has been deployed or a workload changed, possibly leading to accelerated database growth. Scenarios like this might lead to a much earlier exhaustion date than previously forecast, and this alert can keep you from being caught by surprise by any sudden changes.

Forecast Options

Let’s talk a little more about the options you have for generating these forecasts. I mentioned the work we’ve done using machine learning algorithms to produce forecasts that learn from the unique workload on each of your volumes.

These advanced forecasts use R Services (In-Database) on the SQL Server hosting the SentryOne database to produce those results. This technology was introduced in SQL Server 2016, and it's now available for both Standard and Enterprise editions, and requires some additional configuration. Although this technology is growing in popularity, we understand not everyone will be able to use it on day one. That’s why we offer three different options for generating disk usage forecasts, as shown in the screenshot below.

Upon installation or after upgrading to SentryOne 18.5, all users will have Standard Mode Storage Forecasting enabled by default. This requires no additional configuration or dependency on any other technologies. Our Standard Mode forecasts are produced using an enhanced linear regression algorithm. While not as complex as the machine learning analysis, our Standard Mode analysis still provides all the features I’ve described here. It produces its forecast by analyzing your disk’s usage history, identifying any manual interventions such as someone truncating or deleting a big file to make space, and ensures those events don’t skew your forecast.

Cloud Sync Integration

If you aren't able to implement the requirements for Advanced Mode locally in your environment, but really would like to benefit from the more advanced analysis provided by our machine learning solution, any customers using SentryOne Cloud Sync will get the advanced machine learning forecasts in the web-based cloud interface automatically! Although you won’t get the integrations with the on-prem client, you’ll find the new Windows Disk Forecasting report, featuring those more advanced forecasts, available in your cloud account. From there you can set up a daily subscription to email you with a report on any volumes predicted to run out of space.

As I stated at the beginning of this post, this is just the beginning of SentryOne’s integration with advanced analytics capabilities. Storage forecasting was an obvious opportunity, but we have many more enhancements in store for future releases.

So, if you haven’t already, install or upgrade to SentryOne 18.5 today and check out this powerful new feature set. With three different ways to generate storage utilization forecasts, I’m sure you’ll find a method that best meets your needs.

Try out the new advanced Storage Forecasting feature for yourself: Download a free, 15-day trial of SentryOne now.

SentryOne Storage Forecasting

Steve (@SQL_Steve) is a fourteen-year veteran of SentryOne, and has held roles in Solutions Engineering, Quality Assurance, Product Management, and Advanced Analytics prior to assuming his current role. He has almost twenty years' experience working with SQL Server, and holds numerous professional certifications including the Microsoft Professional Program, Data Science Certification. His current responsibilities are focused on ensuring everyone throughout the SentryOne family is educated on our customers, their needs, and the solutions we provide that allow us to improve the lives of Microsoft Data Professionals and their customers everywhere.


Fighting Downtime and Slowdowns