SQL Server Resumable Index Operations

Question

SQL Server Resumable Index Operations

Almighty

SSCommitted

Points: 1969
More actions
June 26, 2023 at 12:00 am

#4214566

Comments posted to this topic are about the item SQL Server Resumable Index Operations
March 8, 2024 at 10:09 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jshell687 Grasshopper Points: 11 More actions · Answer 1

Can you add the resumable part to a current index? Or do you have to add this once you create the index from the beginning?

Almighty SSCommitted Points: 1969 More actions · Answer 2

Almighty

SSCommitted

Points: 1969

June 27, 2023 at 7:40 am

#4220424

recreate the index with resumable option ON .

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

How often are you reindexing where this feature provides a benefit? We re-index so infrequently. I can't see this providing much of a benefit.

Good article though!

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/

Almighty SSCommitted Points: 1969 More actions · Answer 4

Thanks

How large is database and how frequent data is updated/deleted in tables ?

for larger index indexing takes time and this is useful feature in that case

Michael L John One Orange Chip Points: 27038 More actions · Answer 5

Almighty wrote:

Thanks
How large is database and how frequent data is updated/deleted in tables ?
for larger index indexing takes time and this is useful feature in that case

Well, like I said, we do not reindex very often regardless of the size of the table/index. There is one table that's part of a highly transactional system that needs to be reindexed regularly or else the clustered index grows significantly, But, without looking in my log tables, it's been months were any other reindexing occurred.

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/

danrcarollo Grasshopper Points: 14 More actions · Answer 6

One thing we've observed. Using the RESUMABLE option makes the index significantly longer to create/rebuild.

Here are some tests comparing traditional ONLINE with SORT_IN_TEMPDB on for a CLUSTERED index rebuild. I'm guessing this is due to some of the overhead, plus not being able to use SORT-IN_TEMPDB option with RESUMABLE

I was a bit surprise at the extra amount of time it took to complete when I first paused the index build.

ALTER INDEX PKTableName ON tblTableName REBUILD WITH (ONLINE=ON,MAXDOP=4,SORT_IN_TEMPDB=ON)

--Four test iterations
--56 seconds
--45 seconds
--50 seconds
--47 seconds

ALTER INDEX PKTableName ON tblTableName REBUILD WITH (ONLINE=ON,MAXDOP=4,RESUMABLE=ON)

--Without Pausing..
--1:19
--57 seconds
--58 seconds
--55 seconds

ALTER INDEX PKTableName ON tblTableName RESUME


--With PAUSE and RESUME :
--Paused at 20 seconds. 6:07 minutes after RESUME
--Paused at 20 seconds. 1:28 minutes after RESUME
--Paused at 24 seconds. 39 seconds after RESUME: Total: 63 seconds
--Paused at 21 seconds. 3:25 minutes after RESUME

Finally, here's the OFFLINE version for comparison, which was an order of magnitude faster. However, this may not be acceptable in production systems unless you can get an agreed downtime window.

ALTER INDEX PKTableName ON tblTableName REBUILD WITH (ONLINE=OFF,MAXDOP=4,SORT_IN_TEMPDB=ON)
--13 seconds
--12 seconds
--13 seconds
--14 seconds

Michael L John One Orange Chip Points: 27038 More actions · Answer 7

What reason do you need to rebuild these indexes?

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/