Index Defragmentation Best Practices

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

Index Defragmentation Best Practices

Author

Topic

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-26 : 23:41:12

Hello,

I am delivered the following data;

DBCC SHOWCONTIG scanning 'ENT' table...
Table: 'ENT' (1045578763); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 981
- Extents Scanned..............................: 132
- Extent Switches..............................: 131
- Avg. Pages per Extent........................: 7.4
- Scan Density [Best Count:Actual Count].......: 93.18% [123:132]
- Extent Scan Fragmentation ...................: 93.94%
- Avg. Bytes Free per Page.....................: 352.4
- Avg. Page Density (full).....................: 95.65%

To which I assume, my action should be to run;

DBCC INDEXDEFRAG (10, ENT, 0)

Except I receive;

Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'ENT'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And the fragmentation has not changed!

I note from a similar post the extent scans/switches are close in sequence. This is a good thing, yes?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-27 : 00:42:56

Did you run dbcc under correct db?

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-27 : 01:02:48

quote:
Originally posted by rmiao

Did you run dbcc under correct db?

How can I confirm the database ID noted in the SHOWCONTIG statement is in fact the current db? I did not change between statements, so I assume it to be so.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-27 : 03:40:03

According to Books Online (the SQL Server help file) you should enclode the object name with single quotes.

DBCC INDEXDEFRAG (10, 'ENT', 0)

E 12°55'05.25"
N 56°04'39.16"

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-27 : 08:41:47

quote:
Originally posted by Peso

According to Books Online (the SQL Server help file) you should enclode the object name with single quotes.

DBCC INDEXDEFRAG (10, 'ENT', 0)

E 12°55'05.25"
N 56°04'39.16"

Tried this - same error remains.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-27 : 15:30:03

Can you post your 'dbcc showcontig' command? What's owner of table ENT? You need to run both dbcc under same db.

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-29 : 17:34:42

quote:
Originally posted by rmiao

Can you post your 'dbcc showcontig' command? What's owner of table ENT? You need to run both dbcc under same db.

owner is dbo, which i've included as a fully qualified name (with no better results).

statement is DBCC Showcontig (in Master).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-29 : 17:41:40

Are you using SQL Server 2000 or 2005? Probably 2000 since these commands are deprecated in 2005, but since they are still available in 2005, I wanted to double check. I've got defrag code for 2005, so you wouldn't need to write your own.

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

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-29 : 18:12:34

Your error message means you are probably in the wrong database.

Also, you cannot defragment index 0.

"index ID: 0" indicates a "heap", and that cannot be defragmented.

CODO ERGO SUM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-29 : 18:29:15

Good catch Michael.

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 10:21:03

I am using SQL 2K.

Whether I execute the index defrag from master or the db hosting 'ENT', the same error remains.

Can you elaborate on what a "heap" really is? Can I find further information elsewhere? Is this by design?

I have multiple indexes with this attribute.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 12:34:45

It means you don't have a clustered index. And best practices say you should have them on almost all tables.

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

Subscribe to my blog

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 13:31:32

Thanks to all for your input & assistance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources