SQL Server Data Mining Add ins for Office 2007 to Predict Database Growth Part 2

By:   |   Updated: 2009-07-09   |   Comments (1)   |   Related: More > Database Administration


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

Let us help you stay informed and learn something new each day. Click here to keep informed.

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
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.

Predic1

Enter the "Server Name" and log on credentials for your SQL Server where your data is stored.

Predic2

Choose the database name and clear the "connect to a specific table check box and click the "Finish" button

Predic3

If you see output like below, just click "OK"

Predic4

Click the "Properties" button.

Predic5

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"

Predic6

You may see a warning like this saying that you are changing the connection.  If so, just click "Yes".

Predic8

This will bring you back to "Import Data" menu as shown below and then click "OK".

Predic9

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.

Predic16


(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".

Predic10

Click Next from Forecast Wizard.

Predic11

Make sure you are choosing Sheet1 as shown below and click "Next"

Predic12

Choose the "TotalSizeMB" as input columns and select "InsertDate" as Time stamp.

Predic13

Confirm settings and click "Finish" to get the Browse popup.

Predic14

 


(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.

Predic15


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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips


Article Last Updated: 2009-07-09

Comments For This Article




Thursday, April 15, 2010 - 4:11:14 PM - luis lozano Back To Top (5249)
Good afternoon. I want to do this same process but in vb.net, that statistic could Use?, in advance thank you very much for your cooperation.














get free sql tips
agree to terms