SQL Server Performance

Small Table Clustered Index

Discussion in 'Performance Tuning for DBAs' started by seasider, Mar 5, 2003.

  1. seasider New Member

    I am about to create a table that will be used continuously for inserting into and deleting from. Rows will be inserted into it, a stored procedure will be called that will maniuplate the Rows and then the rows will be deleted.

    My question is concerning the creation of a clustered index and the re-use of data pages. If I create a clustered index on an ID field and insert a few hundred rows that take up 3 pages (for example). Then I delete the first 50 of those rows and add another 50.

    Where will the last 50 rows that I added be stored? Will new pages be used at the end of the clustered index? or will it re-use the data pages that were freed up when I deleted the 50 rows?

    I am thinking that DBReindex would have an effect on the answer here and whether it was called before I added the final 50 rows.
  2. bradmcgehee New Member

    Previous pages will be reused when they become available. This, of course, will cause physical fragmentation of the data (not logical), which in the long run will hurt performance because I/O will be more random and the read-ahead ability won't be able to fully work. This means that you should reindex the tables often to minimize the physical data fragmentation.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. satya Moderator

    Make sure to run DBCC checks on regular basis to get optimum performance.

    Satya SKJ
  4. gaurav_bindlish New Member

    Hey guys... I think we have missed a point. Seasider mentioned that the clustered index will be on id column. Now if this is a identity column or eveincreasing value, then the existing pages will not be reused and the insertion will be at end only. As Brad and Satya said, there will be data defragmentation and DBCC checks will be required on regular basis. But if the clustered index is made in such a way that the deleted and inserted rows will be distributed across all the pages, the page reuse is possible.

    Gaurav
  5. bradmcgehee New Member

    You are right gaurav_bindlish. We need more info to make a good recommendation.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page