By: Kun Lee | Comments (1) | Related: More > Database Administration
Problem
In my previous article, "Using SQL Server Data Mining Add-Ins for Office 2007 to Predict Database Growth Trends", I showed how I used the Analyze option within Excel and the Data Mining Tools for SQL Server to predict database growth. Now, I want to show you another easier way to pull information to help predict database growth again using the Data Mining Tools.
Solution
In this tip we will be using the "Get External Data" feature within Excel and the Data Mining menu to pull real time data.
All the pre-requisites are the same as in my previous article, so please refer to that article to find out what tools you need to have installed.
(1) T-SQL
Here is a little tweak that I did from SQL to only collect database size information for every Monday instead of daily by filtering the where clause like this way. I recommend making this a stored procedure for better security.
-- Get database size for every Monday. SELECT TotalSizeMB, CAST(CONVERT(CHAR(10),InsertDate,101) AS DATETIME) InsertDate FROM dbo.DatabaseSizeTrend WHERE (DATEPART (dw, InsertDate)) = 2 -- Filter for every Monday GO |
(2) Setup Excel to Load Data
Open Excel 2007 and choose the "Data" menu and click "Get External Data" then "From Other Sources" then "From SQL Server" as shown below.
Enter the "Server Name" and log on credentials for your SQL Server where your data is stored.
Choose the database name and clear the "connect to a specific table check box and click the "Finish" button
If you see output like below, just click "OK"
Click the "Properties" button.
Choose "Definition" (see 1), change the "Command type" to "SQL" (see 2) and type the T-SQL that you prepared in Step 1 above (see 3) as you see below and then click "OK"
You may see a warning like this saying that you are changing the connection. If so, just click "Yes".
This will bring you back to "Import Data" menu as shown below and then click "OK".
Once you have the data, save this Excel spreadsheet somewhere on one of your File Servers that you can share with others. The only thing that you have to do is to give the users permission to run the query. This is why I suggested using a stored procedure in Step 1 to help simplify the permissions that are needed to access the data.
(3) Setup Forecast by Using Data Mining.
Now you have the data and it is already formatted as a table since it pulls data directly from SQL Server. In the previous article, I used the "Analyze" menu, but this time we will use the "Data Mining" menu and click "Forecast".
Click Next from Forecast Wizard.
Make sure you are choosing Sheet1 as shown below and click "Next"
Choose the "TotalSizeMB" as input columns and select "InsertDate" as Time stamp.
Confirm settings and click "Finish" to get the Browse popup.
(4) Using Browse Tool
The input date time we are using for this example is for every Monday, when you increase the "prediction step" by 1 (See 1), that means you will see 1 week later of your data. So, in this example I increased the prediction step to 50 and you can see the chart dynamically generated 50 weeks into the future.
You can move your mouse over the graph (see 2) and it will show the detailed information. In this case, we can see the DB size on 5/10/2010.
Now the next time you want to see future predictions based on the data you are collecting you just need to open the Excel and click data to get the latest data.
Next Steps
- Use this feature to help predict the CPU usage and other PerfMon data
- Use Analyze key influencers to see what is the biggest performance impact
- There are a lot other features that come with the Data Mining tool for DBA use, so take the time to check them out.
- Read part 1 of this series
About the author
Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
