Sql-server – Ola Hallengrens code for Index Optimization – Clarifications

indexola-hallengrensql server 2014

I am trying to deploy Ola Hallengrens code for Index Optimization in our PROD environment.

What is the difference between using USER_DATABASES and AVAILABILITY_GROUP_DATABASES in the place of @Databases in below code .

Also I was going through the blogs and read :

Ola Hallengrens code for Index Optimization is meant to defragment only with page count more than 1000.

When I execute below command it updates only statistics not rebuild indexes –

Does this mean I am good with fragmentation , not to worry much about it as my end users does not complaint any thing about slow response or bad performance from Database end .

EXECUTE dbo.IndexOptimize
 @Databases = '',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30

Best Answer

The difference between user_databases & availability_group_databases is that when specifying user_databases you will optimize every user database (also the ones in the AG group) if you specify availability_group_databases you will only optimize the indexes of databases inside an availability group.

If you do not have tables with fragmentation above 1000 pages, this will not hurt your SQL Server performance. I recommend you also read this post from Brent Ozar. https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

Before you start

Before you run the initial script, check the first few parameters and the database used:

use [master]
@CreateJobs
@BackupDirectory
@CleanupTime
@OutputFileDirectory
@LogToTable

Hint: Because the tables and procedures are created in the database specified at line 20 in the script (use [master]), you might want to consider storing these objects in a different database, by modifying the script to point to e.g. the msdb database, or some other user specified database.

Hint: Read the description for each parameter and modify according to your requirements or restrictions.

Objects created

The solution is deployed as one script named MaintenanceSolution.sql, which itself will create stored procedures, tables and jobs .

Following is a list of objects that are created:

Table

[dbo].[ComandLog]

Stored Procedures

[dbo].[CommandExecute]
[dbo].[DatabaseBackup]
[dbo].[DatabaseIntegrityCheck]
[dbo].[IndexOptimize]

Getting started

Out of the box Ola's MaintenanceSolution.sql script will create the following jobs:

Database Backup - SYSTEM_DATABASES - FULL
Database Backup - USER_DATABASES - DIFF
Database Backup - USER_DATABASES - FULL
Database Backup - USER_DATABASES - LOG
DatabaseIntegrityCheck - SYSTEM_DATABASES
DatabaseIntegrityCheck - USER_DATABASES
IndexOpitmize - USER_DATABASES
sp_delete_backuphistory
sp_purge_jobhistory
Output File Cleanup
CommandLog Cleanup

Hint: Please be aware that Ola's solution does not create any job schedules! This is something you will have to plan for and create yourself.

Now if you know that these jobs belong to Ola's solution, then you can leave them as is. Otherwise if you would prefer to mark them, then you might consider prefixing a string inside the MaintenanceSolution.sql before you execute the script. Do a regex search for the string SET @JobName[0-9][0-9] = ' with a suitable text-editor and add a prefix to the job's name

e.g. OLA Database Backup - SYSTEM_DATABASES - FULL

Consideration: Please keep in mind that if you update the scripts later on with a newer version from Ola and don't alter the names from the original to match your naming convention, then you will have duplicate jobs. You might want to keep the job names "as-is".

IndexOptimize

Ola created his IndexOptimize stored procedure according to the following Microsoft recommendations:

General Index Maintenace when data/index > 1000 pages
- Fragmentation >5% and <= 30%
  - Do: Index Reorg
- Fragmentation > 30%
  - Do: Index Rebuild

Hint: Depending on your data and/or the amount of records in some of your tables, you might want to consider creating individual jobs with different parameters that suit your requirements for larger tables.

Database backups

The backup job create sub-directories for each level of the database's instance. So if you are running the script on a server MyServer on an instance named MyInstance and the database is named MyDatabase and you are looking at a Full backup, then the backup file will be stored in the following directory with the following name:

H:\MyServer\MyInstance\MyDatabase\FULL\MyServer_MyDatabase_FULL_20170712_105500.bak

A DIFF backup will look like this:

H:\MyServer\MyInstance\MyDatabase\DIFF\MyServer_MyDatabase_DIFF_20170712_105500.bak

etc.

Best practice

Consider deploying the solution to a development server as is (ok, modify the database you will be using) and then have a look at the individual scripts, jobs and tables. Familiarise yourself with the solution and then modify according to your requirements.

And of course consider reading Ola's documentation on his site:

Best Answer

Related Solutions

SQL Server – Fixing Ola Hallengren’s Output File Cleanup Not Deleting Files

Sql-server – Deploying Ola Hallengrens scripts – Index Optimization