Ola IndexOptimize - USER_DATABASES failed
-
EXECUTE [dbo].[IndexOptimize]
@databases = 'USER_DATABASES',
@LogToTable = 'Y'
Job Name IndexOptimize - USER_DATABASES
Step Name IndexOptimize - USER_DATABASES
Duration 01:00:14
Sql Severity 16
Sql Message ID 50000
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. ...0) Server: SERVER1 [SQLSTATE 01000] (Message 50000) Version: 15.0.4316.3 [SQLSTATE 01000] (Message 50000) Edition: Enterprise Edition: Core-based Licensing (64-bit) [SQLSTATE 01000] (Message 50000) Platform: Windows [SQLSTATE 01000] (Message 50000) Procedure: [master].[dbo].[IndexOptimize] [SQLSTATE 01000] (Message 50000) 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, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL, @LogToTable = 'Y', @Execute = 'Y' [SQLSTATE 01000] (Message 50000) Version: 2020-12-31 18:58:56 [SQLSTATE 01000] (Message 50000) Source: https://ola.hallengren.com [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2023-10-20 00:00:00 [SQLSTATE 01000] (Message 50000) Database: [BizTalkDTADb] [SQLSTATE 01000] (Message 50000) State: ONLINE [SQLSTATE 01000] (Message 50000) Standby: No [SQLSTATE 01000] (Message 50000) Updateability: READ_WRITE [SQLSTATE 01000] (Message 50000) User access: MULTI_USER [SQLSTATE 01000] (Message 50000) Recovery model: SIMPLE [SQLSTATE 01000] (Message 50000) Is accessible: Yes [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2023-10-20 00:00:01 [SQLSTATE 01000] (Message 50000) Database context: [BizTalkDTADb] [SQLSTATE 01000] (Message 50000) Command: ALTER INDEX [dta_DebugTrace_index_uidInstanceId] ON [dbo].[dta_DebugTrace] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1, RESUMABLE = OFF) [SQLSTATE 01000] (Message 50000) Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: No, PageCount: 1523, Fragmentation: 88.7722 [SQLSTATE 01000] (Message 50000) Outcome: Succeeded [SQLSTATE 01000] (Message 50000) Duration: 00:00:04 [SQLSTATE 01000] (Message 50000) Date and time: 2023-10-20 00:00:05 [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2023-10-20 00:00:05 [SQLSTATE 01000] (Message 50000) Database context: [BizTalkDTADb] [SQLSTATE 01000] (Message 50000) Command: ALTER INDEX [IX_dta_DebugTrace_TimeStamp] ON [dbo].[dta_DebugTrace] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1, RESUMABLE = OFF) [SQLSTATE 01000] (Message 50000) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: No, PageCount: 2700, Fragmentation: 90.6296 [SQLSTATE 01000] (Message 50000) Outcome: Succeeded [SQLSTATE 01000] (Message 50000) Duration: 00:00:01 [SQLSTATE 01000] (Message 50000) Date and time: 2023-10-20 00:00:06 [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2023-10-20 00:00:06 [SQLSTATE 01000] (Message 50000) Database: [BizTalkMgmtDb] [SQLSTATE 01000] (Message 50000) State: ONLINE [SQLSTATE 01000] (Message 50000) Standby: No [SQLSTATE 01000] (Message 50000) Updateability: READ_WRITE [SQLSTATE 01000] (Message 50000) User access: MULTI_USER [SQLSTATE 01000] (Message 50000) Recovery model: SIMPLE [SQLSTATE 01000] (Message 50000) Is accessible: Yes [SQLSTATE 01000] (Mes... The step failed.
- This topic was modified 1 year, 1 month ago by lan2022nguyen.
-
As you have "LogToTable" enabled, query the command log table and return anything which doesn't have an error code of 0, this will give you what failed and the error number so you can investigate the issue.
select * from commandlog where errornumber <> 0 order by starttime desc
-
The agent log posted above mentioned about [BizTalkMgmtDb], but the commandlog below is showing another database.
This is from the commandlog for this date:
ALTER_INDEX
1205
Transaction (Process ID 97) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Command
ALTER INDEX [PK_ABC_CASE] ON [dbo].[ABC_CASE] REORGANIZE WITH (LOB_COMPACTION = ON)
-
The agent log is the whole thing that the command ran, so its ALL the history of the run, not just the failure.
When you run index optimise manually you see the whole output it runs, that is what the agent sees also and reports that back, you can't trust it will have the error as its only the first 5000 characters or something, so you need to check the commandlog.
OK, so you had a deadlock, you would need to look through the system_health extended event and find the deadlock to see what was being locked.
But you may be best to stop reorganizing, and potentially even rebuilding and just focus on statistics maintenance.
I'm sure Jeff will see this and give you the reasoning behind not reorg/rebuilding, but you can search the forums for his replies on such topics also.
-
thanks for your clarification/assistance. i still have alot to learn about performance/indexing.
- lan2022nguyen wrote:
thanks for your clarification/assistance. i still have alot to learn about performance/indexing.
For starters, the defaults that are built into Ola's scripts are based upon Microsoft's recommendations. Guess what. MS no longer recommends to reorg at 5% fragmentation and rebuild at 30% fragmentation. On Ola's website, he describes these recommendations and provides a link to MS. That link does not say anything about the 5/30 recommendations.
That link is here: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017
That article states:
Index maintenance decisions should be made after considering multiple factors in the specific context of each workload, including the resource cost of maintenance. They should not be based on fixed fragmentation or page density thresholds alone.
Running the index maintenance with the defaults is doing exactly what MS is saying not to do!
I would suggest you do a google search for "Jeff Moden Black Arts Index Maintenance" and take the time to watch it. There is a lot of eye opening things in that about index maintenance, and common fallacies that far too many of us have followed.
Simply, Jeff's research has found that reorg does very little and it's not worth doing and the same performance gains you see from rebuilding an index can be realized by updating statistics more frequently.
In my case, I have not reindexed in almost 5 years.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Actually, REORGANIZE does a whole lot...
It causes log file explosions.
It perpetuates fragmentation because it doesn't clear the pages above the Fill FACTOR.
It combines pages at the worst time possible, which is when you actually need freespace above the FILL FACTOR.
It takes a fair bit of CPU for a much longer than REBUILDs.
Also, except for recovering disk space caused by page splits, do REBUILDs in a generic fashion will cause the "Morning After" syndrome of massive page splits unless you're using the correct Fill Factor AND it has a fragmentation pattern that can actually be helped by a lower Fill Factor.
If you check out all of the BBFAATT (pronounced as "bee-bee fat" and is a "Moden-ism" for "Books, Blogs, Forums, Articles, AI, 'Tubes, and Talks and can be bleated like a goat if sarcasm is desired), you'll find lots and lots of people saying that fragmentation CAN affect performance but most don't even try to provide a working code example that proves it does. In fact, I HAVE seen a couple that suggests that index maintenance can actually hurt performance because of fewer pages and the code going single threaded because of that.
As for the 'Tube that Michael was talking about, here's the link. Don't let the name fool you... it's NOT just about Random GUIDs. I just do most of the demos with those because they're the "Poster Child" for fragmentation and we actually fix those while destroying several decades old myths in the process.
https://www.youtube.com/watch?v=rvZwMNJxqVo
Also, be warned! If you're listening with a headset, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25. The advertisements are important to help support this event but I wanted to let you know that they're sudden and they're loud! They WILL lift your headset!
The bottom line is, it's better to do no index maintenance than it is to do it wrong... and if you're using REORGANIZE, you're doing it wrong for all but a very small class of indexes. If you're doing REBUILDs without understanding why each index is fragmented, you're doing it wrong. And, ironically, if you're using Logical Fragmentation Percent for just about anything other than Random GUIDs, you're doing it wrong.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply