Search

Index Reorganize time limit by Greg Low - Australia

Closed
as Won't Fix Help for as Won't Fix

10
0
Sign in
to vote
Type: Suggestion
ID: 611158
Opened: 10/6/2010 3:06:59 PM
Access Restriction: Public
1
Workaround(s)
It would be useful to be able to specify a maximum time limit for index reorganize. For example, I'll do an hour's worth tonight and do some more tomorrow night.
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

Index reorg should allow you to specify a time limit.

Benefits

Improved Administration
Improved Performance

Other Benefits

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by D Parks on 1/5/2012 at 12:08 AM
This would be so beneficial in our OLTP environment where we simply can't afford Enterprise Edition for online rebuilds. I strongly urge Microsoft to reconsider this proposed feature. It can't be that difficult to implement.
Seems like a work-around would be to set the timeout value prior to issuing the reorg statement. So behind the scenes the implementation could just temporarily set that session timeout value.
Posted by Dan.Frome on 1/4/2012 at 11:21 AM
This should be implemented, having the ability to stop this operation would greatly increase the benefit of index reorganize where you have small (but maybe frequent), maintenance windows.
Posted by MichaelSmith on 12/21/2011 at 10:47 AM
I completely agree that this is a fantastic idea. I would also add that it would be nice to specify a transaction log consumption limit (percentage or absolute kb/mb/gb/tb). With mirroring and availability groups, and maintenance windows, very worthwhile suggestion!
Posted by Paul White NZ on 12/20/2011 at 10:09 PM
Voted up as a useful enhancement to the language. Borrowing Aaron Bertrand's example syntax:

ALTER INDEX foo
ON dbo.bar
REORGANIZE WITH (TIME_LIMIT = '00:45:00');

This is much neater than the workaround (a job to kill a job).
Posted by Greg Low - Australia on 12/19/2011 at 4:27 PM
Sorry, I didn't quite follow the comments. As far as I'm aware, Index rebuild is transactional and will be rolled back if interrupted but Index reorg isn't transactional and won't be rolled back if interrupted.
Then, it makes complete sense to have a time limit.
Many customers can benefit from index reorganization but have limited time windows for this work. It shouldn't be necessary to build a job to find and kill another job, just to be able to specify that "I'd like to do an hour's work of reorg tonight".
Posted by Microsoft on 12/19/2011 at 9:53 AM
Greg,

Good suggestion. However, index reorganization is a transactional operation, so any work completed before a "cancel" is issued will be complete and not rolled back. Can you implement a script that automatically cancels the operation after some period of time?

Thanks,
--R
Sign in to post a workaround.
Posted by Dan.Frome on 1/25/2012 at 8:01 AM
The only way I've been able to accomplish this, is to have the reorganize task in a job, and have a second job that fires off after the first (e.g. scheduled to run an hour after) that runs EXEC dbo.sp_stop_job N'Job Name 1'.

Be a whole lot better to just have this in the reorganize.