Solved

SQL Server Rebuild indexes

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

728 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