This is the fourth article in a series that examines the output of the Brent Ozar Unlimited sp_Blitz™ script run against the SQLServerCentral database servers.

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 v16. 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 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

This article examines those items related to the performance of your database server. It only covers the performance items not covered in the previous article. 

Performance

The third Findings Group output by the sp_Blitz script is the Performance section. These items can affect how your instance responds to client requests, warning you of items that may or may not cause your server to run slower. We received the following sections in our result set:

• High VLF Count
• Slow Storage Reads on Drive x
• Stored Procedure WITH RECOMPILE
• Active Tables Without Clustered Indexes
• Inactive Tables Without Clustered Indexes
• Queries Forcing Joins Hints
• Triggers on Tables
• Old Compatability Level

Each of these items is discussed below with regard to our database servers.

High VLF Count

This is one of those areas that can definitely affect performance if you are writing lots of log records. I used a quick script from Michell Ufford to look for the current VLF counts. Most of our databases were < 100, bt we had 4 databases that were larger:

I suspect all of these high counts are related to the problems of the transaction log files being larger than the data files. As is common with many systems, someone was using small autogrow settings and not backing up the log files. As a result the files grew constantly, adding lots of VLFs along the way.

My mitigation procedure here, after getting the OK from our IT group, was to shrink the log files and then grow them back. This was mentioned in the second article of this series (Reliability) and it's what I decided to do. My plan was to run this code for each database:

dbcc shrinkfile(CommunityServer_Log, TRUNCATEONLY)
go
ALTER DATABASE MyDatabase
 MODIFY FILE (NAME =  CommunityServer_Log, SIZE = 1000MB)

I would change the file names and sizes as appropriate for the particular databases. This worked well for one database, but not the others. Apparently I had active VLFs in the files that prevented me from shrinking the files down to a small amount. I was hoping to get each to its minimum size before growing it.

As a result, I used a trick that I've been using since SQL Server 7. I did the following:

1. created a dummy table
2. added some rows to my dummy table
3. ran a log backup
4. re-ran the shrink file

For all for the databases except one, this worked and I was able to then modify it to a number that was larger than any of the log backups. My reasoning on the file size was to prevent any autogrows from occuring by ensuring there was adequte log space. I grew a few of them larger than I might need, just because we are dealing with rather small storage sizes and a few extra GB don't affect these systems.

For the one file that didn't respond here, I repeated steps 2-4 above a few times and managed to shrink the file down to a small size before expanding it. If you are interested in how to expand your transaction log file, here's a great post from Kimberly Tripp of SQLskills.

When I finished, all of my databases had VLF counts below 100 except 1. Community Server was at 126, which is still low enough that I didn't bother redoing my shrink/grow proces again.

You can read more about this alert at BrentOzar.com.

Moving Forward

This is the fourth article looking at the  very helpful and popular sp_Blitz™ script, and it examined part of the performance section of the output. The next article will examine the Query Plans section of the script as run against the SQLServerCentral database servers.

The other articles in this series are:

• Security
• Reliability
• Performance - Part 1
• Performance - Part 2
• Query Plans
• Informational and other lower priority items

This is a great script to run on your instances, but you need to run it periodically to catch changes, some of which might potentially be causing you problems, or will cause you problems in the future. Saving your output from previous runs and comparing the results and looking for changes is a good idea to save during future analysis.