DBCC SHOWCONTIG - Slow Performance issue..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DBCC SHOWCONTIG - Slow Performance issue..

Author

Topic

azmi
Starting Member

37 Posts

Posted - 2006-05-11 : 04:43:57

[code]
Hi guys, i got very slow performance table even i use select statment to see the data.Doest anyone know what is result tell about, can anyone suggestion on how to increase this table performance...

DBCC SHOWCONTIG(FS_POSTINGS)
/*result*/
DBCC SHOWCONTIG scanning 'fs_postings' table...
Table: 'fs_postings' (871674153); index ID: 0, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 126562
- Extents Scanned..............................: 15833
- Extent Switches..............................: 15832
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.92% [15821:15833]
- Extent Scan Fragmentation ...................: 66.49%
- Avg. Bytes Free per Page.....................: 1366.5
- Avg. Page Density (full).....................: 83.12%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

[/code]

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 05:10:24

Is there a clustered index on this table? I'm surprised not to see a "Logical Scan Fragmentation" value.

See also http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Kristen

azmi
Starting Member

37 Posts

Posted - 2006-05-11 : 05:41:20

[code]
Thank for the link.u mean suppose there is
'Logical Scan Fragmentation' value in this table?.
I also dont see any Logical Scan Fragmentation'word here..that for what?.can i use DBCC INDEXDEFRAG to defrag this table indexes.
Is there is any impact in the data itself?.

SP_HELPINDEX FS_POSTINGS

index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
fs_postings_idx1 nonclustered located on PRIMARY fs_tran_type, fs_tran_group, fs_tran_no, fs_account_no, fs_org_locn, fs_locn_code, fs_company_code
fs_postings_idx2 nonclustered located on PRIMARY fs_tran_type, fs_org_locn, fs_locn_code, fs_company_code
fs_postings_idx3 nonclustered located on PRIMARY fs_gl_status, fs_tran_date, fs_tran_type, fs_locn_code, fs_company_code

[/code]

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 05:56:16

Bit surprised that you don't have a clustered index, and you don't have Primary Key / Unique Key. The fact you don't have one is why there is no Logical Scan Fragmentation value.

But yes you can either Rebuild the indexes, or Defrag (and then Update statistics).

Rebuilding will make the table inaccessible during the rebuild, and if the index is larger than the available free space in the database will extend your database.

Defrag can be used whilst the table is in use, and it can be aborted if it takes a long time (i.e. it will commit the partially completed work)

Kristen

azmi
Starting Member

37 Posts

Posted - 2006-05-11 : 06:29:47

I will do the rebuiling the indexes for this table and see where is any performance increase for this table.Instead of doing the rebuilding the table indexes, can i create clustered index for this table /primary key for this table while this table holding thousand of records...

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 06:33:04

I don't think it is a case of "instead of"!!! You need to consider what would be appropriate for a clustered index - it will either be an existing index, or perhaps a new one.

You should also consider why you do not have a Primary Key on that table. Maybe there is a good reason?

Kristen

azmi
Starting Member

37 Posts

Posted - 2006-05-12 : 07:49:20

thanks for your feedback kristen. That right may be there is good reason behind that.By the way i need to further analysis on this cause as i'am very concern on system performance issue..

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-05-12 : 08:58:12

My ten cents worth here :
Avg. Page Density (full).....................: 83.12%
-- this should be close to 100 %
Extent Scan Fragmentation ...................: 66.49%
--this should be under 20 %
I think also setting your auto update statistics and create statistics in your db options box in EM would help.
but I could be mistaken.

Kristen
Test

22859 Posts

Posted - 2006-05-12 : 11:54:57

Worthwhile having a scheduled task to update stats - otherwise you run the risk that the only time they get rebuilt is when they are detected as being out of shape - which is probably when you site is busiest! and may lead to timeouts etc.

Kristen

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-05-15 : 05:34:22

In response to my last statement the preferrable amounts for the extent fragmentation is below 20 %, I have just created and re fragmented all my indexs that the tuning wizard advised me to create (I did'nt let the wizard create then automatically)aswell as updateding statistics and run a dbcc showcontig getting:

DBCC SHOWCONTIG scanning 'Events' table...
Table: 'Events' (901578250); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 58282
- Extents Scanned..............................: 7311
- Extent Switches..............................: 7310
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.66% [7286:7311]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 48.68%
- Avg. Bytes Free per Page.....................: 746.2
- Avg. Page Density (full).....................: 90.78%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How can I get the extent fragmentation below 20 % please ?

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-05-17 : 12:37:40

Could someone please advise how this could be done please?

Or is the extent scan fragmentation percentage alright ?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-05-17 : 13:42:21

If you have multiple files in your filegroup, the Extent Scan Fragmentation value is meaningless (this is documented in Books Online). When I rewrote DBCC SHOWCONTIG for SQL Server 2000, I upgraded the Logical Scan Fragmentation algorithm to cope with multiple files, but not the Extent Scan Fragmentation one, as the LSF value is far more important. Same thing goes for Scan Density.

You can't do much to change ESF without separating tables - you're always goign to get interleaved extents with other tables/indexes. LSF is the property that affects perf the most - and only of range scans. See the whitepaper on fragmentation below for more details.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-05-18 : 06:22:56

Cheers Paul I feel quiet Privileged, thank you very much

azmi
Starting Member

37 Posts

Posted - 2006-05-19 : 10:38:51

well comeback,thanks for your info paul..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources