July 29, 2003 at 11:55 am
Within the last 2 weeks our system's interactive performance has dramatically gotten slower on our SQL Server 7.0 DB. There has been no unexpected DB growth. Its key tables have grown 2% in the last month. There are 12 tables with over a million records. I have run sp_Udatestats and have been running DBCC DBREINDEX on the largest files with no effect on the performance problem. When I run DBCC SHOWCONTIG on these large files. The sample below is typical.
DBCC SHOWCONTIG scanning 'CM20100' table...
Table: 'CM20100' (1109578991); index ID: 0, database ID: 20
TABLE level scan performed.
- Pages Scanned................................: 13065
- Extents Scanned..............................: 1641
- Extent Switches..............................: 1640
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.57% [1634:1641]
- Extent Scan Fragmentation ...................: 99.21%
- Avg. Bytes Free per Page.....................: 330.0
- Avg. Page Density (full).....................: 95.92%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Index (ID = 2) is being rebuilt.
Index (ID = 3) is being rebuilt.
Index (ID = 4) is being rebuilt.
Index (ID = 5) is being rebuilt.
Index (ID = 6) is being rebuilt.
Index (ID = 7) is being rebuilt.
Index (ID = 8) is being rebuilt.
Index (ID = 9) is being rebuilt.
Index (ID = 10) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system ad
From the literature on the subject my interpretaion of the Extent Scan Fragmentation property is that I have a performance issue here. However, when I run the following command: DBCC DBREINDEX('CM20100',' ' ,10) it has no effect on any of the above parameters.
Am I interpreting this parameter correctly?
Why doesn't the DBREINDEX command have any effect on these parameters? For example I expected the Ave. Page Density to drop to 90% based on the 10% fillfactor I inputted in the DBREINDEX command.
Any other suggestions for tracking down performance issue?
July 29, 2003 at 3:22 pm
Do you have a clustered index? I may be wrong (and often am), but "index ID: 0" suggests a heap, in which case Extent Scan Fragmentation is not relevant.
If no clustered index, maybe creating one will get different DBREINDEX and performance results?
Cheers,
- Mark
Cheers,
- Mark
July 30, 2003 at 8:35 am
quote:
Do you have a clustered index? I may be wrong (and often am), but "index ID: 0" suggests a heap, in which case Extent Scan Fragmentation is not relevant.If no clustered index, maybe creating one will get different DBREINDEX and performance results?
Cheers,
- Mark
July 30, 2003 at 8:44 am
You are correct there are no clustered indexes here and throughout the largest tables. I did some more reading to understand the implications of a heap and apparently a table where there is a lot of nonsequential inserts, which is the nature of this cash receipts DB, does not necessarily need or want a clustered index. Do any of the other of the parameters look suspicious? At this point I am grasping at staws. Thanks for you help. Norm
August 1, 2003 at 12:14 pm
Have you tried running profiler and looking for missing statistics?? I would give that a try. Also on some of the slower running queries have you looked at the query plans to see what they are doing. eg. table scans. As Mccork said I would also put a clustered index on the table, but based on the results of analyzing the query plans.
Tom
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply