Indexes and Fragmentation in SQL Server 2000 Part 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Joey Doherty » Indexes and Fragmentation in SQL Server 2000...

24 posts, Page 3 of 3

«««1 2 3

Indexes and Fragmentation in SQL Server 2000 Part 1

Rate Topic

Display Mode

Topic Options

Author

Message

Girish Rathi

Posted Wednesday, December 20, 2006 5:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 06, 2007 7:04 AM
Points: 4, Visits: 1

Joe,

I like the way you present this topic step by step. Thanks.
I really learnt much from this article. It is really helpful.

Girish

Post #331760

John Kotuby

Posted Wednesday, December 20, 2006 9:26 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 10:41 AM
Points: 17, Visits: 88

Thanks to both Joe and Billy. We are currently trying to optimize a fairly large production DB, and adding clustered indexes is part of the plan. The database gets steady inserts, updates from 50 workstations that hit primarily 13 tables. We plan to re-create everything from scratch and allocate enough space this time around to accommodate 5 years data input without the DB needing to grow. Fortunately we have lots of disk space to work with. We will then read the data back in from the archived database. We run a nightly maintenance job that rebuilds indexes leaving 20% free space. We have stopped the part of the job that shrinks the database, as we were noticing that there was 0 free space showing in EM. With heavy inserts and updates everyday, 0 free space doesn't seem appropriate.

We also were informed that the Sys Admin at the customer site had not been running ANY type of disk defragmentation in the last 2 years. The RAID 1 drive where our log file resides showed 46% overall fragmentation. The RAID 5 data drive showed 30% fragmentation. Yes I know that RAID 10 is better, but the server can't take any more drives.

Post #331838