Help with the Index Optimize solution

[WJBergen]

Hi Ola

I am using version 13.0.5698.0 of your truly great index maintenance script.

Date and time: 2020-11-13 17:15:33 [SQLSTATE 01000]
Server: UPPMNEPICDBWN04 [SQLSTATE 01000]
Version: 13.0.5698.0 [SQLSTATE 01000]
Edition: Enterprise Edition: Core-based Licensing (64-bit) [SQLSTATE 01000]
Platform: Windows [SQLSTATE 01000]
Procedure: [DBATASKS].[dbo].[IndexOptimize] [SQLSTATE 01000]
Parameters: @databases = 'clarity', @FragmentationLow = 'INDEX_REORGANIZE', @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REORGANIZE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'Y', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'N', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'Y', @indexes = NULL, @Timelimit = NULL, @delay = NULL, @WaitAtLowPriorityMaxDuration = 1, @WaitAtLowPriorityAbortAfterWait = 'BLOCKERS', @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = 60, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'Y', @LogToTable = 'Y', @execute = 'N' [SQLSTATE 01000]
Version: 2020-11-01 18:16:36 [SQLSTATE 01000]
Source: https://ola.hallengren.com [SQLSTATE 01000]
[SQLSTATE 01000]
Date and time: 2020-11-13 17:15:34

I know that I am doing something wrong because I have a 25T database with 28000+ vendor tables and at least as many indices.
We also have over 96k of autogenerated statistics
Lastly, the vendor has indicated that I must not reorg indices on 89 tables and that because of the way the san is set up with 8k blocks that we should not do a rebuild but only a reorg until we move to the correctly configured san parameters and blocksize.
The info above is from the most comprehensive execution that only took 5 hrs and 32 mins

I don’t see any change is indices that are still highly fragmented and over the 1000 page threshold
The autogenerated statistics appear to the vast majority in the first run that were put into the command, queue, or queuedatabase table.
Also, the autogenerated statistics are not being updated

These are the parameters that I am

DECLARE @rc int
DECLARE @databases nvarchar(max)
DECLARE @FragmentationLow nvarchar(max)
DECLARE @FragmentationMedium nvarchar(max)
DECLARE @FragmentationHigh nvarchar(max)
DECLARE @FragmentationLevel1 int
DECLARE @FragmentationLevel2 int
DECLARE @MinNumberOfPages int
DECLARE @MaxNumberOfPages int
DECLARE @SortInTempdb nvarchar(max)
DECLARE @MaxDOP int
DECLARE @FillFactor int
DECLARE @PadIndex nvarchar(max)
DECLARE @LOBCompaction nvarchar(max)
DECLARE @UpdateStatistics nvarchar(max)
DECLARE @OnlyModifiedStatistics nvarchar(max)
DECLARE @StatisticsModificationLevel int
DECLARE @StatisticsSample int
DECLARE @StatisticsResample nvarchar(max)
DECLARE @PartitionLevel nvarchar(max)
DECLARE @MSShippedObjects nvarchar(max)
DECLARE @indexes nvarchar(max)
DECLARE @Timelimit int
DECLARE @delay int
DECLARE @WaitAtLowPriorityMaxDuration int
DECLARE @WaitAtLowPriorityAbortAfterWait nvarchar(max)
DECLARE @Resumable nvarchar(max)
DECLARE @AvailabilityGroups nvarchar(max)
DECLARE @LockTimeout int
DECLARE @LockMessageSeverity int
DECLARE @StringDelimiter nvarchar(max)
DECLARE @DatabaseOrder nvarchar(max)
DECLARE @DatabasesInParallel nvarchar(max)
DECLARE @LogToTable nvarchar(max)
DECLARE @execute nvarchar(max)

---Build the exclusion list for @indexes

declare @all_exclusions nvarchar(max)
declare @excluded_dbname sysname
declare @excluded_schema sysname
declare @excluded_table sysname
declare @counter int
set @counter = 0
declare @number_of_exclusions int
select @number_of_exclusions = count(*)
from
DBATASKS.[dbo].[FRAGMENTATION_REORG_REBUILD_EXCLUSION_LIST];
set @all_exclusions = 'ALL_INDEXES'
declare exclusion_list cursor static
for
select
database_name
, schema_name
, table_name
FROM DBATASKS.[dbo].[FRAGMENTATION_REORG_REBUILD_EXCLUSION_LIST];
open exclusion_list;
select @all_exclusions;
fetch next from exclusion_list into @excluded_dbname, @excluded_schema, @excluded_table;
while (@@FETCH_STATUS = 0)
begin
set @counter = @counter + 1;
if @counter <= @number_of_exclusions
begin
set @all_exclusions = CONCAT(@all_exclusions, ', -', @excluded_dbname, '.', @excluded_schema, '.', @excluded_table);
end
fetch next from exclusion_list into @excluded_dbname, @excluded_schema, @excluded_table;
end
close exclusion_list;
deallocate exclusion_list;
select @all_exclusions;

---

---

set @databases = 'clarity'
set @FragmentationLow = 'INDEX_REORGANIZE'
set @FragmentationMedium = 'INDEX_REORGANIZE'
set @FragmentationHigh = 'INDEX_REORGANIZE'
set @FragmentationLevel1 = 5
set @FragmentationLevel2 = 30
set @MinNumberOfPages = 1000
set @SortInTempdb = 'Y'
set @MaxDOP = 0
set @FillFactor = 85
set @PadIndex = 'Y'
set @LOBCompaction = 'N'
set @UpdateStatistics = 'INDEX'
set @OnlyModifiedStatistics = 'Y'
-------------set @StatisticsModificationLevel = 10
-------------set @StatisticsSample int
set @StatisticsResample = 'Y'
set @PartitionLevel = 'Y'
set @MSShippedObjects = 'N'
set @indexes = @all_exclusions
set @Timelimit = 60
set @WaitAtLowPriorityMaxDuration = 1
set @WaitAtLowPriorityAbortAfterWait = 'BLOCKERS' ---POSSIBLY NONE
set @Resumable = 'N'
set @lockmessageseverity = 16
set @LockTimeout = 60 ---wait 60 secs for a lock
set @StringDelimiter = ','
set @DatabasesInParallel = 'Y'
set @LogToTable = 'Y'
set @execute = 'Y'

---

---

EXECUTE @rc = [dbo].[IndexOptimize]
@databases
,@FragmentationLow
,@FragmentationMedium
,@FragmentationHigh
,@FragmentationLevel1
,@FragmentationLevel2
,@MinNumberOfPages
,@MaxNumberOfPages
,@SortInTempdb
,@MaxDOP
,@FillFactor
,@PadIndex
,@LOBCompaction
,@UpdateStatistics
,@OnlyModifiedStatistics
,@StatisticsModificationLevel
,@StatisticsSample
,@StatisticsResample
,@PartitionLevel
,@MSShippedObjects
,@indexes
,@Timelimit
,@delay
,@WaitAtLowPriorityMaxDuration
,@WaitAtLowPriorityAbortAfterWait
,@Resumable
,@AvailabilityGroups
,@LockTimeout
,@LockMessageSeverity
,@StringDelimiter
,@DatabaseOrder
,@DatabasesInParallel
,@LogToTable
,@execute

So can you please take a look at what I have presented and give me some guidance on where I am going wrong.
I have been over your online documentation many times and can’t see what I am missing.

[olahallengren]

Also, the autogenerated statistics are not being updated

Could you try changing from @UpdateStatistics = 'INDEX' to @UpdateStatistics = 'ALL'?

I don’t see any change is indices that are still highly fragmented and over the 1000 page threshold

Have you checked the output - file from IndexOptimize? Are the indexes being reorganized?

As a side note, I can recommend that you to pass the parameters to the stored procedure, by name, not by position.

[WJBergen]

I changed the parameters per your info below. The job still ran in only 17 minutes In the commandlog table there are 4339 alter index statements out of 62,280 rows in the table. Of those 62,280 51,919 rows had update statistics statement as the command that was executed. Note, the vendor does not want me to update statistics for the auto-generated stats. The following shows the distinct commantypes from all the runs CommandType Count ALTER_INDEX 4284 DBCC_CHECKDB 2 UPDATE_STATISTICS 57994 Bill Bergen University of Pennsylvania Health System Sr. SQL Server Database Administrator Work Phone – 215-662-6210 Work Cell Phone – 215-275-7972 Email – [email protected] From: Ola Hallengren <[email protected]> Sent: Sunday, November 29, 2020 5:46 PM To: olahallengren/sql-server-maintenance-solution <[email protected]> Cc: Bergen, William <[email protected]>; Author <[email protected]> Subject: [External] Re: [olahallengren/sql-server-maintenance-solution] Help with the Index Optimize solution (#449) Also, the autogenerated statistics are not being updated Could you try changing from @UpdateStatistics = 'INDEX' to @UpdateStatistics = 'ALL'? I don’t see any change is indices that are still highly fragmented and over the 1000 page threshold Have you checked the output - file from IndexOptimize? Are the indexes being reorganized? As a side note, I can recommend that you to pass the parameters to the stored procedure, by name, not by position. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#449 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AR4QFLKIMO3IE4M3UR4CL7LSSLFI7ANCNFSM4UCYI2QQ>.

[WJBergen]

Hi Ola
I am using IndexOptimize Version: 2020-11-01 18:16:36
Unfortunately, I have a database with 28,000+ tables and about as many indices
The version I am using appears to process in table name order and has reorged the same index multiple times.
So my questions are as follows:

1. Is there a way that you can save and show the fragmentation percent before any action is taken? For this particular database using the Microsoft provided dmv of physical_stats takes up to 10 hours to get fragmentation information for all indices even when using the limited option.
2. Is there a way to process the index reorgs not in table name order but rather
   a. In descending fragmentation order or
   b. In descending number of row order or
   c. In descending number of pages order or
   d. Any order the user wishes
3. Since the job runs from 11am for 10 hours every day is there a way to have the job start at the next selection (table or fragmentation level or any of the 4 items above) to prevent the same table and index from being processed multiple times?

I have some of these capabilities (not all) in a similar version of reorg/rebuild that generates 3 jobs that run simultaneously if you would like to collaborate and offer two versions of your product.
Please let me know how you feel about this option and possibility

Thanks in advance.

Bill Bergen
University of Pennsylvania Health System
Sr. SQL Server Database Administrator
Work Phone – 215-662-6210
Work Cell Phone – 215-275-7972
Email – [email protected]