patternsqlModerate
ALTER INDEX REORGANIZE questions
Viewed 0 times
indexquestionsalterreorganize
Problem
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:
-
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?
-
Are REORGANIZE operations logged to transaction log in any way / at all?
-
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.
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:
-
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?
-
Are REORGANIZE operations logged to transaction log in any way / at all?
-
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#298888, answer score: 10
Revisions (0)
No revisions yet.