DBCC SHOWCONTIG question

Hi Freinds,
SQL 2000
I have performance issues on my database and got to this point that DBCC
SHOWCONTIG result for my tables are:
DBCC SHOWCONTIG scanning 'dup_source_title' table...
Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 19296
- Extents Scanned.......................: 2429
- Extent Switches.......................: 2428
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
- Logical Scan Fragmentation ..............: 0.06%
- Extent Scan Fragmentation ...............: 1.03%
- Avg. Bytes Free per Page................: 7265.6
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 180459
- Extents Scanned.......................: 22605
- Extent Switches.......................: 23105
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
- Logical Scan Fragmentation ..............: 3.78%
- Extent Scan Fragmentation ...............: 12.67%
- Avg. Bytes Free per Page................: 656.4
- Avg. Page Density (full)................: 91.89%
is this good result? bad result? which statistic is not good and need
tuning? and how , what should I do to tune it?
Thanks in advance,
PatThey are both fine in regards to fragmentation but the first one is only 10%
full where as the second is 90% full. There is no right or wrong number for
fullness since it depends a lot on how the index is used. But 10% is
probably way too low of a fill factor. Have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have performance issues on my database and got to this point that DBCC
> SHOWCONTIG result for my tables are:
> DBCC SHOWCONTIG scanning 'dup_source_title' table...
> Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 19296
> - Extents Scanned.......................: 2429
> - Extent Switches.......................: 2428
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
> - Logical Scan Fragmentation ..............: 0.06%
> - Extent Scan Fragmentation ...............: 1.03%
> - Avg. Bytes Free per Page................: 7265.6
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
> Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 180459
> - Extents Scanned.......................: 22605
> - Extent Switches.......................: 23105
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
> - Logical Scan Fragmentation ..............: 3.78%
> - Extent Scan Fragmentation ...............: 12.67%
> - Avg. Bytes Free per Page................: 656.4
> - Avg. Page Density (full)................: 91.89%
> is this good result? bad result? which statistic is not good and need
> tuning? and how , what should I do to tune it?
> Thanks in advance,
> Pat
>|||Thank you very much for the answer,
I am struggling with the performance now and need to do smt to get the db
running.
What does Scan Density [Best Count:Actual Count] show? Should it be low ?
High?
Where are the main places that Ihave to look to tune the database?
Thanks again ina advance ,
Pat
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
> They are both fine in regards to fragmentation but the first one is only
> 10% full where as the second is 90% full. There is no right or wrong
> number for fullness since it depends a lot on how the index is used. But
> 10% is probably way too low of a fill factor. Have a look at these:
>
> http://www.microsoft.com/technet/pr..._showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/col...fillfactors.asp
> Fill Factors
> http://www.sql-server-performance.c...red_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||The 2nd link I gave you is all about DBCC SHOWCONTIG and should answer most
if not all of your questions on that topic. The other links are extremely
beneficial as well. But fragmentation is probably not the first place you
should look if you are having performance issues. Have a look at these to
help you narrow down the culprits:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance Checklist
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:ej0DZfcLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Thank you very much for the answer,
> I am struggling with the performance now and need to do smt to get the db
> running.
> What does Scan Density [Best Count:Actual Count] show? Should it be low ?
> High?
> Where are the main places that Ihave to look to tune the database?
> Thanks again ina advance ,
> Pat
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
>