Reasons why DBCC INDEXDEFRAG would not work

I am performing DBCC INDEXDEFRAG on my indexes in a table because I have
really bad extent scan fragmentation. After I run it though nothing has
seemingly changed. Can someone explain some reasons why this would be
happening?What index id are you looking at? Don't look at index id 0, as this is the d
ata in a heap table
(table without clustered index). In such, there is no order between the rows
, so the value is
meaningless.
If you are looking at an index (index id between 1 and 250), what does logic
al scan fragmentation
say?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:3D1ADA79-7827-48CD-BC51-0AEB4D54F164@.microsoft.com...
>I am performing DBCC INDEXDEFRAG on my indexes in a table because I have
> really bad extent scan fragmentation. After I run it though nothing has
> seemingly changed. Can someone explain some reasons why this would be
> happening?|||In addition to what Tibor stated if you have multiple files in your
filegroup you should pay attention to the Logical Fragmentation.
Andrew J. Kelly SQL MVP
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:3D1ADA79-7827-48CD-BC51-0AEB4D54F164@.microsoft.com...
>I am performing DBCC INDEXDEFRAG on my indexes in a table because I have
> really bad extent scan fragmentation. After I run it though nothing has
> seemingly changed. Can someone explain some reasons why this would be
> happening?|||Run INDEXDEFRAG again in QA, output to text and paste it here.
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:3D1ADA79-7827-48CD-BC51-0AEB4D54F164@.microsoft.com...
>I am performing DBCC INDEXDEFRAG on my indexes in a table because I have
> really bad extent scan fragmentation. After I run it though nothing has
> seemingly changed. Can someone explain some reasons why this would be
> happening?|||I think you mean DBCC SHOWCONTIG?
Andrew J. Kelly SQL MVP
"JT" <someone@.microsoft.com> wrote in message
news:OPK0LivjFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Run INDEXDEFRAG again in QA, output to text and paste it here.
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:3D1ADA79-7827-48CD-BC51-0AEB4D54F164@.microsoft.com...
>|||Here is the output of DBCC SHOWCONTIG (Faxes) with all_indexes
DBCC SHOWCONTIG scanning 'Faxes' table...
Table: 'Faxes' (1893581784); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 31251
- Extents Scanned.......................: 3921
- Extent Switches.......................: 3929
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.41% [3907:3930]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 0.51%
- Avg. Bytes Free per Page................: 748.9
- Avg. Page Density (full)................: 90.75%
DBCC SHOWCONTIG scanning 'Faxes' table...
Table: 'Faxes' (1893581784); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 5482
- Extents Scanned.......................: 696
- Extent Switches.......................: 704
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 97.30% [686:705]
- Logical Scan Fragmentation ..............: 0.29%
- Extent Scan Fragmentation ...............: 1.44%
- Avg. Bytes Free per Page................: 822.8
- Avg. Page Density (full)................: 89.83%
DBCC SHOWCONTIG scanning 'Faxes' table...
Table: 'Faxes' (1893581784); index ID: 3, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 10091
- Extents Scanned.......................: 1276
- Extent Switches.......................: 1353
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 93.21% [1262:1354]
- Logical Scan Fragmentation ..............: 0.50%
- Extent Scan Fragmentation ...............: 1.18%
- Avg. Bytes Free per Page................: 800.6
- Avg. Page Density (full)................: 90.11%
DBCC SHOWCONTIG scanning 'Faxes' table...
Table: 'Faxes' (1893581784); index ID: 4, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 6900
- Extents Scanned.......................: 873
- Extent Switches.......................: 974
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 88.51% [863:975]
- Logical Scan Fragmentation ..............: 0.96%
- Extent Scan Fragmentation ...............: 2.75%
- Avg. Bytes Free per Page................: 867.0
- Avg. Page Density (full)................: 89.29%
DBCC SHOWCONTIG scanning 'Faxes' table...
Table: 'Faxes' (1893581784); index ID: 5, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 7654
- Extents Scanned.......................: 966
- Extent Switches.......................: 1071
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 89.27% [957:1072]
- Logical Scan Fragmentation ..............: 0.78%
- Extent Scan Fragmentation ...............: 2.17%
- Avg. Bytes Free per Page................: 855.0
- Avg. Page Density (full)................: 89.44%
DBCC SHOWCONTIG scanning 'Faxes' table...
Table: 'Faxes' (1893581784); index ID: 6, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 9499
- Extents Scanned.......................: 1197
- Extent Switches.......................: 1325
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 89.59% [1188:1326]
- Logical Scan Fragmentation ..............: 0.88%
- Extent Scan Fragmentation ...............: 38.43%
- Avg. Bytes Free per Page................: 851.5
- Avg. Page Density (full)................: 89.48%
DBCC SHOWCONTIG scanning 'Faxes' table...
Table: 'Faxes' (1893581784); index ID: 7, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 7911
- Extents Scanned.......................: 999
- Extent Switches.......................: 1088
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 90.82% [989:1089]
- Logical Scan Fragmentation ..............: 0.70%
- Extent Scan Fragmentation ...............: 60.26%
- Avg. Bytes Free per Page................: 856.8
- Avg. Page Density (full)................: 89.41%
DBCC SHOWCONTIG scanning 'Faxes' table...
Table: 'Faxes' (1893581784); index ID: 8, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 8900
- Extents Scanned.......................: 1124
- Extent Switches.......................: 1249
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 89.04% [1113:1250]
- Logical Scan Fragmentation ..............: 0.79%
- Extent Scan Fragmentation ...............: 58.19%
- Avg. Bytes Free per Page................: 836.1
- Avg. Page Density (full)................: 89.67%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Tibor Karaszi" wrote:

> What index id are you looking at? Don't look at index id 0, as this is the
data in a heap table
> (table without clustered index). In such, there is no order between the ro
ws, so the value is
> meaningless.
> If you are looking at an index (index id between 1 and 250), what does log
ical scan fragmentation
> say?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:3D1ADA79-7827-48CD-BC51-0AEB4D54F164@.microsoft.com...
>|||That too.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OBNVykvjFHA.3064@.TK2MSFTNGP15.phx.gbl...
>I think you mean DBCC SHOWCONTIG?
> --
> Andrew J. Kelly SQL MVP
>
> "JT" <someone@.microsoft.com> wrote in message
> news:OPK0LivjFHA.3936@.TK2MSFTNGP10.phx.gbl...
>|||Your Logical fragmentation is fine and is what you should be concerned with.
I suspect you have more than 1 file in that filegroup and that is what is
causing the higher numbers of Physical Fragmentation. Is that true? What
does sp_helpdb 'yourDB' show? By the way do you really need 8 indexes on a
Fax table?
Andrew J. Kelly SQL MVP
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:A8CFC75C-77C5-4A61-857F-DA43BA4FC451@.microsoft.com...
> Here is the output of DBCC SHOWCONTIG (Faxes) with all_indexes
> DBCC SHOWCONTIG scanning 'Faxes' table...
> Table: 'Faxes' (1893581784); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 31251
> - Extents Scanned.......................: 3921
> - Extent Switches.......................: 3929
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.41% [3907:3930]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 0.51%
> - Avg. Bytes Free per Page................: 748.9
> - Avg. Page Density (full)................: 90.75%
> DBCC SHOWCONTIG scanning 'Faxes' table...
> Table: 'Faxes' (1893581784); index ID: 2, database ID: 5
> LEAF level scan performed.
> - Pages Scanned........................: 5482
> - Extents Scanned.......................: 696
> - Extent Switches.......................: 704
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 97.30% [686:705]
> - Logical Scan Fragmentation ..............: 0.29%
> - Extent Scan Fragmentation ...............: 1.44%
> - Avg. Bytes Free per Page................: 822.8
> - Avg. Page Density (full)................: 89.83%
> DBCC SHOWCONTIG scanning 'Faxes' table...
> Table: 'Faxes' (1893581784); index ID: 3, database ID: 5
> LEAF level scan performed.
> - Pages Scanned........................: 10091
> - Extents Scanned.......................: 1276
> - Extent Switches.......................: 1353
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 93.21% [1262:1354]
> - Logical Scan Fragmentation ..............: 0.50%
> - Extent Scan Fragmentation ...............: 1.18%
> - Avg. Bytes Free per Page................: 800.6
> - Avg. Page Density (full)................: 90.11%
> DBCC SHOWCONTIG scanning 'Faxes' table...
> Table: 'Faxes' (1893581784); index ID: 4, database ID: 5
> LEAF level scan performed.
> - Pages Scanned........................: 6900
> - Extents Scanned.......................: 873
> - Extent Switches.......................: 974
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 88.51% [863:975]
> - Logical Scan Fragmentation ..............: 0.96%
> - Extent Scan Fragmentation ...............: 2.75%
> - Avg. Bytes Free per Page................: 867.0
> - Avg. Page Density (full)................: 89.29%
> DBCC SHOWCONTIG scanning 'Faxes' table...
> Table: 'Faxes' (1893581784); index ID: 5, database ID: 5
> LEAF level scan performed.
> - Pages Scanned........................: 7654
> - Extents Scanned.......................: 966
> - Extent Switches.......................: 1071
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 89.27% [957:1072]
> - Logical Scan Fragmentation ..............: 0.78%
> - Extent Scan Fragmentation ...............: 2.17%
> - Avg. Bytes Free per Page................: 855.0
> - Avg. Page Density (full)................: 89.44%
> DBCC SHOWCONTIG scanning 'Faxes' table...
> Table: 'Faxes' (1893581784); index ID: 6, database ID: 5
> LEAF level scan performed.
> - Pages Scanned........................: 9499
> - Extents Scanned.......................: 1197
> - Extent Switches.......................: 1325
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 89.59% [1188:1326]
> - Logical Scan Fragmentation ..............: 0.88%
> - Extent Scan Fragmentation ...............: 38.43%
> - Avg. Bytes Free per Page................: 851.5
> - Avg. Page Density (full)................: 89.48%
> DBCC SHOWCONTIG scanning 'Faxes' table...
> Table: 'Faxes' (1893581784); index ID: 7, database ID: 5
> LEAF level scan performed.
> - Pages Scanned........................: 7911
> - Extents Scanned.......................: 999
> - Extent Switches.......................: 1088
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 90.82% [989:1089]
> - Logical Scan Fragmentation ..............: 0.70%
> - Extent Scan Fragmentation ...............: 60.26%
> - Avg. Bytes Free per Page................: 856.8
> - Avg. Page Density (full)................: 89.41%
> DBCC SHOWCONTIG scanning 'Faxes' table...
> Table: 'Faxes' (1893581784); index ID: 8, database ID: 5
> LEAF level scan performed.
> - Pages Scanned........................: 8900
> - Extents Scanned.......................: 1124
> - Extent Switches.......................: 1249
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 89.04% [1113:1250]
> - Logical Scan Fragmentation ..............: 0.79%
> - Extent Scan Fragmentation ...............: 58.19%
> - Avg. Bytes Free per Page................: 836.1
> - Avg. Page Density (full)................: 89.67%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> "Tibor Karaszi" wrote:
>|||No. I have a primary file group with one file and an index file group with
one file.
"Andrew J. Kelly" wrote:

> Your Logical fragmentation is fine and is what you should be concerned wit
h.
> I suspect you have more than 1 file in that filegroup and that is what is
> causing the higher numbers of Physical Fragmentation. Is that true? What
> does sp_helpdb 'yourDB' show? By the way do you really need 8 indexes on
a
> Fax table?
> --
> Andrew J. Kelly SQL MVP
>
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:A8CFC75C-77C5-4A61-857F-DA43BA4FC451@.microsoft.com...
>
>|||I just realized you are using INDEXDEFRAG. Have you tried using DBCC
DBREINDEX instead? In either case I wouldn't worry too much about it and
concentrate on the logical fragmentation instead.
Andrew J. Kelly SQL MVP
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:578E75D1-9A3B-434C-8166-4871D498FB91@.microsoft.com...
> No. I have a primary file group with one file and an index file group
> with
> one file.
> "Andrew J. Kelly" wrote:
>