Our production server is currently running SQL Server 7.0
sp3. We have a table that is inserted to, and deleted from
all the time. Data is selected from it as well. There is a
non clustered index on the primary key fields (2). We have
a few stored procedures that insert a bunch of records
into this table, select and insert those records into
another table, then delete these records when the insert
into the second table has completed successfully. This
process is enclosed in a transaction block.
What I've been seeing is the size of the table grows
continuously over a period of time, when I actually have
to drop / recreate the table to keep it manageable. I've
run a dbcc showcontig and find that the scan density is
usually up around 98%, but the average page density is
always under 2%! It's almost as though there is only a few
records per data page. The record size is only 217 bytes
(not including overhead), so I don't think this is the
reason. However, in trying to find more information about
internal fragmentation, which is what the page density
represents, I've only found information that indicates
that this is caused by a large record size.
Because there is no clustered index on this table, records
are inserted to a heap. Putting a clustered index on the
table seems to clear up the continuous table growth, but I
am concerned that many concurrent processes trying to
insert records with a key containing sequential integer values will create data page 'hot spots',affecting the performance of the stored procedures that are involved.
I would appreciate any insight into this behavior. Records
are constantly being added and then deleted from the
table. According to the dbcc showcontig output, it appears
that it's the data pages that are fragmented. Is this true?
Let me also add that there is a fill factor of 90% on the non clustered index.
Even without the clustered index you are getting a 'hot page', like you said the table is a heap and all data is being added to the end. Also when the data is deleted from the heap that space will not be reused since all new inserts are added to the end of the table. I would look at creating a clustered index on another column(s), something not sequential.
Is this data being searched and if so by what column(s) and are these searches range searches (LIKE, BETWEEN or ex. Lastname='Smith'), if so then these column(s) may be good candidates for the clustered index.
You may want to set your fillfactor to something lower 50-60%, play with it. FYI, the fillfactor is only used on the intial creation of the indexes any page splits I believe uses a fillfactor of 75%, not quite sure on that number.