SQLServerCentral Article

Monitoring on a Budget - Part 2



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.


  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.


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.


3 (10)

You rated this post out of 5. Change rating




3 (10)

You rated this post out of 5. Change rating