1

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

2 Answers 2

3

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/

1
  • 1
    +1 For the simple explanation. And yes you can change the script anytime if your want action for lesser page count @PageCountLevel int = 500, in theIndexOptimize SP. Jul 18, 2017 at 9:17
1

Availability Group Databases

If you have a quick look at the documentation there is a nice example for the USER_DATABASES and AVAILABILITY_GROUP_DATABASES:

Databases

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES, and AVAILABILITY_GROUP_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 (,).

Value                                           Description  
----------------------------------------------  --------------------------------------------------------  
SYSTEM_DATABASES                                All system databases (master, msdb, and model)  
USER_DATABASES                                  All user databases`  
ALL_DATABASES                                   All databases`  
AVAILABILITY_GROUP_DATABASES                    All databases in availability groups  
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES   All user databases that are not in availability groups  
Db1                                             The database Db1  
Db1, Db2                                        The databases Db1 and Db2  
USER_DATABASES, -Db1                            All user databases, except Db1  
`%Db%                                           All databases that have “Db” in the name  
%Db%, -Db1                                      All databases that have “Db” in the name, except Db1  
ALL_DATABASES, -%Db%                            All databases that do not have “Db” in the name  

Index Fragmentation

As you correctly noted, index optimisation is triggered at certain levels.

  • An index is reorganised if there are more than 1000 pages in the table and index fragmentation is between 5 and 30 percent.
  • An index is rebuilt if there are more than 1000 pages in the table and index fragmentation is above 30 percent.

Depending on your requirements and/or the size of the data, you might have to reorganise some indexes earlier.

Very simple example

You have a table with 200 Million records (online archive) and 10'000 records are modified on a daily basis and 10'000 records are created daily. Doing the simple maths results in a maximum fragmentation of 0.01% which will never trigger the index reorg or rebuild. It would take 500 days to reach 5% fragmentation. In a production environment this could lead to potential performance issues, depending on the size of the table involved and the complexity of the queries.

Your question answered

Yes, you are good for fragmentation, with the above limitations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.