This is the third 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 a few of the items returned. The next article will examine the other performance items.
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:
- File growth set to percent
- Fill Factor Changed
- Foreign Keys Not Trusted
- High VLF Count
Each of these items is discussed below with regard to our database servers.
File growth set to percent
This is one of those items that is good to understand, but it's necessarily something that needs to be changed. For the SQLServerCentral database instance, we received this alert for the following databases.
- master
- model
- msdb
As you can see in the public SQL Monitor site, this is true (master shown).
This is opposed to the autogrow setting on the SQLServerCentral database, which is a fixed amount.
In general, as a database grows, the percentage can become a problem. Not many databases grow their growth regularly over time. Lots of databases have a slow acceleration. Your case may be different, but in general I've seen a fairly steady growth over any short period of time in most of the databases I've administered. If I look at the change in master over time, it's not much (as expected).
This is five months of data space, and it's steady. I don't think this needs to be changed from a percentage, but for the sake of consistency (since all other databases are a fixed growth amount in case of emergency, I'll change this (after notifying IT).
| Database | Old Autogrow Setting | New Autogrow Setting |
| master | 10% data and log | 25MB data, 5MB log |
| model | 1MB data, 10% log | 10MB data, 5MB log |
| msdb | 10% data and log | 50MB data, 20MB log |
Where did I get these numbers? From here:
There isn't a great way to determine what your autogrow settings should be. Every database should be considered individually. For the master and msdb databases, there is almost no growth across the last six months. I don't anticipate much in the next, so I'm choosing numbers that are beyond what I expect if there is an emergency, and are probably large, percentage-wise, but since we're talking MB, I'd rather grow too much than too little here.
The msdb database is a little different. This database is currently 480MB. Again, almost no growth across 6 months. We have a fairly steady state for the data being stored here. As new backups are run, old ones trimmed, so not a lot of data in here. I'm choosing a (roughly) 10% growth as a fixed number. The log also is steady, so I'm going slightly larger here, percentage-wise, since if things go wrong in msdb, I expect more log activity than data activity.
You can read more about this alert at BrentOzar.com.
Fill Factor Changed
Fill factor is a concept that confuses lots of new DBAs. Essentially this is the amount of free space SQL Server tries to leave on each data page in your database when the page is filled (new or index rebuild). However this isn't maintained. Across lots of changes, you can end up with more or less free space.
Why change the fill factor? The main reason is that you are trying to reduce page splits that are the result of lots of data changes in between index rebuilds. If data cannot fit on a page (new insert or update), then there is a lot of work the storage engine must do. However if you set the fill factor too low, then there is wasted space, both on disk and in memory. Wasting memory is bad, so you don't necessarily want to change the fill factor if you don't need to.
On our database instances, we have objects in almost every database with different fill factors. If I examine some of the objects in the various databases, I decide to run this code:
select fill_factor , object_id , name , index_id , type_desc from sys.indexes where fill_factor > 0
You can read more about this alert at BrentOzar.com.
Foreign Keys Not Trusted
Foreign Keys (FKs) exist mainly to ensure referential integrity in a database. If the foreign keys are trusted, with the sys.foreign_keys.is_not_trusted = 0, then the query optimizer can choose more efficient plans. If the FKs are not trusted, then SQL Server won't use these indexes to build better plans.
Our results showed untrusted foreign keys in two databases: msdb and SQLServerCentralForums. The ones in msdb are not a problem as these are all from the Database Tuning Advisor (DTA). I could remove them, but there's no need. We rarely run the DTA and these don't affect our performance.
The indexes in the SQLServerCentralForums database are a bit more troubling. This is a third party product we purchased (InstantForum) and I'm hesitant to make changes here without some extensive testing. We don't really get support on the code, but we don't want to cause ourselves any issues either. I'd like to ask our developers to do some testing on this from their end, and then either have myself or Grant Fritchey spend some time looking at the data t be sure it conforms to the FK in production. This can be time consuming, so for now this will remain on our scan.
You can read more about this alert at BrentOzar.com.
Moving Forward
This is the third 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 rest of the Performance 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.
