Solved

SQL Server Rebuild indexes

Posted on 2014-01-15
6
360 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now