index keeps going bad

Hi All
I am running on SQL Server 2000 SP3.
I have a deduction table which might have a row added here and there
throughout a month and all is fine. But once a month we run a process
which inserts 25,000 rows. For the last five months, every time that
process was run, a select which normally takes less than a second
would take a minute and a half. If I recreate the index on the table
the select goes back down to less than a second.
That is my fix for it but I don't know if that is just inadvertently
fixing it or it is truly the index which is the problem. Checkdb and
checktable show no problems before I recreate the index.
This is the result of showcontig while slow:
- Pages Scanned........................: 68066
- Extents Scanned.......................: 8600
- Extent Switches.......................: 27218
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 31.26% [8509:27219]
- Logical Scan Fragmentation ..............: 18.59%
- Extent Scan Fragmentation ...............: 15.12%
- Avg. Bytes Free per Page................: 1198.4
- Avg. Page Density (full)................: 85.19%
This is the result of showcontig when good:
- Pages Scanned........................: 58283
- Extents Scanned.......................: 7334
- Extent Switches.......................: 7336
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [7286:7337]
- Logical Scan Fragmentation ..............: 2.19%
- Extent Scan Fragmentation ...............: 8.36%
- Avg. Bytes Free per Page................: 40.5
- Avg. Page Density (full)................: 99.50%
Any suggestions on what might be causing this?
Or what I might do to find the problem?
Thanks,
MGBIn message <7341bcd.0409291012.4746ecdc@.posting.google.com>, MGB
<groups@.beu.hm> writes
>Scan Density [Best Count:Actual Count]......: 31.26% [8509:27219]
This would suggest to me that the table in question has a Primary Key
which is Clustered and you have left no space in the table for normal
growth through your import of 25000 transactions.
This typically happens when you use a GUID column as a Clustered Primary
Key. As a GUID is randomly created it could appear anywhere in the index
and hence more Extents and Pages are created in order to keep the
Clustered Index ordered properly (remember that a Clustered index means
the data is also physically stored in the same order - witch in turn
increases the load on the server). If the Primary Key was an AutoNumber
column then the Insert would always be on the last page of the last
extent, in laymans terms.
There are several solutions:
1) Never create a Clustered Index on a GUID column unless the data in
that table is almost static.
2) After step 1 adjust the index's Fill Factor to create space for your
25000 Inserts.
3) Schedule a rebuild of the index's once a month to keep things sweet.
You don't need to keep Droping the index, try Scheduleing the function
below.
--
CREATE PROC TWRebuild AS
DECLARE @.TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE @.Command VARCHAR(255)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @.TableName
DBCC DBREINDEX(@.TableName)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
If you provide more information about the Table structure, Index's and
Constraints we could be a little more accurate about the problem.
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com