The best SQL monitoring practices
(even if Santa brought you all third party software licenses you asked for)
I don’t care how many third party software monitoring you got going on. Some alerts work best as SQL native tools.
SQL DBA are responsible to many SQL Servers. Especially today. Days, when you had one important server are over, buddy.
So it is important to deploy some custom SQL monitoring to cover the basics.
Because there is always some server, you did not purchase the monitoring license for. Or some other weird scenario.
I tend to deploy few custom SQL monitoring jobs on pretty much any SQL Server we maintain.
SQL Server custom monitoring jobs we deploy:
- SQL Agent restart notification – when SQL Agent restarts, send an email out.
- SQL Engine restart notification – when SQL Engine service restarts, send an email out. Very useful to capture random reboots or someone messing with the server, which they forgot to tell me about.
- HDD space monitoring – this is configurable HDD space monitoring, when threshold is hit, it emails out.
- DDL events monitoring – when any sql objects (tables, stored procedures, views) are modified, created or deleted, keep a log all of those changes. Very damn useful!!
- Snapshotting of currently running T-SQL commands – capture what is happening on server every few minutes, capture that into a table, delete not relevant and old data out, so I can go back and answer a question, of why server was slow at 3PM on Tue last week. Very useful for low end performance monitoring.
- SQLAgent job change monitoring – capture and log all job and job step changes into a log table.
- SQL Config Change Track and Notifier – capture everything I can like SQL instance configuration changes and send out an email once per day, so I can know if someone is messing with server settings that forgot to tell me about. Like reducing RAM on AWS EC2 instance. Or something similar
- Cache single plan cleanup – if single use plans are a problem, this will run and get delete useless plans from cache making more RAM available to the rest of the server. If problem doesn’t exist on your server, then this won’t do any harm.
- DB and table growth monitoring – periodically log how database sizes, how big tables are, so after a while I can easily estimate what growth patterns looking like.
- Index usage and missing index monitoring – Capture this data and store in the tables. Daily. So if there is any doubt that index was created to address month end reporting or we can quickly find out.
- Failed login attempt reports – daily email me a report with number of failed attempts and IPs where it came from. Log that to the table.
- Capture Alerts from SQL ErrorLog. This is early warning system. Think of it like a red light that starts blinking and we about to have an outage. Very useful for preventing downtime.
- I really don’t like stopping at #13, but what da hell. This is all I can think of right now.
So there you have it. Few automated jobs that each SQL Server probably needs to have.
And this is how you avoid manual SQL Server monitoring. Or not monitoring at all.
Anything you’d add? Remove? Comment below.