This is the seventh article in a series that examines the output of the Brent Ozar Unlimited sp_Blitz™ script run against the SQLServerCentral database servers. This article looks for future scans.
Managing a SQL Server is an ongoing job. While the SQL Server platform will run itself for a long time, as your workload evolves, your applications are patched, and your staff experiences changes, it's easy for configuration and security issues to creep into your environment. The best way to prevent issues and problems is with regular monitoring and auditing of your instances.
The SQLServerCentral servers are regularly monitored, using SQL Monitor from Red Gate Software, and you can see the data at monitor.red-gate.com. We expose that as a demonstration of what the monitoring software can do. This covers alerts and problems that occur in real time, but it doesn't catch some changes which may occur over time.
This is where some type of auditing becomes very important. One of the ways in which you might think about auditing your instances is with a standard script that examines various potential places where your configuration isn't optimal.
sp_Blitz™
Brent Ozar Unlimited has released a fantastic script for examining the configuration, health, and performance of your SQL Server instances. This script is known as sp_Blitz™ and is currently at v22. You can download it and it builds a stored procedure that you can run on any of your instances.
A tremendous amount of information is returned. If you are interested, you can read about the script on the Brent Ozar Unlimited site.
We used this script to perform a quick audit of our database server instances in December of 2012 and learned a few things. This series of articles examines the results and mitigation strategies we've taken. The other articles are:
- Security
- Reliability
- Performance - Part 1
- Performance - Part 2
- Query Plans
- Informational and other lower priority items
- The Future
This article looks forward at how we will use sp_Blitz in the future.
Ongoing Scans
We monitor and watch our SQL Server instances with SQL Monitor. That makes sense as it's the product we make as well as Red Gate Software (owners of SQLServerCentral). As we've mentioned above, you can go to monitor.red-gate.com and see a demo version of the software, but with real data. We replicate this data to a second instance where we show it on the web.
How does sp_Blitz™fit in here? We've actually integrated the script into SQL Monitor as a custom metric. You can see the configuration below:
We've loaded the script onto the instances and then we call it on a regular basis. We've used the parameter This results in a
As you can see from the graph below, there's a steady level of issues, though a few drops at times. Those are likely items that were transient in the system (like backups or latencies), but have re-occurred. Overall, these seems consistent with the original analysis that we performed.
Monitoring
The various issues we resolved with sp_Blitz reduced the numbers of alerts, but there are some items which are unlikely to change without substantial alterations to the application and database instances. Like many businesses, the site works well, so not necessarily worth much time to resolve all the issues that we've noted from the script.
However, when we have issues, we can quickly check this metric and determine if anything fundamental has changed. If it has, we can manually run the script to examine the results and dig in deeper to determine the problem.
The sp_Blitz™ script doesn't solve our problems, but it does give us a way to perform additional monitoring on the SQLServerCentral database servers and this is another data point that we can use to watch the health of our database servers. This script integrates easily into SQL Monitor, which lets our IT staff get alerted if issues arise.