how do you know indexes have been fragmented?

[kalpesh.shah]

kalpesh.s...@gm ail.com Feb 1, 6:50 am show options

Newsgroups: comp.databases. informix
From: kalpesh.s...@gm ail.com - Find messages by this author
Date: 1 Feb 2005 06:50:21 -0800
Local: Tues, Feb 1 2005 6:50 am
Subject: how do you know indexes have been fragmented
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

i ran dbcc showcontig on my sql server db and it returned fo*llowing
Table: 'Table1' (1621580815); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........ ............... .........: 4982
- Extents Scanned........ ............... .......: 628
- Extent Switches....... ............... ........: 627
- Avg. Pages per Extent......... ............... : 7.9
- Scan Density [Best Count:Actual Count].......: 99.20% [623*:628]
- Logical Scan Fragmentation ............... ...: 0.00%
- Extent Scan Fragmentation ............... ....: 99.52%
- Avg. Bytes Free per Page........... ..........: 38.3
- Avg. Page Density (full)......... ............: 99.53%
Based on searching for info on index defrag it seems my Exte*nt Scan
Fragmentation percentage is not what it should be (0%) . Is *it true
and
if yes how can you be sure that your indexes have been fragm*ented.
If indexes are really fragmented what is the best way withou*t
reindexing(or is that the best way) to defrag the indexes.
Thank you
Kalpesh

[Paul S Randal [MS]]

Which version of SQL Server are you using? Did you run shrink after
rebuilding the index? Does your database have multiple files?

More info can be found in our whitepaper on fragmentation below:

http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

Regards.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

<ka**********@g mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
kalpesh.s...@gm ail.com Feb 1, 6:50 am show options

Newsgroups: comp.databases. informix
From: kalpesh.s...@gm ail.com - Find messages by this author
Date: 1 Feb 2005 06:50:21 -0800
Local: Tues, Feb 1 2005 6:50 am
Subject: how do you know indexes have been fragmented
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

i ran dbcc showcontig on my sql server db and it returned fo*llowing
Table: 'Table1' (1621580815); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........ ............... .........: 4982
- Extents Scanned........ ............... .......: 628
- Extent Switches....... ............... ........: 627
- Avg. Pages per Extent......... ............... : 7.9
- Scan Density [Best Count:Actual Count].......: 99.20% [623*:628]
- Logical Scan Fragmentation ............... ...: 0.00%
- Extent Scan Fragmentation ............... ....: 99.52%
- Avg. Bytes Free per Page........... ..........: 38.3
- Avg. Page Density (full)......... ............: 99.53%
Based on searching for info on index defrag it seems my Exte*nt Scan
Fragmentation percentage is not what it should be (0%) . Is *it true
and
if yes how can you be sure that your indexes have been fragm*ented.
If indexes are really fragmented what is the best way withou*t
reindexing(or is that the best way) to defrag the indexes.
Thank you
Kalpesh

[louis]

Paul,

Please correct me if I'm wrong. Kalpesh's output show 0% logical frag
(perfect index scan performance) and 99.5% page density (terrific IO
performance). However the extent frag is 99.5%. Assuming he's on a
RAID disk array or SAN -- should Kalpesh worry about this? Is the
disparity between logical and extent fragmentation -- caused by using
DBCC IndexDefrag? Will DBCC DBReindex make logical and extent
fragmentation more in line with each other?

Thanks.

[Paul S Randal [MS]]

If his db is spread over multiple files then my feeling is he shouldn't
worry as things look good. I'm assuming he's on SQL 2000.

The disparity isn't anything to do with using indexdefrag as opposed to
rebuild. Simple answer is that the logical and extent fragmentation numbers
shouldn't ever be opposite like that and I can't readily explain it, hence
the questions.

Rebuilding the index isn't guaranteed to fix all the extent fragmentation.
If you have multiple files, extent fragmentation is pretty much meaningless
(as explained in BOL) as the algorithm doesn't take multiple files into
account. On a single file, it depends on the distribution of free space that
will be used to construct the new index.

Regards.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"louis" <lo************ @gmail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

Paul,

Please correct me if I'm wrong. Kalpesh's output show 0% logical frag
(perfect index scan performance) and 99.5% page density (terrific IO
performance). However the extent frag is 99.5%. Assuming he's on a
RAID disk array or SAN -- should Kalpesh worry about this? Is the
disparity between logical and extent fragmentation -- caused by using
DBCC IndexDefrag? Will DBCC DBReindex make logical and extent
fragmentation more in line with each other?

Thanks.