August 7, 2018 at 5:49 am
Hi all, first post here!!
In an environment where a defrag (Index Optimize) is running 2-3 times longer than usual, would appreciate any help with troubleshooting:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d Tools -Q
"EXECUTE [dbo].[IndexOptimize]
@databases = 'USER_DATABASES',
@TimeLimit = 21600,
@LogToTable = 'Y',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 60" -b
Thanks, JC
August 14, 2018 at 10:23 am
I realize this was a week ago, but have you taken a look at the CommandLog table in your master database?
You should be able to see the information (including the start and end times) for each index that is touched during the job's run. If there's one or more index operations that are taking longer than normally you can pinpoint it there.
Depending on how much history you keep and how often your job runs you can also see when the change happened. Usually when mine runs longer than normal it's because it's hitting more indexes than it has previously.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply