Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Rebuild Index Expand / Collapse
Author
Message
Posted Wednesday, December 2, 2009 7:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
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
Posted Wednesday, December 2, 2009 7:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
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
Posted Wednesday, December 2, 2009 8:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:15 AM
Points: 1,159, Visits: 4,659
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
Posted Wednesday, December 2, 2009 10:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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)?
Post #827939
Posted Thursday, December 3, 2009 12:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:08 AM
Points: 490, Visits: 1,348

/* 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
Posted Thursday, December 3, 2009 12:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
Agreed Nagraj
Post #827980
Posted Thursday, December 3, 2009 1:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
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
Posted Thursday, December 3, 2009 6:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 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
Posted Wednesday, December 9, 2009 12:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:15 AM
Points: 1,159, Visits: 4,659
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
Posted Monday, October 18, 2010 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase ««123»»

Permissions Expand / Collapse