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

Reindexing & defragmentation Expand / Collapse
Author
Message
Posted Monday, February 18, 2008 3:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131
Hi all,

Here on daily basis large number of updates/inserts/fetching are happening on database.

On weekly basis I am reindexing & after that defragmenting my data to improve performance.
Is it necessary/good to do defragmentation after reindexing.

If yes then I just have to ask, what i have to do first Reindexing & defragmentation or defragmentation & reindexing or it doesnt matter what is the sequence.
If No, plz tell me difference in defragmentation & reindexing.

Plz reply soon..

Regards
Majid
Post #456839
Posted Monday, February 18, 2008 9:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:25 AM
Points: 33,267, Visits: 15,433
How are you reindexing? There is reindexing and rebuilding, which operate differently. Read the BOL entries and you'll see that one handles fragmentation.

How do you defragment? You rebuild the index.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #456972
Posted Monday, February 18, 2008 11:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131
For rebuilding indexing I used DBCC DBREINDEX and for defragmentation DBCC INDEXDEFRAG

Is it necessary to use both commands or just using DBCC DBREINDEX i can do reindexing as well as defragmentation.



Post #457215
Posted Tuesday, February 19, 2008 12:35 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 @ 11:19 AM
Points: 43,002, Visits: 36,158
Reindex completely rebuilds the index, removing fragmentation at all levels.
Index Defrag shuffles the leaf pages into order, removing fragmentation at the leaf levels, but potentially leaving fragmentation at the other levels of the index.

Doing a defrag right after a reindex is redundant and a waste of time and server resources.



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 #457234
Posted Tuesday, February 19, 2008 1:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131


If dbcc dbreindex rebuilds indexes also defragment the data then is it necessary to use DBCC INDEXDEFRAG ?

If I am using DBCC INDEXDEFRAG & after that dbcc dbreindex then is it ok?

plz reply...

Regards
Majid
Post #457236
Posted Tuesday, February 19, 2008 2: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 @ 11:19 AM
Points: 43,002, Visits: 36,158
Doing a reindex right after a defrag is redundant and a waste of time and server resources

A reindex does all that a defrag does and more.

You would typically use a defrag is you have only a small window (it's often faster than a reindex) or you need the table accesssible during the defrag. (on SQL 2000, an index rebuild is an offline operation)



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 #457274
Posted Tuesday, February 19, 2008 3:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131
ok..ok..
got it..

I will use DBCC dbreindex once a week..

Thanks a lot Gail for guidance.
Post #457288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse