Solved

SQL Server Rebuild indexes

Posted on 2014-01-15
6
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 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