SQLFacts dashboards

Challenge

The ugly truth when working with SQL Server is that there usually isn’t much money left over for monitoring once you pay for the license.  Most DBAs accept this reality by implementing a custom data collection.  But what happens when all you want is to visualize that data quickly? 

Dashboard walk-through

With less than an hour's worth of work, I was able to install SQLFacts and create four dashboards.

The setup

First, I read through then imported the needed T-SQL code (never import stray SQL code!). Then I fired up my MSMS, put my data in the master, and it created my file history, monitor, wait counts, etc. I pulled it up and everything was there.

If I’d had a special query or was looking for special data, SQLFacts will let you add it to the collection. For example, you may have a specific application on a weird server and you need to collect certain values, this will do it. Then, SquaredUp will let you visualize that and publish it (for free, even!). Some of the SquaredUp tiles even just call the stored procedures on demand.

Then, in SquaredUp, when you add a new tile you simply need to add a new provider, if it’s not already listed. It’ll take you to the integrations list. Choose SQLFacts. Then add in the connection string.

If you have multiple servers, just clone the dashboard, create a new data source, change which one it’s pointing too, and you have a new dashboard.

Here are the 4 dashboards I created:

The SQLFacts Master dashboard

This dashboard details how to get started using SQLFacts.  It also includes an example Scalar tile that will change state based on a clever T-SQL statement with SQLFacts.  I also added the Page Life Expectancy (of questionable value these days), but neat to see.  And several server metrics that SQLFacts collects, like how your server is running so you don’t have to go back and forth with your Windows team.

Wait Status dashboard

The Wait Status dashboard includes a lovely table of Observed Wait Summaries and two graphs of Wait History (in percentage and seconds).

File History dashboard

File History is a graph heavy dashboard that shows all of the databases in your SQL Server Instance.  You can see vital metrics like storage, which is huge for SQL. Data from this dashboard is so important, you could display this on a NOC or mission control dashboard easily.

Backups dashboard

SQL Server keeps track of its backup status, but even the most diligent Admins don’t check it daily.  This dashboard brings the backup data goodness to one place to share with the world so you always know whose backup is the most recent.  Share this with your App Admins, and they will be glad to check this every day for you!