[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Rebuild and Reorganize Index

Posted on 2013-05-13
8
Medium Priority
?
607 Views
Last Modified: 2013-05-16
In SQL 2008, is it necessary to rebuild and reorganize the index under the maintenance Plans.

If so, which one should rebuild periodically?  Should I Rebuild before Reorganizing?  

What is the main difference between these two tasks?  Are there any dangers to doing this?

I will be using the maintenance wizard to implement this, so any advice on the above questions would be helpful.

Thanks
0
Comment
Question by:al4629740
  • 4
  • 2
  • 2
8 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1600 total points
ID: 39163154
Yes you should schedule jobs to do that, I wont recommend maiintenance plans though, use the script mentioned here
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

differene b/w reindex and organie can be found here
http://technet.microsoft.com/en-us/library/ms189858(v=sql.105).aspx
0
 

Author Comment

by:al4629740
ID: 39163216
Why not the maintenance plans?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39163227
>Why not the maintenance plans?
You cannot really specify when to go for index reorganise and rebuild. Also i find it really hard to debug. The solution on that link is excellent most of the top SQL gurus uses that.
0
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!

 

Author Comment

by:al4629740
ID: 39163278
Would I run this code without modification?

http://ola.hallengren.com/scripts/IndexOptimize.sql

I am apprehensive about running any scripts that change something that I don't fully understand.
0
 

Author Comment

by:al4629740
ID: 39163308
Also, in my original question, which would be better to do first?

Reorganize or rebuild?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 39163507
Would I run this code without modification?
All the code does is create a Stored Procedure called IndexOptimize, it is up to you how you want to execute it.

I am apprehensive about running any scripts that change something that I don't fully understand.
And how is that different from a Maintenance Plan?  Do you have any idea what it is doing?

Also, in my original question, which would be better to do first?
Reorganize or rebuild?

It depends.  You use one or the other not both.  Have you read the link that Aneesh posted on the subject?
0
 

Author Comment

by:al4629740
ID: 39163549
ok.  How does one determine the avg_fragmentation_in_percent
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39164497
How does one determine the avg_fragmentation_in_percent
It is covered in that same article that Aneesh posted.  Let me know if you are having difficulty reaching that link.
0

Featured Post

Industry Leaders: 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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

872 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