SQL Server tuning

Summary of SQL performance notes:

The bottleneck that most SQL Servers become encumbered by first is Disk I/O.  Reducing unnecessary Disk I/O during business hours should be one of the primary goals of any database administrator to improve database performance, maybe running a close second to backup monitoring.   A properly managed database server will keep the SQL Server query optimizer up to date with the latest information (statics) to provide the fastest query results with the least amount of Disk I/O, memory and CPU usage possible.  SQL Server needs help to stay running smoothly through regular monitoring and maintenance.

Along with a good maintenance plan, which is right up there with the backup monitoring too, there are many important tuning processes required for optimal performance for a SQL Server instance and database performance.  Below are some of considerations with links to some best practice information that can help with overall performance to keep a SQL Server instance running optimally.   Implementing a good maintenance plan like the solution that we suggest clients consider using created by Ola Hallengren does not help if it does not run efficiently every night.

Below are a few important items that are not always monitored as frequently as necessary to help maintain optimal performance:   monitoring the TEMPDB size, File Growth\Log file Growth tuning, Monitoring your maintenance plan, and Index fill factor optimization.

  • TempDB tuning has been covered in length by others, but with most current servers 8 TEMPDB files at 256 or 512 each are usually a good starting point for a server with 8 CPUs.  The tempdb is recreated every time SQL Server is restarted.   Making sure it is recreated with the appropriate initial sizes will decrease the related Disk I/O.


  • Your backup solution should be tested often to make sure it is completing and has plenty of storage for future backups that meet your data retention policy requirements.  A backup restoration procedure should be created and tested, so if data corruption occurs, the restoration process should be clear and complete.  When your users are sitting idle waiting to work, is not the time for a DBA to try and figure out how to restore a database.
  • Have only one log file for the Infinity products because a specific and expensive hardware design would have to be used to see a benefit from multiple logs and I’ve not heard of anyone using the needed configuration, so far.

–Need to create a full backup before and after.  This should drop the 2nd log if not in use if it exists.


  • Automatic File Growth by percentage, which is the default, is bad for a SQL Server with large SQL databases or number of transactions and unnecessarily increases disk I/O that could take place in the off hours instead of during the busy load times during the day.  LOG file growth should be configured, managed and monitored as well to reduce the number of VLFs.  Depending on your databases server utilization, having more than 50 VLFs(Virtual Log files) can impact SQL Server performance and impact maintenance procedures run times, like backups or large DB writes.  For a DB with a large log file close to or over 8 GB, I would start with an 8000 MB log file that grows by 8000 MB.  The goal is to have a log file that does not grow automatically in random sized chunks most of the time, but stays the same sized to decrease unnecessary I/O with the users are in the system.



  • Check your Maintenance plans logs.  Is your maintenance plan completing?  Using the solution created by Ola Hallengren is a great place to start, his FAQ has the following information about monitoring for indexes that are becoming fragmented quickly and need a larger fill factor.  WARNING: Fill factors should be increased gradually and only if necessary for frequently updated indexes and will increase the overall size of your database.

How can I log the commands in IndexOptimize to a table so that I can analyze which indexes are becoming fragmented quickly?

You can use the @LogToTable = ‘Y’ option to log the commands to a table. Then you can use this script to analyze the data.

  • Using the solution created by Ola Hallengren it is easy to update your databases statistics nightly, if not running the index

EXECUTE dbo.IndexOptimize

@Databases = ‘USER_DATABASES’,

@FragmentationLow = NULL,

@FragmentationMedium = NULL,

@FragmentationHigh = NULL,

@UpdateStatistics = ‘ALL’,

@OnlyModifiedStatistics = ‘Y’

Symptoms that are typical for issues related to statistics. What they all have in common is seemingly strange variations in duration when the server has no resource problems and you have confirmed that there is no blocking:

  • The same query (with the same values in the search argument or WHERE clause) varies in duration without any related blocking or resource problem
  • Some queries degrade in performance as time passes since the last Index rebuild
  • The same procedure will perform different depending on what values are passed into it
  • Intermittent performance problem on some tables without any related blocking or resource problem
  • Although all of these symptoms may be related to other issues, the quality of the statistics should be the first thing checked

For more information:


  • Index Fill Factor tuning is one of the key tuning steps to allow for a maintenance plan to work optimally and efficiently.  Nightly maintenance will not complete on busy OLTP databases if the fill factors are not properly maintained.  The link below has some great information about monitoring fill factors.  Because fill factor can have such a large impact on performance when set incorrectly, it is recommended to increase fill factors gradually, validate it is needed and evaluate performance after the changes and revert back if no improvement.  Avoid blanket fill factor polices on large databases.


Summary information about why fill factors are important

Goals for Fill Factor for optimization:

  • Highest possible fill factor to prevent page splits when the index is modified
  • Lowest possible fragmentation { 5 acceptable } (15 impacts performance and 30 percent fragmentation will likely changes query plans depending on the queries.)
  • Highest possible fill ratios to increase read performance.  Read only or infrequently updated indexes should stay at a 100 percent fill factor for example.
  • Allows the maintenance plan to finish during a defined or acceptable maintenance windows

To tune fill factors correctly start with the problem tables

  • Only Indexes greater than 8 MB
  • Skip most indexes where Key is monotonically increasing.
  • Focus on problem indexes
    • Default Fill Factor
    • High Fragmentation
    • Average Fragmentation and High Page fill factor
    • Low Fragmentation and Low Page fill
    • Fill factor < 100 on Read only Partition, if applicable

Fill factor should be based upon calculations per index on each table.

  • Without proper fill factors good indexes can become poor indexes quickly.
  • Increased Read times when fill factors are increased
  • Decrease the number of Page Splits to minimize Disk I/O
  • Increased Write times when Page Splits are necessary
  • Increased CPU usage for page splits and peaks with splits and increases server overhead dramatically.
  • Maintenance window pressure
    • Indexes become useless without maintenance and will cause full table scans if the regular maintenance cannot complete in a timely fashion
    • Bad fill factor causes a bad balance between the main resources
      • Using more CPU that you should
      • Using memory to contain the splits that need to be extended
      • Wasting I/O and increasing traffic required to storage
      • Memory and CPU used to manage I/O instead of what should be necessary
    • Compression will increase the problem as well unless all reads are sequential and should be monitored closely
      • Compressed fragmentation causes higher I/O than non compressed
    • Need to look at all indexes independently
  • Running SQL Server as a VM requires even more monitoring and tuning of SQL Server to run optimally and special consideration for servers that require tier 1 performance.  Configuring and testing should be done to achieve optimal performance.


  • SQL Server trace flags that should be considered tested for performance  (SDO uses some of these already in their server setup)

DBCC TRACEON (1118,-1) — TempDB entire page vs. partial to avoid latch contention on busy SQL Servers

DBCC TRACEON (1222,-1) — Deadlocks in the SQL Server Error log that can be used to raise alerts

DBCC TRACEON (3226,-1) — by default, every successful backup operation adds an entry in the SQL Server

— error log and in the system event log. This flag strips them out to reduce the log size .

DBCC TRACEON (834,-1)  — WARNING Need Memory MAX configured correctly or SQL Server may not start.  Slow down startup time of instance. Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool.  MVWare environemnts strongly consider to prevent memory sharing with other VMs

  • Forced Parameterization – database setting for busy SQL Servers that run at a constant CPU that appears abnormal caused by a high number of ad-hoc queries.  Changing this will reduce CPU and memory utilization in some shared SQL environments.

Please leave a reply and let me know what you think.

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s