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

How do I do reindexing & defragmentation of my database Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2008 4:07 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

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
Post #451520
Posted Tuesday, February 5, 2008 4:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #451527
Posted Tuesday, February 5, 2008 3:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 4, 2012 11:40 AM
Points: 89, 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.
Post #451937
Posted Tuesday, February 5, 2008 11:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 4:00 PM
Points: 2,038, Visits: 1,663
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
Post #452026
Posted Tuesday, February 5, 2008 11:57 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 42,458, Visits: 35,518
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 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 #452029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse