Extent Fragmentation

I am currently trying to defrag a table and am unable to
reduce Extent fragmentation.
The structure of the table is as follows.
CREATE TABLE [dbo].[PortfolioMember] (
[PortfolioID] [int] NOT NULL ,
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
CLUSTERED
(
[PortfolioID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
NONCLUSTERED
(
[ID],
[PortfolioID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PortfolioMember_FK] ON [dbo].
[PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
FOREIGN KEY
(
[PortfolioID]
) REFERENCES [dbo].[Portfolio] (
[PortfolioID]
)
GO
So it has a clustered index.
The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
DBCC SHOWCONTIG scanning 'PortfolioMember' table...
Table: 'PortfolioMember' (1429580131); index ID: 1,
database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 26048
- Extents Scanned.......................: 3277
- Extent Switches.......................: 3283
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.15%
[3256:3284]
- Logical Scan Fragmentation ..............: 0.03%
- Extent Scan Fragmentation ...............: 90.51%
- Avg. Bytes Free per Page................: 802.9
- Avg. Page Density (full)................: 90.08%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I have tried the following all with no success at
reducing Extent fragmentation:
DBCC DBREINDEX (portfoliomember, '', 90)
DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
PK_PORTFOLIOMEMBER)
I have executed sp_spaceused and there is plenty of room.
I am currently rebuilding the table but don't want to
have to do this to properly defrag the table.
Does anyone have any suggestions?
Thanks in advance.
Jamie
jamie.downs@.risk.sungard.comJamie,
A couple of questions first. Is this the entire table? I assume it has
more columns but were left out for simplicity sake. If so it is usually
best to shoe the actual DDL so there are no assumptions. If [ID] is unique
then why not just make it the PK and drop the PortfolofioID? Why do you
create duplicate indexes? You have a PK constraint and a Unique constraint
on the two columns. This will make an index already to enforce the
constraint. There is no need to add a second index to each one. The last
comment is that I assume you have multiple files in your file group and that
is what is giving you the Extent fragmentation. This value is basically
useless when you have multiple files.
--
Andrew J. Kelly
SQL Server MVP
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> I am currently trying to defrag a table and am unable to
> reduce Extent fragmentation.
> The structure of the table is as follows.
> CREATE TABLE [dbo].[PortfolioMember] (
> [PortfolioID] [int] NOT NULL ,
> [ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> CLUSTERED
> (
> [PortfolioID],
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> NONCLUSTERED
> (
> [ID],
> [PortfolioID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [PortfolioMember_FK] ON [dbo].
> [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> FOREIGN KEY
> (
> [PortfolioID]
> ) REFERENCES [dbo].[Portfolio] (
> [PortfolioID]
> )
> GO
> So it has a clustered index.
> The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> Table: 'PortfolioMember' (1429580131); index ID: 1,
> database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 26048
> - Extents Scanned.......................: 3277
> - Extent Switches.......................: 3283
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.15%
> [3256:3284]
> - Logical Scan Fragmentation ..............: 0.03%
> - Extent Scan Fragmentation ...............: 90.51%
> - Avg. Bytes Free per Page................: 802.9
> - Avg. Page Density (full)................: 90.08%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I have tried the following all with no success at
> reducing Extent fragmentation:
> DBCC DBREINDEX (portfoliomember, '', 90)
> DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> PK_PORTFOLIOMEMBER)
> I have executed sp_spaceused and there is plenty of room.
> I am currently rebuilding the table but don't want to
> have to do this to properly defrag the table.
> Does anyone have any suggestions?
> Thanks in advance.
> Jamie
> jamie.downs@.risk.sungard.com
>
>|||As a follow-on to what Andrew said, the extent fragmentation algorithm
inside DBCC SHOWCONTIG specifically does not work when an index/table spans
multiple files and this is stated in BooksOnLine.
You should read the whitepaper on fragmentation and how-to/whether-to
address it at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$44jy$kDHA.684@.TK2MSFTNGP09.phx.gbl...
> Jamie,
> A couple of questions first. Is this the entire table? I assume it has
> more columns but were left out for simplicity sake. If so it is usually
> best to shoe the actual DDL so there are no assumptions. If [ID] is
unique
> then why not just make it the PK and drop the PortfolofioID? Why do you
> create duplicate indexes? You have a PK constraint and a Unique
constraint
> on the two columns. This will make an index already to enforce the
> constraint. There is no need to add a second index to each one. The last
> comment is that I assume you have multiple files in your file group and
that
> is what is giving you the Extent fragmentation. This value is basically
> useless when you have multiple files.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jamie" <jamie.downs@.risk.sungard.com> wrote in message
> news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> > I am currently trying to defrag a table and am unable to
> > reduce Extent fragmentation.
> >
> > The structure of the table is as follows.
> >
> > CREATE TABLE [dbo].[PortfolioMember] (
> > [PortfolioID] [int] NOT NULL ,
> > [ID] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> > CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> > CLUSTERED
> > (
> > [PortfolioID],
> > [ID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> > NONCLUSTERED
> > (
> > [ID],
> > [PortfolioID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PortfolioMember_FK] ON [dbo].
> > [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> > ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> > FOREIGN KEY
> > (
> > [PortfolioID]
> > ) REFERENCES [dbo].[Portfolio] (
> > [PortfolioID]
> > )
> > GO
> >
> > So it has a clustered index.
> >
> > The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> >
> > DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> > Table: 'PortfolioMember' (1429580131); index ID: 1,
> > database ID: 7
> > TABLE level scan performed.
> > - Pages Scanned........................: 26048
> > - Extents Scanned.......................: 3277
> > - Extent Switches.......................: 3283
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.15%
> > [3256:3284]
> > - Logical Scan Fragmentation ..............: 0.03%
> > - Extent Scan Fragmentation ...............: 90.51%
> > - Avg. Bytes Free per Page................: 802.9
> > - Avg. Page Density (full)................: 90.08%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> > I have tried the following all with no success at
> > reducing Extent fragmentation:
> >
> > DBCC DBREINDEX (portfoliomember, '', 90)
> >
> > DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> >
> > DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> > PK_PORTFOLIOMEMBER)
> >
> > I have executed sp_spaceused and there is plenty of room.
> >
> > I am currently rebuilding the table but don't want to
> > have to do this to properly defrag the table.
> >
> > Does anyone have any suggestions?
> >
> > Thanks in advance.
> >
> > Jamie
> > jamie.downs@.risk.sungard.com
> >
> >
> >
> >
>sql