Indexes and Fragmentation in SQL Server 2000 Part 1

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

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

  • 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



  • 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.




    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 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.


  • Joe,

    I like the way you present this topic step by step. Thanks.

    I really learnt much from this article. It is really helpful.


  • 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.

  • Good Article, looking forward to have next..

  • Hey Cort Ellingson - email me !!! Victor Champney

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

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