|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Reasons why DBCC INDEXDEFRAG would not workI 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 data 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 logical scan fragmentation say? -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Andre" <An***@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? 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. Show quote "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 rows, so the value is > meaningless. > > If you are looking at an index (index id between 1 and 250), what does logical 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" <An***@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? > > 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? -- Show quoteAndrew J. Kelly SQL MVP "Andre" <An***@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: > >> 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 >> rows, so the value is >> meaningless. >> >> If you are looking at an index (index id between 1 and 250), what does >> logical 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" <An***@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? >> >> No. I have a primary file group with one file and an index file group with
one file. Show quote "Andrew J. Kelly" wrote: > 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" <An***@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: > > > >> 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 > >> rows, so the value is > >> meaningless. > >> > >> If you are looking at an index (index id between 1 and 250), what does > >> logical 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" <An***@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 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. -- Show quoteAndrew J. Kelly SQL MVP "Andre" <An***@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: > >> 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" <An***@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: >> > >> >> 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 >> >> rows, so the value is >> >> meaningless. >> >> >> >> If you are looking at an index (index id between 1 and 250), what does >> >> logical 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" <An***@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. -- Show quoteAndrew J. Kelly SQL MVP "Andre" <An***@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.
Show quote "Andre" <An***@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?
-- Show quoteAndrew J. Kelly SQL MVP "JT" <some***@microsoft.com> wrote in message news:OPK0LivjFHA.3936@TK2MSFTNGP10.phx.gbl... > Run INDEXDEFRAG again in QA, output to text and paste it here. > > "Andre" <An***@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? > > That too.
Show quote "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" <some***@microsoft.com> wrote in message > news:OPK0LivjFHA.3936@TK2MSFTNGP10.phx.gbl... >> Run INDEXDEFRAG again in QA, output to text and paste it here. >> >> "Andre" <An***@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? >> >> > > DBCC INDEXDEFRAG is only intended to fix logical scan fragmentation
problems, not extent scan fragmentation. No new extents are allocated during DEFRAG, so you keep the same extents you had. Please read http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx This whitepaper specifically states: DBCC INDEXDEFRAG does not correct extent fragmentation on indexes. You have to completely rebuild the index to do that. Show quote "Andre" <An***@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?  |
|||||||||||||||||||||||
Other interesting topics