August 17, 2004 at 12:53 pm
As stored procedures and queries are running very slow on one of my tables I ran DBCC SHOWCONTIG on that table i got this information.
- Pages Scanned................................: 78932
- Extents Scanned..............................: 9978
- Extent Switches..............................: 11080
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 89.04% [9867:11081]
- Logical Scan Fragmentation ..................: 10.66%
- Extent Scan Fragmentation ...................: 32.98%
- Avg. Bytes Free per Page.....................: 494.7
- Avg. Page Density (full).....................: 93.89%
With the above information how can I judge what to do to boost the performance.
Thanks.
August 17, 2004 at 2:48 pm
This shows fragmentation, which can slow access to the table, but it doesn't appear to be too fragmented. It is, however, a large table, so most likely indexing better can help. Also, what type of queries are you running? Any examples?
August 18, 2004 at 8:57 am
I am running some stored procedures (sround 10) to calculate the summary of the data.And in those summary sps I am using lots of temp tables.
And the queries i am running are having joins.
August 19, 2004 at 4:16 am
If your using SQL Server 2000 you could try using Declared tables instead of temp tables to improve performance - note that I dont think you can pass these between stored procs though. But most likely optimizations need to take place.
Do you do regular optimizations and indexing as part of your backup plan?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply