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
> >
> >
>
.