Extra White Space

Hi All,
I've got a report that is set up with the Report Properties layout as
follows: page width = 8.5in; page height = 11in; left/right margins = .25in;
top/bottom margins = .50in.
When the report displays in IE7, there is a large white area (blank) to the
right of the report and it's full length. When I export as a PDF document,
this area seems to cause extra pages to be created in the PDF output.
Since I'm not sure "why" this extra white space is appearing, I thought I'd
post this here.
Thanks in advance!
Kind regards - FredHi Fred,
I've found, that meters of extra white space on the right side, before all
when viewed in HTML, are created when there are overlapping visible textboxes.
When textboxes overlap where one is invisible, there are also minor layout
differences, but never so dramatically.
Yours, Henry

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