Using SSMS as a Dashboard

The other day I was asked by a friend about a recommendation on a “Dashboard” for SQL Server 2005. She wanted to be able to see all the pertinent information about her server with just a few clicks. She asked me what I had used in the past, and wanted to know what she could get that was cheap, fast and simple. I told her that I had designed a few VB applications against SQL Server 2000 and when 2005 came out I had a couple of web pages I made, but then I told her the fastest, simplest and cheapest way was probably just to use Management Studio. She looked a little surprised.


Can you really use SQL Server Management Studio as a one-stop shop to watch your SQL Server Systems? Yes! Here’s how I do it:


First, I create a shortcut in Windows that opens a file in SQL Server Management Studio, like this:

Sqlwb.exe status.sql


The “status.sql” file is a file I run that reads the SQL Logs, checks the service pack, and gets other meta-data I care about. Over the years I’ve changed that file to now read from some tables I’ve created which is filled from a job every five minutes using a job. Those tables contain certain results of the logs, alerts, selected performance counters and locking info, things like that.


Next, I walk through a few of the reports I’ve been describing in this series. I start with the dashboard report and then normally check the transaction statistics reports.


Finally, I’ve created some reports of my own that I’m slowly transitioning to from my status.sql file. I’ve got that custom report pared down to just what I need to see each day, and I’m improving it all the time as I get better with Reporting Services. After I check my script and reports, I use the log viewer, activity monitor and the rest of the tools to investigate any issues I've found.


So you actually can see your server’s status using just the built-in tools and a few queries. But will it scale for multiple servers? Well, yes. Right now I do that with linked servers and openquery statements, and in 2008 you’ll see we’ve added even more multiple-server enhancements, as well as a great new set of dashboard reports that work with the performance gathering system we’re building in. There are also other options, such as using a “master” server to hold all the tables I mentioned earlier and using that one to monitor the others. We’ve enhanced the speed in SSMS for 2008, so you’ll be able to query faster on more servers.


The point is that you should always investigate what you have before you think about what you should buy. Not that other solutions aren’t good ones, but you may already have what you need.