4

I have few tables with indexes that are highly fragmented (over 90% fragmentation) and are big enough (tens of gigabytes) in size. Goal of defragmenting those is to reduce space consumption by above tables.

I would like to avoid performing REBUILD operations, as it is too resource intensive. Also trying to avoid logging to transaction log and log growth, and big transaction log backup sizes as the result.

That is why REORGANIZE operation was chosen instead of REBUILD, to deal with high fragmentation.

Regarding REORGANIZE, have below questions:

  1. If run multiple REORGANIZE operations on separate indexes at the same time (say 10+, in separate SSMS windows/sessions) - can it cause data corruption or any other known issues?

  2. Are REORGANIZE operations logged to transaction log in any way / at all?

  3. What kind of locks are held by REORGANIZE ? Which queries can suffer from this?

The only reason for defragmentation in my case is to reduce storage space consumption, and it really helps (for example index was 18 GB before and then became 8 GB afterwards, and so on). This is easier way out than ordering additional storage, for my case.

0

1 Answer 1

10

If run multiple REORGANIZE operations on separate indexes at the same time (say 10+, in separate SSMS windows/sessions) - can it cause data corruption or any other known issues ?

No it would not. Regarding any other issues AFAIK if your SQL Server is patched to latest SP and CU you should be pretty much safe.

Are REORGANIZE operations logged to transaction log in any way / at all ?

Yes they are logged in fact logged in all recovery model. Quoting from SQLskills.com

In all recovery modes, reorganizing an index is fully logged, but is performed as a series of small transactions so should not cause the transaction log to grow inordinately. And of course, transaction log is only generated for the operations performed, which may be less for a reorganize as it only deals with fragmentation that exists.


What kind of locks are held by REORGANIZE ? Which queries can suffer from this ?

Quoting again from Paul Randals blog shared above

An index reorganize holds an intent-exclusive table lock throughout the operation, which will only block shared, exclusive, and schema-modification table locks. One of the major reasons I wrote DBCC INDEXDEFRAG for SQL Server 2000 was as an online alternative to DBCC DBREINDEX.

Also trying to avoid logging to transaction log and log growth, and big transaction log backup sizes as the result

Please note as said above index reorganize is fully logged and hence the logs would be generated but since SQL engine works on one database page at time so log growth will not bloat extensively and would be in control.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.