Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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

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


Last Updated: 2009-07-09


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
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, April 15, 2010 - 4:11:14 PM - luis lozano Back To Top
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.

Learn more about SQL Server tools