<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Rebuild Index
21 posts, Page 2 of 3
««
1
2
3
»»
Rebuild Index
Rate Topic
Display Mode
Topic Options
Author
Message
GilaMonster
GilaMonster
Posted Wednesday, December 02, 2009 7:57 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 38,099,
Visits: 30,393
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 2008, MVP
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
Post #827491
GilaMonster
GilaMonster
Posted Wednesday, December 02, 2009 7:59 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 38,099,
Visits: 30,393
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 2008, MVP
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
Post #827494
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Posted Wednesday, December 02, 2009 8:06 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 11:52 AM
Points: 1,126,
Visits: 4,434
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
Post #827501
luckysql.kinda
luckysql.kinda
Posted Wednesday, December 02, 2009 10:24 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310,
Visits: 649
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)?
Post #827939
arr.nagaraj
arr.nagaraj
Posted Thursday, December 03, 2009 12:04 AM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 483,
Visits: 1,201
/* 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
Strictlysql.blogspot.com
Post #827966
luckysql.kinda
luckysql.kinda
Posted Thursday, December 03, 2009 12:40 AM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:41 PM
Points: 310,
Visits: 649
Agreed Nagraj
Post #827980
GilaMonster
GilaMonster
Posted Thursday, December 03, 2009 1:44 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 38,099,
Visits: 30,393
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 2008, MVP
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
Post #828000
ALI SQLDBA
ALI SQLDBA
Posted Thursday, December 03, 2009 6:56 AM
Valued Member
Group: General Forum Members
Last Login: Monday, March 07, 2011 1:23 PM
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...
Post #828126
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Posted Wednesday, December 09, 2009 12:04 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 11:52 AM
Points: 1,126,
Visits: 4,434
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
Post #831190
tina 2565
tina 2565
Posted Monday, October 18, 2010 9:32 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 20, 2010 9:37 AM
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?
Post #1006317
« Prev Topic
|
Next Topic »
21 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
