SQL Server Performance

Why some Indexes increase fast?

Discussion in 'General DBA Questions' started by halmeida, Oct 18, 2006.

  1. halmeida New Member


    I need some words from experts because I am newbie with SQL SERVER.

    I have a third-party application and some tables have indexex that increase fast.

    I have a script to drop and create these indexes but some increase fast.

    Does anyone have idea or point me to a link explaining this?

    Heberson Sette de Almeida
  2. Chappy New Member

    When you say 'an index that increases fast', do you mean they increase in size?

    Are the tables receiving lots of inserts/updates ? Maybe your indices are becoming fragmented a lot.
    see DBCC INDEXDEFRAG in Books Online if thats the case

    Also check the fill factor on the index is set to a suitable value (this would depend on the index type, fields, and 'volatility' of the table). Again see Books Online if you dont understand what fill factor is for

  3. xiebo2010cx Member

    If the size of index increase fast. the potential problem is:

    1. The table got lots of new rows and getting bigger
    2. The fragmentation is becoming worse
    3. You have too many non-clustered indices, and you index key or keys are too big in size.

    General, clustered index contains table data, the storage of B-Tree structure of index pages is very small compared to the data itself

    Nonclusted indices will have one index record with bookmark info for each data row in their leaf page, if you indexed on large column or columns, or your clustered index key or keys are large. the non-clustered index may be very, very large. Moverover, one table can have many nonclustered indices.

    hope this help

    Bug explorer/finder/seeker/locator
  4. satya Moderator

    As a newbie to understand the index strategy refer thru the book Inside SQL Server 2000 by Kalen Delaney.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page