dimanche 1 mars 2015

Ola Hallengren Index Optimise Script not Rebuilding Indexes


I have downloaded Ola Hallengren's script and deployed to master database. I run it using the 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


I get this output in SSMS but the Indexes have not been rebuilt. Fragmentation is still very high. Am I missing something?



Date and time: 2015-03-01 14:07:24
Server: TestSvr
Version: 10.50.2500.0
Edition: Standard Edition (64-bit)
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @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,
@PageCountLevel = 1000, @SortInTempdb = 'N',
@MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL,
@LOBCompaction = 'Y', @UpdateStatistics = NULL,
@OnlyModifiedStatistics = 'N', @StatisticsSample = NULL,
@StatisticsResample = 'N', @PartitionLevel = 'Y',
@MSShippedObjects = 'N',
@Indexes = NULL, @TimeLimit = NULL, @Delay = NULL,
@WaitAtLowPriorityMaxDuration = NULL,
@WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL,
@LogToTable = 'N', @Execute = 'Y'
Source: http://ift.tt/1w4Tfii

Date and time: 2015-03-01 14:07:24
Database: [TestData]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE


enter image description here





2 commentaires:

  1. You are sending in a "@PageCountLevel = 1000" parameter and the index specifics you shared had only 679 pages. This parameter defines the minimum number of pages for an index to have for it to be considered for a reorg or rebuild. 1000 pages seems to be a generally accepted number to keep this on, if not a bit high. Defragmentation of small indexes is not efficient, thus the need for this flexible parameter.

    RépondreSupprimer
  2. Ce commentaire a été supprimé par l'auteur.

    RépondreSupprimer