Regarding Reindexing - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Regarding Reindexing

Author

Topic

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-05-04 : 20:25:09

when i do a dbcc showcontig on a datbase ,i
am not sure what to see in the results whether
the scan density or the logical fragmentation.
the database has single data and log file

can anybody help on this

thanks,

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-04 : 23:37:25

According to the SQL Server Query Performance Turing Distilled book,
Scan Density[the ratio of best count of extents to the Actual counts] is one of the most useful indicators of fragmentation.

The Best count represents the number of extents required for the number of pages scanned.[ Best count = pages Scanned / 8 ]

The Actual count is an indicator of how many extents can ideally cause the current number of the extent switches. [Actual Count = Extent Switches + 1]

In the best case, the Scan Density should be 100%. A value less than 100% indicates that the pages are non-contiguously distributed between the extents. An index with Scan Density less than 40% can be considered a candidate for defragmentation and might need to rebuild index

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-05-05 : 07:33:32

This articles has a lot of useful information

Microsoft SQL Server 2000 Index Defragmentation Best Practices
[url]http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp[/url]

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-05-05 : 19:05:23

jung1975,

do you reccomend me to look at scan density rather than logical fragmentation

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-05 : 19:20:27

Scan density is what I always look at first. After you run the DBCC DBREINDEX, the scan density reported by DBCC SHOWCONTIG should be at 100% or very close to 100%.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-06 : 10:01:39

Tara (or anyone):

This is a DBCC SHOWCONTIG on my Dev box. What are the best practices to keep the database healthy as part of a maint plan (I guess that's a very broad question, sorry).

Here's the output...any comments appreciated:

[edit] is this called thread theft?[/edit]


DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 3713.0
- Avg. Page Density (full).....................: 54.13%
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 3
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 33.33%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 2827.3
- Avg. Page Density (full).....................: 65.07%
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 7
- Extent Switches..............................: 7
- Avg. Pages per Extent........................: 1.1
- Scan Density [Best Count:Actual Count].......: 12.50% [1:8]
- Logical Scan Fragmentation ..................: 25.00%
- Extent Scan Fragmentation ...................: 57.14%
- Avg. Bytes Free per Page.....................: 3835.0
- Avg. Page Density (full).....................: 52.62%
DBCC SHOWCONTIG scanning 'systypes' table...
Table: 'systypes' (4); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6712.0
- Avg. Page Density (full).....................: 17.07%
DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 2.3
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 3438.6
- Avg. Page Density (full).....................: 57.52%
DBCC SHOWCONTIG scanning 'sysfiles1' table...
Table: 'sysfiles1' (8); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6508.0
- Avg. Page Density (full).....................: 19.59%
DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6930.0
- Avg. Page Density (full).....................: 14.38%
DBCC SHOWCONTIG scanning 'sysusers' table...
Table: 'sysusers' (10); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7296.0
- Avg. Page Density (full).....................: 9.86%
DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 2909.0
- Avg. Page Density (full).....................: 64.06%
DBCC SHOWCONTIG scanning 'sysreferences' table...
Table: 'sysreferences' (14); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7602.0
- Avg. Page Density (full).....................: 6.08%
DBCC SHOWCONTIG scanning 'sysfilegroups' table...
Table: 'sysfilegroups' (96); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8058.0
- Avg. Page Density (full).....................: 0.44%
DBCC SHOWCONTIG scanning 'Orders' table...
Table: 'Orders' (21575115); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 20
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 146.5
- Avg. Page Density (full).....................: 98.19%
DBCC SHOWCONTIG scanning 'Products' table...
Table: 'Products' (117575457); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 3.0
- Avg. Page Density (full).....................: 99.96%
DBCC SHOWCONTIG scanning 'Order Details' table...
Table: 'Order Details' (325576198); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 9
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 33.33% [2:6]
- Logical Scan Fragmentation ..................: 11.11%
- Extent Scan Fragmentation ...................: 16.67%
- Avg. Bytes Free per Page.....................: 673.2
- Avg. Page Density (full).....................: 91.68%
DBCC SHOWCONTIG scanning 'Region' table...
Table: 'Region' (885578193); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7644.0
- Avg. Page Density (full).....................: 5.56%
DBCC SHOWCONTIG scanning 'Territories' table...
Table: 'Territories' (901578250); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 1365.0
- Avg. Page Density (full).....................: 83.14%
DBCC SHOWCONTIG scanning 'EmployeeTerritories' table...
Table: 'EmployeeTerritories' (917578307); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6773.0
- Avg. Page Density (full).....................: 16.32%
DBCC SHOWCONTIG scanning 'Employees' table...
Table: 'Employees' (1977058079); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 4945.0
- Avg. Page Density (full).....................: 38.91%
DBCC SHOWCONTIG scanning 'Categories' table...
Table: 'Categories' (2041058307); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7504.0
- Avg. Page Density (full).....................: 7.29%
DBCC SHOWCONTIG scanning 'Customers' table...
Table: 'Customers' (2073058421); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 3
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 246.7
- Avg. Page Density (full).....................: 96.95%
DBCC SHOWCONTIG scanning 'Shippers' table...
Table: 'Shippers' (2105058535); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7867.0
- Avg. Page Density (full).....................: 2.80%
DBCC SHOWCONTIG scanning 'Suppliers' table...
Table: 'Suppliers' (2137058649); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 654.0
- Avg. Page Density (full).....................: 91.92%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Brett

8-)

Edited by - x002548 on 05/06/2003 10:02:16

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-06 : 12:17:18

If it were production, I would run DBCC DBREINDEX on those indexes that don't have 100% for scan density. But since it is a dev box, go ahead and run DBCC DBREINDEX on the entire database (it's just easier).

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-06 : 12:25:35

Thanks Tara, and good morning. I'll have a go at it...must be a homegrown utility I can write to check which ones arent and isolate them....let you know

ps. do you have a royal scepter yet?

Thanks

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-06 : 12:30:43

quote:

Thanks Tara, and good morning. I'll have a go at it...must be a homegrown utility I can write to check which ones arent and isolate them....let you know

Someone at my last job wrote a script that can do this, but it had issues. I don't have the script anymore, otherwise I would post it for you.

quote:

ps. do you have a royal scepter yet?

Not yet, but I am looking for one.

Tara

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-05-08 : 19:04:49

i did dbcc showcontig on some tables
which do not have clustered index at all but the scan density of those
tables is low,so when i do dbreindex on those tables the scan density
doesn't change is there anything special i will have to do for
these kind of tables which donot have clustered index

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources