Indexes and Fragmentation in SQL Server 2000 Part 1

Question

Indexes and Fragmentation in SQL Server 2000 Part 1

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Steve Calverley SSC Rookie Points: 25 More actions · Answer 1

Thank you *very much* for a really interesting and useful article and I'll be watching closely for more installments.

Reecha Mishra SSC Enthusiast Points: 177 More actions · Answer 2

It was a great article!!! I am waiting for the next article as this gives ignition to all thoughts regarding Fragmentation.

dbaforever Hall of Fame Points: 3740 More actions · Answer 3

Thanks for an enlightening article, Joe.

You state: "Running any form of query on a heap will force a full scan of the table and could take a considerable amount of time. And the results from the DBCC SHOWCONTIG command, which is a tool for measuring fragmentation, is also of little use on a heap. "

I want to make sure I understand correctly. If TableA has a non-clustered index on ColumnA, but no clustered indexes..... and I select * from TableA Where ColumnA = 'abc'......... a table scan will be performed? I didn't know that. I thought by having an index on the column I'm querying on, that was sufficient to prevent a table scan (unless the table only has a few rows of course). Please elaborate.

Thanks, John

Joe Doherty-260822 SSC Eights! Points: 915 More actions · Answer 4

John,

You are right to point that out. Perhaps I should have made it clear that we were dealing with data that had NO indexes applied (clustered and non-clustered).

With part 1 of the series I simply wanted to get people thinking in terms of a heap of data stored in data pages. And to also introduce a few basics we could build on.

As you will see in the following parts we will introduce clustered and non-clustered indexes.

Joe

Laxma Reddy P SSC Rookie Points: 37 More actions · Answer 5

Joe,

Thanks alot for nice article. Would like to go for next part of this with not much gap. Hope you will post it very soon. By the way, I have question here...how data stores if we define a clustured index on a column which contains duplicate data and how it results when we SELECT it..please clarify.

Onceagain thanks for spending time for this good work.

-LRP

Girish Rathi SSC Rookie Points: 32 More actions · Answer 6

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

John Kotuby Ten Centuries Points: 1079 More actions · Answer 7

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.

binu john SSCommitted Points: 1987 More actions · Answer 8

binu john

SSCommitted

Points: 1987

March 29, 2007 at 3:29 am

#697595

Good Article, looking forward to have next..

dataguy2202 Newbie Points: 3 More actions · Answer 9

Hey Cort Ellingson - email me !!! Victor Champney vwchampney@aol.com