|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
I can't get rid of ExtentFragmentation on a few primary keys
tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG (with a fillfactor of 80). But nothing seems to correct it. There is a few foreign keys against this primary key, so it's really hard to drop the index totally and recreate it. Has anyone seen this before? Thank you John DBCC SHOWCONTIG scanning 'Users' table... Table: 'Users' (901578250); index ID: 1, database ID: 12 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.....................: 3789.0 - Avg. Page Density (full).....................: 53.19% The server is running SQL Server 2000 sp4
Show quoteHide quote "John" wrote: > I have many tables with primary keys that look like the one below. I've > tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG > (with a fillfactor of 80). But nothing seems to correct it. There is a few > foreign keys against this primary key, so it's really hard to drop the index > totally and recreate it. Has anyone seen this before? > > Thank you > John > > DBCC SHOWCONTIG scanning 'Users' table... > Table: 'Users' (901578250); index ID: 1, database ID: 12 > 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.....................: 3789.0 > - Avg. Page Density (full).....................: 53.19% Hi John
There seems to be only two extents in this index, the second of which is only partially full (probably less than 20%) John Show quoteHide quote "John" wrote: > I have many tables with primary keys that look like the one below. I've > tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG > (with a fillfactor of 80). But nothing seems to correct it. There is a few > foreign keys against this primary key, so it's really hard to drop the index > totally and recreate it. Has anyone seen this before? > > Thank you > John > > DBCC SHOWCONTIG scanning 'Users' table... > Table: 'Users' (901578250); index ID: 1, database ID: 12 > 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.....................: 3789.0 > - Avg. Page Density (full).....................: 53.19% Just to add to Johns answer anything less than 8 pages will come from mixed
extents and will never be able to get 100% free from fragmentation. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "John" <J***@discussions.microsoft.com> wrote in message news:7470559B-3653-4F59-917B-758781E8568D@microsoft.com... >I have many tables with primary keys that look like the one below. I've > tried Create index with (drop index), dbcc dbreindex and dbcc INDEXDEFRAG > (with a fillfactor of 80). But nothing seems to correct it. There is a > few > foreign keys against this primary key, so it's really hard to drop the > index > totally and recreate it. Has anyone seen this before? > > Thank you > John > > DBCC SHOWCONTIG scanning 'Users' table... > Table: 'Users' (901578250); index ID: 1, database ID: 12 > 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.....................: 3789.0 > - Avg. Page Density (full).....................: 53.19%  
Other interesting topics
Indexed view appears slower
Cannot change tran log to "unrestricted" growth Revert to checkpoint Unknown Error with MDE file Transaction log backups Identify the reason for a Deadlock vb sqlserver 2000 Backup Restore Data not being partitioned properly? Running Query Analyzer on a workstation Problem with top 1. urgent help needed |
|||||||||||||||||||||||
