Pagination

I have a report which consists of a list item, in which is a rectangle containing a header (as a subreport, but I think that's not relevant) and a subreport. The aforementioned rectangle is specified to include a pagebreak after each occurrence, but that pagebreak is not working as expected.

When I run the report I get 1 subreport on the first page, and exactly two on each subsequent page. Other than the pagination, I am getting very close to what I want. It's too bad that the subreport's pagination is ignored, but I can live with that for now.

Things I've already tried:

The report's pageSize and interactiveSize are both set to landscape

The subreport's report's pageSize and interactiveSize are both set to the actual size that the subreport takes up on the main report (which is ignored anyway)

The list does not have any of the page break options selected.

Any Ideas?

Are you using any visibility condition for subreport or rectangle?

I am asking this becasue page break is not supported with visibility condition.

|||

Thanks for your help.

I have not set any visibility settings away from the default of visible.

|||

Maybe it would help if I explained more of what I'm trying to do...

A single report is run from a single id, but there are several ways of selecting that ID, and some ways should result in batches of reports being generated. (for example, a rep may want to run one report to be mailed to each of their clients). Each report should have just one header at the beginning of it.

My report consists of one big list. In the list there are two subreports, one for the header that should be at the top of each report, and one for the body of the report itself. The list uses a query that returns the IDs that should be included in the batch as its dataset. The ID field is passed as one of the report parameters to the body subreport, with the rest of the parameters for the subreport passed straight through.

I put both of the subreports inside a rectangle (inside the list) to try to force the pagebreak.

<Edit: I removed the bit about table headers in the subreport, because that seems to be working>

|||

I hate to keep bumping my thread, but I need help figuring this out.

Maybe I am making my process too complicated? Is there another way to do what I'm trying to do that will allow the report server to correct the pagination?

Is there more info I could give that would help find a solution?

PAGEIOLATCH_SH performance problem

Hi,
We are currently experiencing huge performance problems when running stored
procs that consists of several 'select into' queries of type:
select A as AA,
B as BB,
sum(cast(C as decimal)) as CC
into myResult
from myTable
where id = 1 and C <> ' '
group by A, B
Database size is >200gig.
The query uses an index on (id, A, B)
The execution plan looks like this:
44% insert
28% bookmark lookup
28% index scan
The following blocking is reported:
SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
SP6 : system, background, DBCC
SP10 (blocking) myResult (tab), myTable
SP6 (blocked by SP10) myResult (tab)
Can anyone explain what the system/dbcc task is doing and if this blocking
causes the problem?
I checked some of the counters but the only unusual value I found is a disk
io of < 2MB which I understand is too low.
Is there an easy way to improve this, would disk defrag or index reorg help?
Any help is much appreciated,
FrankThe fact that your query is blocking DBCC shouldn't be an issue. If it were
the other way around, you'd have a problem. That being said, the DBCC
commands are ussually part of the Database Maintenance tasks. You should be
running operations while the maintenance is going on. I'd look at the
schedule of these two.
The PAGEIOLATCH_SH wait happens, the question is what is the duration? If
the latches are taking too long, that could be indicative of server memory
starvation or a sub perfoming Disk I/O subsystem.
Look at these performance counters:
SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if the
Target is larger than the Total, SQL Server would be assisted by having more
memory available to it.
SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and TotalLatchWait:
if the waits are larger than 100 ms, you probably have some sluggishness.
If they are greater than 1000 ms (1 second), then you may have a serious
issue. The wait count is a tough one to determine because it is highly
dependent on system resources and useage. However, if the wait times are
high and the wait counts are low, then each request is suffering.
Physical or Logical Disk: sec/Read and sec/Writ: these are the seek latency
counters. They tell you the responsiveness of the disk. For an optimal
disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O, on
average. You might get some 100 ms or more per I/O, but should only be for
short durations.
Also take a look at the Disk %Read and %Write %Idle, this will tell you how
busy your disk are for the various I/O types. You'll want to check out the
Read/sec and Write/sec, most modern disks should be able to support up to
200 to 300 I/O operations per second. You'll aslo want to look at Read
Bytes/sec and Write Bytes/sec to see if you are overloading the disk
subsystem bandwidth.
Also look at all of the counters for the SQL Server:Buffer Manager. These
will tell you the distribution of the memory manager segments within the
Buffer Pool. Look to see if any area is being overused.
Hope this gives you some areas to look at.
Sincerely,
Anthony Thomas
"FrankM" <a@.b.c> wrote in message
news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
Hi,
We are currently experiencing huge performance problems when running stored
procs that consists of several 'select into' queries of type:
select A as AA,
B as BB,
sum(cast(C as decimal)) as CC
into myResult
from myTable
where id = 1 and C <> ' '
group by A, B
Database size is >200gig.
The query uses an index on (id, A, B)
The execution plan looks like this:
44% insert
28% bookmark lookup
28% index scan
The following blocking is reported:
SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
SP6 : system, background, DBCC
SP10 (blocking) myResult (tab), myTable
SP6 (blocked by SP10) myResult (tab)
Can anyone explain what the system/dbcc task is doing and if this blocking
causes the problem?
I checked some of the counters but the only unusual value I found is a disk
io of < 2MB which I understand is too low.
Is there an easy way to improve this, would disk defrag or index reorg help?
Any help is much appreciated,
Frank|||Frank -- what is the wait resource being reported for the I/O latch in
question? I would be curious as to why a sleeping spid is persistently
waiting on an I/O to complete.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uTDIYtq4EHA.3708@.TK2MSFTNGP14.phx.gbl...
> The fact that your query is blocking DBCC shouldn't be an issue. If it
> were
> the other way around, you'd have a problem. That being said, the DBCC
> commands are ussually part of the Database Maintenance tasks. You should
> be
> running operations while the maintenance is going on. I'd look at the
> schedule of these two.
> The PAGEIOLATCH_SH wait happens, the question is what is the duration? If
> the latches are taking too long, that could be indicative of server memory
> starvation or a sub perfoming Disk I/O subsystem.
> Look at these performance counters:
> SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if the
> Target is larger than the Total, SQL Server would be assisted by having
> more
> memory available to it.
> SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and TotalLatchWait:
> if the waits are larger than 100 ms, you probably have some sluggishness.
> If they are greater than 1000 ms (1 second), then you may have a serious
> issue. The wait count is a tough one to determine because it is highly
> dependent on system resources and useage. However, if the wait times are
> high and the wait counts are low, then each request is suffering.
> Physical or Logical Disk: sec/Read and sec/Writ: these are the seek
> latency
> counters. They tell you the responsiveness of the disk. For an optimal
> disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O,
> on
> average. You might get some 100 ms or more per I/O, but should only be
> for
> short durations.
> Also take a look at the Disk %Read and %Write %Idle, this will tell you
> how
> busy your disk are for the various I/O types. You'll want to check out
> the
> Read/sec and Write/sec, most modern disks should be able to support up to
> 200 to 300 I/O operations per second. You'll aslo want to look at Read
> Bytes/sec and Write Bytes/sec to see if you are overloading the disk
> subsystem bandwidth.
> Also look at all of the counters for the SQL Server:Buffer Manager. These
> will tell you the distribution of the memory manager segments within the
> Buffer Pool. Look to see if any area is being overused.
> Hope this gives you some areas to look at.
> Sincerely,
>
> Anthony Thomas
>
> --
> "FrankM" <a@.b.c> wrote in message
> news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are currently experiencing huge performance problems when running
> stored
> procs that consists of several 'select into' queries of type:
> select A as AA,
> B as BB,
> sum(cast(C as decimal)) as CC
> into myResult
> from myTable
> where id = 1 and C <> ' '
> group by A, B
>
> Database size is >200gig.
> The query uses an index on (id, A, B)
> The execution plan looks like this:
> 44% insert
> 28% bookmark lookup
> 28% index scan
>
> The following blocking is reported:
> SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
> SP6 : system, background, DBCC
> SP10 (blocking) myResult (tab), myTable
> SP6 (blocked by SP10) myResult (tab)
>
> Can anyone explain what the system/dbcc task is doing and if this blocking
> causes the problem?
>
> I checked some of the counters but the only unusual value I found is a
> disk
> io of < 2MB which I understand is too low.
> Is there an easy way to improve this, would disk defrag or index reorg
> help?
>
> Any help is much appreciated,
> Frank
>|||Kevin, I believe it's the stored proc which runs in query analyzer.
"Kevin Stark" <SENDkevo97NO@.POTTEDhotMEATmailHERE.com> wrote in message
news:uqfcMdr4EHA.1192@.tk2msftngp13.phx.gbl...
> Frank -- what is the wait resource being reported for the I/O latch in
> question? I would be curious as to why a sleeping spid is persistently
> waiting on an I/O to complete.
>
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:uTDIYtq4EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > The fact that your query is blocking DBCC shouldn't be an issue. If it
> > were
> > the other way around, you'd have a problem. That being said, the DBCC
> > commands are ussually part of the Database Maintenance tasks. You
should
> > be
> > running operations while the maintenance is going on. I'd look at the
> > schedule of these two.
> >
> > The PAGEIOLATCH_SH wait happens, the question is what is the duration?
If
> > the latches are taking too long, that could be indicative of server
memory
> > starvation or a sub perfoming Disk I/O subsystem.
> >
> > Look at these performance counters:
> >
> > SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if
the
> > Target is larger than the Total, SQL Server would be assisted by having
> > more
> > memory available to it.
> >
> > SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and
TotalLatchWait:
> > if the waits are larger than 100 ms, you probably have some
sluggishness.
> > If they are greater than 1000 ms (1 second), then you may have a serious
> > issue. The wait count is a tough one to determine because it is highly
> > dependent on system resources and useage. However, if the wait times
are
> > high and the wait counts are low, then each request is suffering.
> >
> > Physical or Logical Disk: sec/Read and sec/Writ: these are the seek
> > latency
> > counters. They tell you the responsiveness of the disk. For an optimal
> > disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O,
> > on
> > average. You might get some 100 ms or more per I/O, but should only be
> > for
> > short durations.
> >
> > Also take a look at the Disk %Read and %Write %Idle, this will tell you
> > how
> > busy your disk are for the various I/O types. You'll want to check out
> > the
> > Read/sec and Write/sec, most modern disks should be able to support up
to
> > 200 to 300 I/O operations per second. You'll aslo want to look at Read
> > Bytes/sec and Write Bytes/sec to see if you are overloading the disk
> > subsystem bandwidth.
> >
> > Also look at all of the counters for the SQL Server:Buffer Manager.
These
> > will tell you the distribution of the memory manager segments within the
> > Buffer Pool. Look to see if any area is being overused.
> >
> > Hope this gives you some areas to look at.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > --
> >
> > "FrankM" <a@.b.c> wrote in message
> > news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > We are currently experiencing huge performance problems when running
> > stored
> > procs that consists of several 'select into' queries of type:
> >
> > select A as AA,
> > B as BB,
> > sum(cast(C as decimal)) as CC
> > into myResult
> > from myTable
> > where id = 1 and C <> ' '
> > group by A, B
> >
> >
> > Database size is >200gig.
> >
> > The query uses an index on (id, A, B)
> >
> > The execution plan looks like this:
> > 44% insert
> > 28% bookmark lookup
> > 28% index scan
> >
> >
> > The following blocking is reported:
> > SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
> > SP6 : system, background, DBCC
> >
> > SP10 (blocking) myResult (tab), myTable
> > SP6 (blocked by SP10) myResult (tab)
> >
> >
> > Can anyone explain what the system/dbcc task is doing and if this
blocking
> > causes the problem?
> >
> >
> > I checked some of the counters but the only unusual value I found is a
> > disk
> > io of < 2MB which I understand is too low.
> >
> > Is there an easy way to improve this, would disk defrag or index reorg
> > help?
> >
> >
> > Any help is much appreciated,
> > Frank
> >
> >
>

PAGEIOLATCH_SH performance problem

Hi,
We are currently experiencing huge performance problems when running stored
procs that consists of several 'select into' queries of type:
select A as AA,
B as BB,
sum(cast(C as decimal)) as CC
into myResult
from myTable
where id = 1 and C <> ' '
group by A, B
Database size is >200gig.
The query uses an index on (id, A, B)
The execution plan looks like this:
44% insert
28% bookmark lookup
28% index scan
The following blocking is reported:
SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
SP6 : system, background, DBCC
SP10 (blocking) myResult (tab), myTable
SP6 (blocked by SP10) myResult (tab)
Can anyone explain what the system/dbcc task is doing and if this blocking
causes the problem?
I checked some of the counters but the only unusual value I found is a disk
io of < 2MB which I understand is too low.
Is there an easy way to improve this, would disk defrag or index reorg help?
Any help is much appreciated,
Frank
The fact that your query is blocking DBCC shouldn't be an issue. If it were
the other way around, you'd have a problem. That being said, the DBCC
commands are ussually part of the Database Maintenance tasks. You should be
running operations while the maintenance is going on. I'd look at the
schedule of these two.
The PAGEIOLATCH_SH wait happens, the question is what is the duration? If
the latches are taking too long, that could be indicative of server memory
starvation or a sub perfoming Disk I/O subsystem.
Look at these performance counters:
SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if the
Target is larger than the Total, SQL Server would be assisted by having more
memory available to it.
SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and TotalLatchWait:
if the waits are larger than 100 ms, you probably have some sluggishness.
If they are greater than 1000 ms (1 second), then you may have a serious
issue. The wait count is a tough one to determine because it is highly
dependent on system resources and useage. However, if the wait times are
high and the wait counts are low, then each request is suffering.
Physical or Logical Disk: sec/Read and sec/Writ: these are the seek latency
counters. They tell you the responsiveness of the disk. For an optimal
disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O, on
average. You might get some 100 ms or more per I/O, but should only be for
short durations.
Also take a look at the Disk %Read and %Write %Idle, this will tell you how
busy your disk are for the various I/O types. You'll want to check out the
Read/sec and Write/sec, most modern disks should be able to support up to
200 to 300 I/O operations per second. You'll aslo want to look at Read
Bytes/sec and Write Bytes/sec to see if you are overloading the disk
subsystem bandwidth.
Also look at all of the counters for the SQL Server:Buffer Manager. These
will tell you the distribution of the memory manager segments within the
Buffer Pool. Look to see if any area is being overused.
Hope this gives you some areas to look at.
Sincerely,
Anthony Thomas

"FrankM" <a@.b.c> wrote in message
news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
Hi,
We are currently experiencing huge performance problems when running stored
procs that consists of several 'select into' queries of type:
select A as AA,
B as BB,
sum(cast(C as decimal)) as CC
into myResult
from myTable
where id = 1 and C <> ' '
group by A, B
Database size is >200gig.
The query uses an index on (id, A, B)
The execution plan looks like this:
44% insert
28% bookmark lookup
28% index scan
The following blocking is reported:
SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
SP6 : system, background, DBCC
SP10 (blocking) myResult (tab), myTable
SP6 (blocked by SP10) myResult (tab)
Can anyone explain what the system/dbcc task is doing and if this blocking
causes the problem?
I checked some of the counters but the only unusual value I found is a disk
io of < 2MB which I understand is too low.
Is there an easy way to improve this, would disk defrag or index reorg help?
Any help is much appreciated,
Frank
|||Frank -- what is the wait resource being reported for the I/O latch in
question? I would be curious as to why a sleeping spid is persistently
waiting on an I/O to complete.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uTDIYtq4EHA.3708@.TK2MSFTNGP14.phx.gbl...
> The fact that your query is blocking DBCC shouldn't be an issue. If it
> were
> the other way around, you'd have a problem. That being said, the DBCC
> commands are ussually part of the Database Maintenance tasks. You should
> be
> running operations while the maintenance is going on. I'd look at the
> schedule of these two.
> The PAGEIOLATCH_SH wait happens, the question is what is the duration? If
> the latches are taking too long, that could be indicative of server memory
> starvation or a sub perfoming Disk I/O subsystem.
> Look at these performance counters:
> SQL Server:Memory Manager TargetServerMemory and TotalServerMemory: if the
> Target is larger than the Total, SQL Server would be assisted by having
> more
> memory available to it.
> SQL Server:Latch AverageLatchWait, LatchWaitperSecond, and TotalLatchWait:
> if the waits are larger than 100 ms, you probably have some sluggishness.
> If they are greater than 1000 ms (1 second), then you may have a serious
> issue. The wait count is a tough one to determine because it is highly
> dependent on system resources and useage. However, if the wait times are
> high and the wait counts are low, then each request is suffering.
> Physical or Logical Disk: sec/Read and sec/Writ: these are the seek
> latency
> counters. They tell you the responsiveness of the disk. For an optimal
> disk I/O subsystem, these should be in the range of 10 to 15 ms per I/O,
> on
> average. You might get some 100 ms or more per I/O, but should only be
> for
> short durations.
> Also take a look at the Disk %Read and %Write %Idle, this will tell you
> how
> busy your disk are for the various I/O types. You'll want to check out
> the
> Read/sec and Write/sec, most modern disks should be able to support up to
> 200 to 300 I/O operations per second. You'll aslo want to look at Read
> Bytes/sec and Write Bytes/sec to see if you are overloading the disk
> subsystem bandwidth.
> Also look at all of the counters for the SQL Server:Buffer Manager. These
> will tell you the distribution of the memory manager segments within the
> Buffer Pool. Look to see if any area is being overused.
> Hope this gives you some areas to look at.
> Sincerely,
>
> Anthony Thomas
>
> --
> "FrankM" <a@.b.c> wrote in message
> news:%23KcYiYq4EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are currently experiencing huge performance problems when running
> stored
> procs that consists of several 'select into' queries of type:
> select A as AA,
> B as BB,
> sum(cast(C as decimal)) as CC
> into myResult
> from myTable
> where id = 1 and C <> ' '
> group by A, B
>
> Database size is >200gig.
> The query uses an index on (id, A, B)
> The execution plan looks like this:
> 44% insert
> 28% bookmark lookup
> 28% index scan
>
> The following blocking is reported:
> SP10: myuser, sleeping, select into, PAGEIOLATCH_SH
> SP6 : system, background, DBCC
> SP10 (blocking) myResult (tab), myTable
> SP6 (blocked by SP10) myResult (tab)
>
> Can anyone explain what the system/dbcc task is doing and if this blocking
> causes the problem?
>
> I checked some of the counters but the only unusual value I found is a
> disk
> io of < 2MB which I understand is too low.
> Is there an easy way to improve this, would disk defrag or index reorg
> help?
>
> Any help is much appreciated,
> Frank
>
|||Kevin, I believe it's the stored proc which runs in query analyzer.
"Kevin Stark" <SENDkevo97NO@.POTTEDhotMEATmailHERE.com> wrote in message
news:uqfcMdr4EHA.1192@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Frank -- what is the wait resource being reported for the I/O latch in
> question? I would be curious as to why a sleeping spid is persistently
> waiting on an I/O to complete.
>
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:uTDIYtq4EHA.3708@.TK2MSFTNGP14.phx.gbl...
should[vbcol=seagreen]
If[vbcol=seagreen]
memory[vbcol=seagreen]
the[vbcol=seagreen]
TotalLatchWait:[vbcol=seagreen]
sluggishness.[vbcol=seagreen]
are[vbcol=seagreen]
to[vbcol=seagreen]
These[vbcol=seagreen]
blocking
>