SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I do reindexing & defragmentation of my database


How do I do reindexing & defragmentation of my database

Author
Message
mjafar
mjafar
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 131
Nowadays my database is running very slow.

So I have decided to do re indexing & defragmentation.
I have did shrinking of database.
Here first time I am doing re indexing & defragmentation of database.
How do I go for this. Is there any procedure for this task.
What are the commands/stored proc, steps take to consider.


please reply soon __.____._


Regard
Majid
Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5363 Visits: 1585
mjafar (2/5/2008)

Nowadays my database is running very slow.

So I have decided to do re indexing & defragmentation.
I have did shrinking of database.
Here first time I am doing re indexing & defragmentation of database.
How do I go for this. Is there any procedure for this task.
What are the commands/stored proc, steps take to consider.


please reply soon __.____._


Regard
Majid


You could rebuild or defragment your indexes using ALTER INDEX statements. Whether to rebuild, defragment or do nothing, you can decide based on the output of sys.dm_db_index_physical_stats, This is described in a lot of detail in Books Online under http://technet.microsoft.com/en-us/library/ms189858.aspx.

Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
tew
tew
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 318
You can construct an SSIS package from Management Studio to accomplish your goal; remember to schedule the associated Agent Job.

To save time and system resources you may want to start w/ a Reorganize Index task in your package instead of Rebuild Index.
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8068 Visits: 1719
And don't run a shrink of the data files - it will cause fragmentation. I documented this in SS2005 Books Online for DBCC SHRINKDATABASE. See http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx for an example.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220913 Visits: 46279
Refer to OP's other post on this please- Reindexing and defragmentation (SQL 2000)

Apparently server is 2000, post was initially in wrong forum.

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


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