Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Monitoring on a Budget - Part 2

By Bert Carles,

Introduction

As a follow up to my first article "Monitoring on a Budget", here's how we present the fact data to management using Microsoft Excel. This has little to do with SQL Server and much to do with Excel but is certainly useful in quickly creating graphs from SQL Server data (or any external data). There are many other ways to present this data. I'd welcome your ideas and/or examples.

Technique

  1. Open Excel (you can add this data to an existing sheet or use a blank one)
  2. Click Data on the menu bar.

     

  3. Select PivotTable and PivotChart Report...
  4. Click the radio button for External data source and PivotChart report (with PivotTable report)
  5. Click Next
  6. Click Get Data...

     

  7. Highlight <New Data Source> and click OK

     

  8. Enter a NEW source name on line 1.
  9. Select SQL Server on line 2.

     

  10. Click Connect... on line 3.
  11. Enter a server name and the appropriate connection credentials. Since I'm always the person generating these charts, I use a trusted connection. If many people will be viewing your charts, you may want to use a native SQL Server login.
  12. Click Options >> and select the database that host's your statistics.
  13. Click OK

     

  14. Select one of the views defined in the original article
  15. Click OK

     

  16. Click OK
  17. Click > to select all columns in the view
  18. Click Next

     

  19. Click Next (I'm not sure why you'd want to, but you can add your own filters here if you'd like)
  20. Sort by: SrvName Ascending, Then by: SampDate Ascending
  21. Click Next

     

  22. Select the radio button for Return Data to Microsoft Office Excel
  23. Click Finish

     

  24. Click Next

     

  25. Select the radio button for Existing worksheet
  26. Select a cell which will be the starting point for inserting data into the sheet.
  27. Click Finish

     

     

  28. Highlight then drag SrvName from PivotTable Field List to Drop Page Fields Here

     

  29. Highlight then drag SampDate from PivotTable Field List to Drop Category Fields Here
  30. Highlight then drag any or all (one at a time) of the FACT columns to Drop Data Items Here. We created separate charts for each fact (repeating each step 2-30 above for each fact) for a cleaner presentation.

     

  31. You can double click any of the labels and tags to enhance the appearance. We replaced all of the column and tab names with meaningful, unabbreviated words and added commas to the numeric scale.

     

  32. Finally, you need to save the spread sheet.

     

Show me the money

Obviously, we have so much data that the dates across the bottom are no longer readable. If you pass your cursor across a point on the graph, it will pop up with the date and the value for that date.

You'll notice drop down boxes so you can select a specific server and/or dates. You'll need to click the exclamation point (!) to refresh the data if the database has changed since your last viewing. After a period of time, refreshing the data will take longer.

Conclusion

As I stated in the beginning, there are many ways to present this data. This technique is relatively simple and inexpensive. It is easy to view, allows access by multiple users, and provides the ability to filter the data. Immediately, you will be able to observe spikes/dips and over time, trends in performance metrics.

Total article views: 5435 | Views in the last 30 days: 27
 
Related Articles
FORUM

Open microsoft outlook by clicking field on SSRS report

Is it possible to open microsft outlook by clicking on the email address field on a report?

FORUM

Linked Server for Excel File

Excel Linked Server

FORUM

Concatenate existing fields SQL server

concatenate existing fields SQL server

FORUM

Problem with EXCEL

EXCEL

FORUM

import data from excel file to sql server database

import data from excel file to sql server database

Tags
excel    
monitoring    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones