Solved

SQL Server Rebuild indexes

Posted on 2014-01-15
6
372 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 19

Expert Comment

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

Accepted Solution

by:
Ryan McCauley earned 334 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 19

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 334 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 166 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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