|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Defrag and Indexreindex defrags my indexes. From the data below it seems pretty obvious it doesn't, but I am unsure. I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a mess. Is this correct? Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less than enthusiastic about doing an online defrag. Do I need to do an DBCC DBREINDEX So to recap... 1) Does night reindex, defrag? 2) Is the index below a disaster? 3) What is the best way to get this defragged? Keeping the index online is preferred, but not necessarily required. ======================================================== DBCC SHOWCONTIG scanning 'TS1Endpoints' table... Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10 TABLE level scan performed. - Pages Scanned................................: 948 - Extents Scanned..............................: 204 - Extent Switches..............................: 203 - Avg. Pages per Extent........................: 4.6 - Scan Density [Best Count:Actual Count].......: 58.33% [119:204] - Extent Scan Fragmentation ...................: 99.51% - Avg. Bytes Free per Page.....................: 889.5 - Avg. Page Density (full).....................: 89.01% DBCC SHOWCONTIG scanning 'TS1Endpoints' table... Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10 LEAF level scan performed. - Pages Scanned................................: 292 - Extents Scanned..............................: 37 - Extent Switches..............................: 36 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 100.00% [37:37] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 810.7 - Avg. Page Density (full).....................: 89.98% -- Paul Bergson MVP - Directory Services MCT, MCSE, MCSA, Security+, BS CSci 2003, 2000 (Early Achiever), NT http://www.pbbergs.com Please no e-mails, any questions should be posted in the NewsGroup This posting is provided "AS IS" with no warranties, and confers no rights. Paul,
There are not to many pages, so I will not worry much. If you really want to defrag that table, then you have to add a clustered index, or dump the data, recreate the table and import the data. AMB Show quote "Paul Bergson [MVP-DS]" wrote: > I am by no means an SQL expert, I need to figure out if doing a nightly > reindex defrags my indexes. From the data below it seems pretty obvious it > doesn't, but I am unsure. > > I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a > mess. Is this correct? > > Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less > than enthusiastic about doing an online defrag. > > Do I need to do an DBCC DBREINDEX > > So to recap... > 1) Does night reindex, defrag? > 2) Is the index below a disaster? > 3) What is the best way to get this defragged? Keeping the index online > is preferred, but not necessarily required. > > ======================================================== > > DBCC SHOWCONTIG scanning 'TS1Endpoints' table... > > Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10 > > TABLE level scan performed. > > - Pages Scanned................................: 948 > > - Extents Scanned..............................: 204 > > - Extent Switches..............................: 203 > > - Avg. Pages per Extent........................: 4.6 > > - Scan Density [Best Count:Actual Count].......: 58.33% [119:204] > > - Extent Scan Fragmentation ...................: 99.51% > > - Avg. Bytes Free per Page.....................: 889.5 > > - Avg. Page Density (full).....................: 89.01% > > DBCC SHOWCONTIG scanning 'TS1Endpoints' table... > > Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10 > > LEAF level scan performed. > > - Pages Scanned................................: 292 > > - Extents Scanned..............................: 37 > > - Extent Switches..............................: 36 > > - Avg. Pages per Extent........................: 7.9 > > - Scan Density [Best Count:Actual Count].......: 100.00% [37:37] > > - Logical Scan Fragmentation ..................: 0.00% > > - Extent Scan Fragmentation ...................: 0.00% > > - Avg. Bytes Free per Page.....................: 810.7 > > - Avg. Page Density (full).....................: 89.98% > > > -- > Paul Bergson > MVP - Directory Services > MCT, MCSE, MCSA, Security+, BS CSci > 2003, 2000 (Early Achiever), NT > > http://www.pbbergs.com > > Please no e-mails, any questions should be posted in the NewsGroup > This posting is provided "AS IS" with no warranties, and confers no rights. > > > So the line
Extent Scan Fragmentation ...................: 99.51% Is not bad? -- Show quotePaul Bergson MVP - Directory Services MCT, MCSE, MCSA, Security+, BS CSci 2003, 2000 (Early Achiever), NT http://www.pbbergs.com Please no e-mails, any questions should be posted in the NewsGroup This posting is provided "AS IS" with no warranties, and confers no rights. "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@microsoft.com... > Paul, > > There are not to many pages, so I will not worry much. If you really want > to > defrag that table, then you have to add a clustered index, or dump the > data, > recreate the table and import the data. > > > AMB > > "Paul Bergson [MVP-DS]" wrote: > >> I am by no means an SQL expert, I need to figure out if doing a nightly >> reindex defrags my indexes. From the data below it seems pretty obvious >> it >> doesn't, but I am unsure. >> >> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely >> a >> mess. Is this correct? >> >> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is >> less >> than enthusiastic about doing an online defrag. >> >> Do I need to do an DBCC DBREINDEX >> >> So to recap... >> 1) Does night reindex, defrag? >> 2) Is the index below a disaster? >> 3) What is the best way to get this defragged? Keeping the index >> online >> is preferred, but not necessarily required. >> >> ======================================================== >> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table... >> >> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10 >> >> TABLE level scan performed. >> >> - Pages Scanned................................: 948 >> >> - Extents Scanned..............................: 204 >> >> - Extent Switches..............................: 203 >> >> - Avg. Pages per Extent........................: 4.6 >> >> - Scan Density [Best Count:Actual Count].......: 58.33% [119:204] >> >> - Extent Scan Fragmentation ...................: 99.51% >> >> - Avg. Bytes Free per Page.....................: 889.5 >> >> - Avg. Page Density (full).....................: 89.01% >> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table... >> >> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10 >> >> LEAF level scan performed. >> >> - Pages Scanned................................: 292 >> >> - Extents Scanned..............................: 37 >> >> - Extent Switches..............................: 36 >> >> - Avg. Pages per Extent........................: 7.9 >> >> - Scan Density [Best Count:Actual Count].......: 100.00% [37:37] >> >> - Logical Scan Fragmentation ..................: 0.00% >> >> - Extent Scan Fragmentation ...................: 0.00% >> >> - Avg. Bytes Free per Page.....................: 810.7 >> >> - Avg. Page Density (full).....................: 89.98% >> >> >> -- >> Paul Bergson >> MVP - Directory Services >> MCT, MCSE, MCSA, Security+, BS CSci >> 2003, 2000 (Early Achiever), NT >> >> http://www.pbbergs.com >> >> Please no e-mails, any questions should be posted in the NewsGroup >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> >> >> Paul,
That number is not relevant to heaps (tables without clustered index) and is meaningless when the index spans multiple files. DBCC SHOWCONTIG http://msdn2.microsoft.com/en-US/library/aa258803(SQL.80).aspx Microsoft SQL Server 2000 Index Defragmentation Best Practices http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx AMB Show quote "Paul Bergson [MVP-DS]" wrote: > So the line > Extent Scan Fragmentation ...................: 99.51% > > Is not bad? > > -- > Paul Bergson > MVP - Directory Services > MCT, MCSE, MCSA, Security+, BS CSci > 2003, 2000 (Early Achiever), NT > > http://www.pbbergs.com > > Please no e-mails, any questions should be posted in the NewsGroup > This posting is provided "AS IS" with no warranties, and confers no rights. > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message > news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@microsoft.com... > > Paul, > > > > There are not to many pages, so I will not worry much. If you really want > > to > > defrag that table, then you have to add a clustered index, or dump the > > data, > > recreate the table and import the data. > > > > > > AMB > > > > "Paul Bergson [MVP-DS]" wrote: > > > >> I am by no means an SQL expert, I need to figure out if doing a nightly > >> reindex defrags my indexes. From the data below it seems pretty obvious > >> it > >> doesn't, but I am unsure. > >> > >> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely > >> a > >> mess. Is this correct? > >> > >> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is > >> less > >> than enthusiastic about doing an online defrag. > >> > >> Do I need to do an DBCC DBREINDEX > >> > >> So to recap... > >> 1) Does night reindex, defrag? > >> 2) Is the index below a disaster? > >> 3) What is the best way to get this defragged? Keeping the index > >> online > >> is preferred, but not necessarily required. > >> > >> ======================================================== > >> > >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table... > >> > >> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10 > >> > >> TABLE level scan performed. > >> > >> - Pages Scanned................................: 948 > >> > >> - Extents Scanned..............................: 204 > >> > >> - Extent Switches..............................: 203 > >> > >> - Avg. Pages per Extent........................: 4.6 > >> > >> - Scan Density [Best Count:Actual Count].......: 58.33% [119:204] > >> > >> - Extent Scan Fragmentation ...................: 99.51% > >> > >> - Avg. Bytes Free per Page.....................: 889.5 > >> > >> - Avg. Page Density (full).....................: 89.01% > >> > >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table... > >> > >> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10 > >> > >> LEAF level scan performed. > >> > >> - Pages Scanned................................: 292 > >> > >> - Extents Scanned..............................: 37 > >> > >> - Extent Switches..............................: 36 > >> > >> - Avg. Pages per Extent........................: 7.9 > >> > >> - Scan Density [Best Count:Actual Count].......: 100.00% [37:37] > >> > >> - Logical Scan Fragmentation ..................: 0.00% > >> > >> - Extent Scan Fragmentation ...................: 0.00% > >> > >> - Avg. Bytes Free per Page.....................: 810.7 > >> > >> - Avg. Page Density (full).....................: 89.98% > >> > >> > >> -- > >> Paul Bergson > >> MVP - Directory Services > >> MCT, MCSE, MCSA, Security+, BS CSci > >> 2003, 2000 (Early Achiever), NT > >> > >> http://www.pbbergs.com > >> > >> Please no e-mails, any questions should be posted in the NewsGroup > >> This posting is provided "AS IS" with no warranties, and confers no > >> rights. > >> > >> > >> > > > Paul Bergson [MVP-DS] (pbergson@allete_nospam.com) writes:
> I am by no means an SQL expert, I need to figure out if doing a nightly No, because that's not an index, but a heap, a table without a > reindex defrags my indexes. From the data below it seems pretty obvious > it doesn't, but I am unsure. > > I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a > mess. Is this correct? clustered index. Heaps are quite prone to fragmentation, and your table is not in the best shape with 4.6 pages per extent. But as Alejandro pointed out the table is not that big, and it is not likely to be a major problem. > Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is Neither INDEXDEFRAG or DBREINDEX works on heap. You can build a clustered> less than enthusiastic about doing an online defrag. index on the table and then drop it. Or you can just build a clustered index and have it that way. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics