Monitoring your SQL

I’ve seen situations where monitoring SQL falls into a kind of black hole. The DBA’s feel (somehow) that this is beneath their notice; they’re focused on strategic issues or otherwise busy – maybe riding horses and pointing majestically off into the distance. And developers feel that their responsibility is to deliver good code; monitoring the health of the SQL boxes isn’t something they have time (or the desire) to do. As a result, the application’s performance and reliability takes a hit.

From my perspective, I’d like a little insurance as a developer so I can definitively point to the backend environment as the cause of a performance bottleneck. And it’d definitely be helpful if I was collecting execution times so when we make sproc changes we can determine if they’re harmful to the health of the system. As a monitoring checklist on your SQL boxes, you should be checking the following on a daily basis:

  1. Are your backups working?
  2. Have you set up automated restores and are you monitoring success/fails?
  3. Are you checking error logs?
  4. Are you checking for failed jobs?
  5. Is there sufficient free disk space for production databases?
  6. Did you run integrity checks and check index health? This continually requires finetuning.
  7. Did you check failed login attempts for security?

Let’s face it – you’re NOT going to log onto QA or PROD every day and run SQL scripts manually. Other interests beckon! But, it would be great to log this stuff.

So, check this out: Admin creation script

Use this SQL here to set up automatic monitoring. Once you do this, you’ll have a set of tables collecting data over time and a daily report that will show you all the good stuff for the day. It wouldn’t take much work to drop this into a ListView on a website to make this even easier to check. It also, incidentally, will fix any indexes that are excessively fragmented and update statistics.

To implement it, just create a database called “Admin”. Run the attached creation script, and then create a set of views in any target databases to collect fragmentation data. (There’s probably a more elegant way to implement this last piece; I kinda banged this out over a few hours and then moved on.) Create a set of jobs then with the following schedules:

  1. A Daily Report job – run dbo.Daily_Data_Colleciton
  2. A Daily Maintenance job – run dbo.Daily_Database_Maintenance
  3. A Weekly Maintenance job – run dbo.Weekly_Database_Maintenance

#2 and #3 could be combined if desired, I imagine as a weekly run on a Saturday. Right here, combined with a database backup (and an automated restore) you’re well ahead of the game.

Didn’t take much work, and I found pretty quickly that 1) we needed to add a ton more indexing, especially as our table would start to bloat – and that the Optimize for Ad Hoc Workloads flag was (incorrectly) set to 0. And our SQL Server Agent was set to manual startup. no Bueno!!


  • I mucked around a little with using WMI to view not just SQL’s portion of CPU stress, but the entire CPU demands on the production SQL box. However, on most environments, SQL is going to be the bulk of the stress. It didn’t seem worth it – and having to turn on CLR on the SQL box isn’t a great solution. The DVM’s that come with SQL Server are fine. If you wanted to do this, it’s easy and runs fairly fast – just copy the DBStats.Database.CLRFunctions.dll from the 9781439247708_CH06 code download from this source (Chap06 folder) and copy it out to the same Data folder that you use for all your system databases (for me this was H:\MSSQL11_0.MSSQLSERVER\MSSQL\DATA ).
  • There’s a great free reindexing/statistics tool that I want to look into:
  • Note that the fragmentation script from the Apress book I referenced above was fubar – went with a riff on this article instead.
  • And I can’t recommend highly enough the DMV’s and SQL found at This guy is a genius and if you want to start digging deeply into why your server is lagging, this is a great place to begin. I also really like the section on looking for bad NC indexes or missing indexes.
  • Future refinements: Add Backup information to the daily job.

Some explanatory text of the embedded SQL:

  • usp_RunCheckdb
    • this runs and if the db size is less than 50 GB it runs DBCC CHECKDB(DBNAME) WITH NO_INFOMSGS
  • usp_Collect_login_trace
    • See the LoginAuditTrace table – has records tracking the last login.
  • updateStat_onAllDbs
    • runs exec UpdateStat_OnAllDbs – which basically fires off execute sp_updatestats on every database that’s read_write, online, and multi-User and not in master/model/tempdb
  • usp_IndexMaintenance – runs indexes that are excessively fragmented. A nifty set of SQL here!
  • and an index fragmentation job with two steps:
    • GetLargeDbs (inserts into a table Auto_Exception_Lst a set of databases >50GB
    • usp_CollectFragmentation
  • And last but not least –
  • Other sprocs:
    • GetSysConfigDetails – checks the server parallelism and AWE. Run rarely.
    • sp_GetBackupStatus – old, replaced by other jobs to check backup – usp_BackupDB_Info
    • usp_Alter_Index
    • usp_BackupDB_Info – shows most recent backup for each db.
    • usp_cluster_loadbalance_checker – checks if load balancing is running normally on SQL Server clusters.
    • usp_CollectFragmentation – collect fragmentation information (a good alternative)
    • usp_fileconfig – shows the drive location and size of each logical filename in SQL.
    • usp_GetLastRunCheckdb – shows the last run of CheckDB for each db. Shouldn’t be necessary with usp_RunCheckDb above.
    • usp_IndexDefrag – runs a rebuild/reorganize on fragmented indexes. Superceded by usp_IndexMaintenance.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.