Hi!!! 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
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
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 ------------------
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 http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing.
