MS-SQL Server - ReOrganize

MS-SQL Server - ReOrganize

To prevent blocking during Index Maintenance we can change the settings prior to execute Reorganize / Rebuild.

Use MyDB

ALTER INDEX [PK_Employee] ON [dbo].[Employee] SET ( ALLOW_PAGE_LOCKS = ON )
ALTER INDEX [PK_Employee] ON [dbo].[Employee] SET ( ALLOW_ROW_LOCKS = ON )

But please also confirm the same from Microsoft before applying this technique on production.

But please ensure to revert after completion.

Additionally,

Lock Escalation

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/resolve-blocking-problems-caused-lock-escalation

Error Log Cycle

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-errorlog-transact-sql?view=sql-server-ver16

Wait Types

CXCONSUMER
CXPACKET

However, most of the

PREEMPTIVE Type of waittypes are for Internal use only.

Eg.
PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO

Best Regards

T. Amit Vaid S.

विचार की सदा हो जय!