Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server Rebuild indexes

Posted on 2014-01-15
6
Medium Priority
?
377 Views
Last Modified: 2014-03-21
We have an issue when running a db maintenance plan which includes an Index rebuild. Normally the whole process takes about 20 minutes. Sometimes however, it can take 2-3 hours. This causes the applications to freeze. There is very little activity on the database at that
time.

Any help would be appreciated

Many thanks

Rob
0
Comment
Question by:Morpheus7
6 Comments
 
LVL 20

Expert Comment

by:strivoli
ID: 39782067
Did you consider the option "Keep Index Online"?
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 1336 total points
ID: 39782312
Doing online index rebuilds is an enterprise edition feature, so this may not be an option. However, you can always do an index REORGANIZE online, without impact queries that are running or blocking anybody. Also, SQL Server may be rebuilding/reorganizing indexes when they're not actually needed because they're not sufficiently fragmented - also, since it starts at the beginning each time, it may not ever get to the tables/indexes at the end of the list.

If you want some more control over index maintenance, I'd suggest you check out some of the more advanced maintenance scripts available, rather than using the standard "rebuild indexes" step in the maintenance plans. For example, Ola Hallengren's scripts are probably the most well known example of such customizations:

http://ola.hallengren.com/
0
 

Author Comment

by:Morpheus7
ID: 39784690
Many thanks for the reply. I will check out the scripts.  Do we know why this happens every so often?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 20

Expert Comment

by:strivoli
ID: 39784986
Maintenance tasks such as this one can be run scheduled with a needed periodicity (daily, weekly, monthly, ...). Index Rebuilding might be run weekly... perhaps daily is too often, but that depends on your environment.
Index Rebuilding is a very intensive task and there's no surprise front-end software suffer. This is why such tasks should run during nights or week-ends.
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 1336 total points
ID: 39788826
The index rebuild means that the table may be rescanned and generally means that there's a tremendous amount of disk acitivity associates - out-of-order pages in the index are moved, page splits are corrected, and additional free space is added according to the FILLFACTOR setting. All of these mean disk activity, and it will compete with regular application usage. For a REORGANIZE, by contrast, it moves data around inside the index to better organize things, but doesn't recreate the index from scratch during the process.

I'd agree that these operations should preferrably run after-hours - while a reorg during the day isn't a big deal and can probably run in the background, I'd save a rebuild until the system is otherwise quiet.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 664 total points
ID: 39789091
Hi

If this iis the first time running something like this on this database then I wouldn't be running one of these scripts or jobs. Too likely for said job to run too long.

I'd select a clustered index that's very fragmented, large but not huge, and rebuild just that.

This should give some improvement, enough to be noticeable, and give a feel for time to run entire job.

Free disk. Rebuilds  require free disk and leave up to 1.4 times the finished size of the index as free space in the database. So have you got instant file initialization turned on? Have you got enough disk?

Whatever you do, don't shrink after an index rebuild! You'll just undo all your hard work!

HTH
 David
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question