dbcc showcontig help needed - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

dbcc showcontig help needed

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-23 : 01:35:05

Dear Friends,

I have run the dbcc showcontig statement for a table iam getting the following result.the table has an clustered index if i run the table using clustered index it leeds to clustered index scan.i have done reindexing also same exists.Please guide me.

DBCC SHOWCONTIG scanning 'DETAILFILE' table...
Table: 'DETAILFILE' (1854629650); index ID: 1, database ID: 19
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-23 : 01:51:48

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-23 : 02:07:46

The table has some 50000 records and has clutered index.
for example column 2,3 has clustered index
select column_name from table_name where 2 = '' and 3 = ''
this query leeds to clustered index scan.
i have run dbcc showcontig statement the above result is displayed
it s not showing Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: any details

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-23 : 02:41:13

I think you are confused by DBCC SHOWCONTIG and the graphical execution plan.
They are not the same.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-23 : 02:48:18

However´, it does look suspicious that a 50 0000 record table has zero scanned pages using DBCC SHOWCONTIG.
There should be at minimum 6 or 7 pages acanned.

Show us the exact DBCC SHOWCONTIG command you are using.

E 12°55'05.63"
N 56°04'39.26"

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-23 : 03:57:45

sorry for late reply
dbcc showcontig ('Detailfile')

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-23 : 04:44:12

if i run dbcc showcontig ('Detailfile') statement for this table alone the problem comes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-23 : 05:19:49

Tell us what a CLUSTERED INDEX SCAN in the execution plan has to do with the DBCC SHOWCONTIG command?
What is your question/problem, really?

E 12°55'05.63"
N 56°04'39.26"

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-23 : 06:22:00

IF i run DBCC SHOWCONTIG for a table of 50000 records iam getting following result.
how come the Pages Scanned,Extents Scanned ....details will be 0 for 50000 records.

DBCC SHOWCONTIG scanning 'DETAILFILE' table...
Table: 'DETAILFILE' (1854629650); index ID: 1, database ID: 19
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-23 : 07:01:16

Do you have two DetailFile tables? Do you have case sensitive collation?

E 12°55'05.63"
N 56°04'39.26"

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-23 : 09:36:52

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-23 : 09:40:14

Run

Select count(*) from [schema].DETAILFILE

before we know what exactly you are looking for.

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-23 : 09:43:38

12617

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-23 : 09:58:24

Run this and post results:

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("[schema].DETAILFILE");

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-23 : 10:02:19

DBCC SHOWCONTIG scanning 'DETAILFILE' table...
Table: 'DETAILFILE' (1854629650); index ID: 1, database ID: 19
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-23 : 15:17:41

Run DBCC UPDATEUSAGE against the database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-27 : 00:29:56

Thanks for your reply.
I have tried DBCC UPDATEUSAGE its not returning any msg.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-27 : 00:42:27

It won't return anything, but it might fix your data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-27 : 00:57:46

Thanks tkizer
if i run DBCC SHOWCONTIG for this table again the

DBCC SHOWCONTIG scanning 'DETAILFILE' table...
Table: 'DETAILFILE' (1854629650); index ID: 1, database ID: 19
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0

is returning

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-27 : 11:10:01

Which tool are you using to execute the command?
Query Analyzer? Management Studio? Which version?

Which database version is DetailFile? SQL Server 2000? SQL Server 2005?
What is the compatibility level?

E 12°55'05.63"
N 56°04'39.26"

shaggy
Posting Yak Master

248 Posts

Posted - 2009-01-29 : 05:55:07

Sorry for very delay Reply.

Which tool are you using to execute the command?
Query Analyzer?
Management Studio? YES
Which version? Microsoft SQL Server 2005 - 9.00.3033.00 (Intel X86)
Dec 6 2006 17:53:12
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build2600:Service Pack 2)

Which database version is DetailFile?
SQL Server 2000?
SQL Server 2005? YES
What is the compatibility level? 90

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources