IndexOptimize worsen performance?

[robertonunnari]

Hello.

I use IndexOptimize with standard parameters on several MS SQL Servers.

The problem is that when it runs, on one server performance of a particular query becomes worse. Please, find attached a graph that shows on the left the time that it took for the query to complete, and on the right the count of indexes that need rebuild or reorg.

Now I rescheduled it to run every six months because I don't want to risk performance get even worse.

Could anybody help me understand what's going on? Why it happens?

Best regards.
Roberto

[robertonunnari]

I'll add some more details:

DBMS: Microsoft SQL Server Standard 2016 64bit (version 13.0.5830.85, CU14)
OS: Microsoft Windows Server 2012 R2

select distinct DTreeCore.DataID, rendition.DataID as templateID, CONCAT(DTreeCore.Name, ' - ', name.ValStr) as text, DTreeCore.DComment
from DTreeCore
right join DTreeACL on (DTreeACL.DataID = DTreeCore.DataID and See >= 1)
right join KUAFRightsListNew k on (DTreeACL.RightID = k.RLRightID and k.RLID = 12345678 AND k.RLProxyType = 0)
inner join LLAttrData name on (name.ID = DTreeCore.DataID
and name.VerNum = DTreeCore.VersionNum
and name.DefID = 1234567
and name.AttrID = 26)
inner join LLAttrData bp on (bp.ID = DTreeCore.DataID
and bp.VerNum = DTreeCore.VersionNum
and bp.DefID = 1234567
and bp.AttrID = 13
and bp.ValStr = 'Indipendente')
inner join LLAttrData status on (status.ID = DTreeCore.DataID
and status.VerNum = DTreeCore.VersionNum
and status.DefID = 1234567
and status.AttrID = 2
and status.ValInt = 1)
left join DTreeCore rendition on (DTreeCore.Name = rendition.Name
and rendition.ParentID = 1234123)
where DTreeCore.ParentID = 2345678
and DTreeCore.Deleted = 0

[robertonunnari]

An update.
I got the performance of that query back to normal by running Statistics Update only:
EXECUTE dbo.IndexOptimize
@databases = 'MYDB',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@LogToTable = 'Y',
@LockTimeout=60,
@Timelimit='7200'

For completeness, here's the parameters to the IndexOptimize procedure:
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',
@LogToTable = 'Y',
@LockTimeout=60,
@Timelimit='7200'

Anybody can give me some insight on why doing UpdateStatistics together with Index Fragmentation behaves that way?

Best regards.

[Stiffboard]

Hi, Enterprise or Standard? Indexing and update statistics is two different things. Now you are only updating statistics. Spinning disks or SSD drives? Many or large indexes?

[robertonunnari]

Hi.
Standard.
One of the queried tables has 48 million rows, and two indexes.
It's a VM in a VSAN made of SSDs.
Looks like that when doing a Reorg after that that it does an update stats, while after a rebuild it doesn't.

Why?

[Stiffboard]

If you are running Standard rebuild is an offline manoeuvre. Will rebuilding a 48 million row make it faster on the SSDs? Consider changing the defaults.

https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/.

Perhaps skipping small ones?

DECLARE @jobid uniqueidentifier = (SELECT job_id FROM msdb.dbo.sysjobs where name = 'IndexOptimize - USER_DATABASES')

EXEC msdb.dbo.sp_update_jobstep @job_id=@jobid, @step_id=1 ,
@command=N'EXECUTE [dbo].[IndexOptimize]
@databases = ''USER_DATABASES'',
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 40,
@FragmentationLevel2 = 70,
@MinNumberOfPages = 4000,
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y'',
@LogToTable = ''Y'''

Less I/O on shared storage, less time (CPU) spent on indexing, less transaction logs to backup.

[Stiffboard]

Look at the logfile that is generated and you can see what is updated, index and statistics.

[pbarryuk]

Could Parameter Sniffing be a potential cause?

[robertonunnari]

No idea.

[seanblack65536]

A rebuild will automatically update the stats. Your initial results is probably due to a reorg.

…

On Wed, 12 Apr 2023 at 13:47, robertonunnari ***@***.***> wrote: No idea. — Reply to this email directly, view it on GitHub <#734 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOCZXOFN46W4AS5PEK6O2KLXA2P5XANCNFSM6AAAAAAWWNK3CI> . You are receiving this because you are subscribed to this thread.Message ID: <olahallengren/sql-server-maintenance-solution/repo-discussions/734/comments/5592263 @github.com>

[seanblack65536]

A reorg compacts the index, a rebuild is more optimal. I've stopped using reorg. On Wed, 12 Apr 2023 at 16:10, Khalid Mohammed < ***@***.***> wrote:

…

A rebuild will automatically update the stats. Your initial results is probably due to a reorg. On Wed, 12 Apr 2023 at 13:47, robertonunnari ***@***.***> wrote: > No idea. > > — > Reply to this email directly, view it on GitHub > <#734 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AOCZXOFN46W4AS5PEK6O2KLXA2P5XANCNFSM6AAAAAAWWNK3CI> > . > You are receiving this because you are subscribed to this thread.Message > ID: > <olahallengren/sql-server-maintenance-solution/repo-discussions/734/comments/5592263 > @github.com> >