Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild Index


Rebuild Index

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
Ali_SQLDBA (12/2/2009)
Most of my tables are either clusterd or non clustered.


Um, the distinction is clustered or heap. A nonclustered index is a separate structure and can sit on either tables with a clustered index or heaps

so as per your expertise... this is not a big deal for me to worry. Right?

Not for the small tables. For the large ones, it's still something that you need to do.

Also, do you recommend me t run rebuild offline scheduling an outage of the database every week.??

Offline, no. If your system is 24/7, then rebuild the indexes that you can online, if they need rebuilding, and only the ones that have to be rebuilt offline (because of LOB columns) get an offline rebuild.
Use a custom script rather than just a maintenance plan. I'm fond of this one - http://sqlfool.com/2009/06/index-defrag-script-v30/, lots of options to customise how much you rebuild, where the threshold are and other details.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
muthukkumaran (12/2/2009)
Hi,
Read & use the index de-fragmentation script added in my signature.


Why does your script use deprecated objects (that will be removed in future versions) and global temp tables?
Also, if you're using Try .. Catch, you shouldn't be using @@Error. There are updated functions in SQL 2005 for getting error details


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4838
GilaMonster (12/2/2009)
muthukkumaran (12/2/2009)
Hi,
Read & use the index de-fragmentation script added in my signature.


Why does your script use deprecated objects (that will be removed in future versions) and global temp tables?
Also, if you're using Try .. Catch, you shouldn't be using @@Error. There are updated functions in SQL 2005 for getting error details


Gail,Thanks for the update & Suggestion.I ll edit and post Script ASAP.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
Gila - "External fragmentation usually refers to file-level fragmentation that's fixed with a disk defragmentation program."

According to my understanding the external fragmentation is nothing to do with disk fragmentation program. This is simply hip hop between extents because of page split. What's ur PoV (point of view)?
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
/* Offline, no. If your system is 24/7, then rebuild the indexes that you can online, if they need rebuilding, and only the ones that have to be rebuilt offline (because of LOB columns) get an offline rebuild.
*/

Ali,
If you have a downtime/offpeak please rebuild during ur downtime as doing it online is always more expensive than a offline rebuild.
If you dont have any downtime online rebuild is the only option.

Regards,
Raj

http://Strictlysql.blogspot.com
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
Agreed Nagraj
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
Ok, looked around and seems there's both definitions of internal and external fragmentation. I usually use them to mean fragmentation internal to SQL (and fixable within SQL) and fragmentation external to SQL (fixable outside SQL)


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ALI SQLDBA
ALI SQLDBA
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 335
Thank you very much all of you.. I really appreciate you guys for putting out such a great discussion. I have used the script by muthukumaran and studied the results. The results were convincing.

Actually, I am referring to two different databases on the same instance here. Both of them are mission critical and at the same time, Application team has agreed for a weekly outage of two hours on both the databases. So, we can do the rebuild offline.

Now, I have a small concern here... While executing the stored procedure on one of my databases on a test server.. I have noticed CPU usage was maxed out. usually, this box sits at 45-50% CPU usage.. Also when this SP was running, I had none other than me logged into this machine... any comments on its effect on my prod server...
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4838
muthukkumaran (12/2/2009)
GilaMonster (12/2/2009)
muthukkumaran (12/2/2009)
Hi,
Read & use the index de-fragmentation script added in my signature.


Why does your script use deprecated objects (that will be removed in future versions) and global temp tables?
Also, if you're using Try .. Catch, you shouldn't be using @@Error. There are updated functions in SQL 2005 for getting error details


Gail,Thanks for the update & Suggestion.I ll edit and post Script ASAP.



Now the article has been published.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

tina 2565
tina 2565
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
Hi guys, I have a question for you:

I am doing an index rebuild offline weekly.
I have a Service that runs 24/7 and inserts articles in the db.
Do I need to run a script that will stop this Service during the offline rebuild?
Is there a queue that is generated with all the requests or because of the lock on those tables I will just have some errors?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search