SQL Server Index and Statistics maintenance: Importance and a good solution with explanation (Ola hallengren’s IndexOptimize)

INTRODUCTION

Index and Statistics need to be maintained regularly for optimum performance of a SQL Server database engine. The SQL Server core engine within the Query Optimizer part of it, uses Indexes and statistics to create a query plan to execute any piece of SQL and T-SQL passed to it.

Indexes and stats need maintenance to be kept in healthy state so that the Query optimizer can (in most cases) create the most efficient query execution plan which utilises least resources to accomplish what is requested. An inefficient query plan naturally causes over all slow response which affects the resources usage negatively and ultimately bad end user experience.

In short the process of query optimisation is finding the most efficient route which will require least amounts of the reads both logical and physical and these decisions are based on the information available which is the statistics and indexes on tables.

 

Solution Overview

Requirements

As a standard solution to be deployed across the board basic parameters need to be specified which can cater for most scenarios ideally.

 

Moreover the solution needs to have /be,

 

  1. Native SQL engine based. No proprietary code.
  2. Fast: Subsequent executions should be faster ideally by avoiding repeatition
  3. Smart: To be able to decide based on standards whether to perform Index recreation (online or offline), reorganisation just statistics updates.
  4. Cost effective
  5. Customisable where unique circumstances dictate
  6. Easily deployable
  7. Leave alone all indexes less than 5% fragmentation
  8. Reorganize indexes with >5% and <=30% fragmentation
  9. Rebuild indexes more than 30% fragmented on all user databases
  10. Update only modified statistics on all user databases

 

 

Solution

Code being used is two stored procedures out of “Maintenance Solution” by Olla Hallengren

  1. CommandExecute – Helps execute custom SPs
  2. IndexOptimizer – Hold the logic to maintain indexes and stats

 

It is well regarded in the industry and uses standard SQL commands from within the core engine plus meets all of our requirements.

 

 

The IndexOptimizer code allows quite a few parameters with different values full details of which are available here https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

(Text and image copies of this web page and the full code listing of the stored procedures used is part of the appendix at the end of the document)

 

IndexOptimize is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics. IndexOptimize is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016.

 

 

 

 

Solution Deployment

Methodology

The two stored procedures used are to be created on each server

  1. CommandExecute – Helps execute custom SPs
  2. IndexOptimizer – Hold the logic to maintain indexes and stats

 

Indexoptimize method with our standard is to be called in the “Daily Backup and Performance Maintenance Plan” Maintenance Plan via the “Execute T-SQL statement Task” step from the toolbox.

 

Typically leaving the named standard daily plan looking like following.

 

 

The standard to follow is to make sure that this optimisation has taken place after full backup which is after Database integrity checks. The sequence of the clean-up tasks isn’t that important as long as they run.

 

 

Parameters configuration

The deployed execution script looks like below,

 

EXECUTE dbo.IndexOptimize

@Databases = ‘USER_DATABASES’,

@FragmentationLow = NULL,

@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@UpdateStatistics = ‘ALL’,

@OnlyModifiedStatistics = ‘Y’

 

 

Note: The stats get automatically updated with full scan when the indexes are recreated so no need to redo the job especially when the default setting for stats up is not full scan but 10% instead.

 

 

@Databases = ‘USER_DATABASES’,

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, and ALL_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

 

Here we have set it on all user databases

 

@FragmentationLow = NULL,

No action on the Indexes lower the lowest threshold. In our case 5% fragmentation.

 

 

@FragmentationMedium =

‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

Specify index maintenance operations to be performed on a medium-fragmented (>5% and <=30%) index. It will try the actions in sequence specified and will exit after whichever one is possible first.

 

 

@FragmentationHigh =

 ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

Specify index maintenance operations to be performed on a High-fragmented (>30%)  index. It will try the actions in sequence specified and will exit after whichever one is possible first.

 

 

@FragmentationLevel1 = 5,

Set the lower limit, as a percentage, for high fragmentation/

 

 

@FragmentationLevel2 = 30,

Set the lower limit, as a percentage, for high fragmentation

 

@UpdateStatistics = ‘ALL’,

All means, Update all index and column statistics.

 

 

@OnlyModifiedStatistics = ‘Y’

 

Y means Update statistics only if any rows have been modified since the most recent statistics update

 

 

Advertisements
About

IT professional with wider interest in technology

Tagged with: , ,
Posted in sql, SQL Server, tsql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: