March 21, 2016 at 10:00 am
We are using standard edition of SQL Server.
We are doing index rebuild on weekends but I am trying to change index maintenance job to reorg daily based on the index fragmentation.
What will be the effect of index reorganization to the users?
When we are doing reorganization still it is available to the users?
March 21, 2016 at 1:23 pm
What will be the effect of index reorganization to the users?
The reorgs, while they are running, should go un-noticed by the users (application). You can even stop a rerog and continue at another time if you like as it does not require a rollback as a rebuild would.
When we are doing reorganization still it is available to the users?
Yes, reorgs are not designed to block other processes as they do not require a schema mod lock as a rebuild would. Reorgs only defrag at the leaf level of the index so you are not getting as efficient defragmentation as a rebuild would get you. Are only reorgs sufficent enough for your purposes?
Brent Ozar has an excellent article on this very subject that would be worth taking a look at:
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/
If you wanted to reorg or rebuild based on a particular fragmentation level, Ola Hallengren's index script would be very good to implement:
March 22, 2016 at 5:14 am
In Standard Edition, while the index is being rebuilt, it is offline and user access is blocked.
Why are you updating the indexes so frequently? Unless your queries are doing lots of scans, index fragmentation doesn't impact performance much. Instead, what most people are doing with frequent index defrags is getting updated statistics. Skip the defrags and update the statistics more frequently.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 22, 2016 at 6:45 am
ramana3327 (3/21/2016)
We are using standard edition of SQL Server.We are doing index rebuild on weekends but I am trying to change index maintenance job to reorg daily based on the index fragmentation.
What will be the effect of index reorganization to the users?
When we are doing reorganization still it is available to the users?
Yes, reorgs do many small transactions when they move pages around and the index remains on line.
edit: rebuilds on the other hand are offline in standard edition.
March 22, 2016 at 7:13 am
Reorg of indexes is always executed online irrespective of the edition and is a single threaded operation.Writes are not blocked and updates to the underlying tables are allowed.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2016 at 1:37 pm
Business doesn't allow weekdays downtime.
So planning to do only reorganizing daily the fragmented indexes above 5% but rebuild/reorg on weekends depends on %fragmentation.
Previously noticed rebuild indexes ran faster than reorganization also less log space. So I assume Probably Rebuild index is better option if you have enterprise edition.
March 22, 2016 at 6:15 pm
For non-clustered indexes you may create pairs if identical indexes and disable one of them.
Then you run something like this:
IF INDEXPROPERTY(OBJECT_ID('dbo.TableName'), 'IX_Index1_Odd', 'IsDisabled') = 0
BEGIN
ALTER INDEX IX_Index1_Even ON dbo.TableName REBUILD
IF @@ERROR = 0
ALTER INDEX IX_Index1_Odd ON dbo.TableName DISABLE
END
IF INDEXPROPERTY(OBJECT_ID('dbo.TableName'), 'IX_Index1_Even', 'IsDisabled') = 0
BEGIN
ALTER INDEX IX_Index1_Odd ON dbo.TableName REBUILD
IF @@ERROR = 0
ALTER INDEX IX_Index1_Even ON dbo.TableName DISABLE
END
It would not be that easy with clustered indexes.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply