Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLServerCentral Runs sp_Blitz - Performance Part 1

By Steve Jones,

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:

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 log, 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
In many of the databases, I found a number of indexes set to be at 80 or 90 for the fillfactor. that makes some sense, leaving a little bit of free space on new pages to allow for updates and changed in between maintenance.
However I also found a number of indexes set with a fillfactor of 99. That makes no sense to me, unless I would be expecting only 1 or 2 updates for a page that might not cause a page split. I'm not sure it's worth making the change now, but I'd like to do some testing here if I get time and see if there are page splits occurring and if setting these indexes to 95 instead of 99 (or 90) improves performance. My guess is we won't notice changes, but it might be worth trying.

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:

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.

Total article views: 7947 | Views in the last 30 days: 15
 
Related Articles
SCRIPT

Script to change auto growth settings for all databases in SQL Server

Script to change auto growth settings for all databases in SQL Server.

FORUM

Changing db owner script

need to change multiple databases on many servers

FORUM

Script for Changing Port Number of SQL Server 2005

Script for Changing Port Number of SQL Server 2005

ARTICLE

SQLServerCentral Runs sp_Blitz - Performance Part 2

The second part of our performance examination on the SQLServerCentral database servers using the sp...

FORUM

Downloading a changing SQL Server Database structure to Access

Programatically Downloading a changing SQL Server Database to Access

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones