effects of inserting while selecting

We have a situation where a burst of inserts occurs while a select is being
done. This causes both clients to time out. As a whole does selecting from a
table during the time that inserts are being down slow down the inserts? or
do we have some indexing problems?
thanksmlblastvm wrote:
> We have a situation where a burst of inserts occurs while a select is being
> done. This causes both clients to time out. As a whole does selecting from a
> table during the time that inserts are being down slow down the inserts? or
> do we have some indexing problems?
> thanks
My money is on an indexing problem... Review the execution plan for
some of your SELECT statements - any scans, particularly table scans,
are likely sources of contention and opportunities for improved indexing.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||the execution plans look fine. One application is performing at least 50,000
inserts in succession. The other application is reading around 22000 rows.
Actually to be more specific here is what happens. Application A reads out
20k records from Table A and then inserts around that many to the database
(different table though). Application B at the same time inserts records into
Table A. Do the inserts from one application slow down the inserts from the
other application because the heads have to move around?
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > We have a situation where a burst of inserts occurs while a select is being
> > done. This causes both clients to time out. As a whole does selecting from a
> > table during the time that inserts are being down slow down the inserts? or
> > do we have some indexing problems?
> > thanks
> My money is on an indexing problem... Review the execution plan for
> some of your SELECT statements - any scans, particularly table scans,
> are likely sources of contention and opportunities for improved indexing.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> the execution plans look fine. One application is performing at least 50,000
> inserts in succession. The other application is reading around 22000 rows.
> Actually to be more specific here is what happens. Application A reads out
> 20k records from Table A and then inserts around that many to the database
> (different table though). Application B at the same time inserts records into
> Table A. Do the inserts from one application slow down the inserts from the
> other application because the heads have to move around?
>
Well, inserting data into a database certainly does produce disk I/O,
particularly against the transaction log. The rule of thumb is that the
transaction log and database files are place on seperate volumes, with
the transaction log being on an I/O channel that provides the fastest
possible write throughput.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In our case the logs are on separate volumes. I assume based on what you are
saying that doing a select while inserts are being done will have an impact
on both the select and the insert?
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > the execution plans look fine. One application is performing at least 50,000
> > inserts in succession. The other application is reading around 22000 rows.
> > Actually to be more specific here is what happens. Application A reads out
> > 20k records from Table A and then inserts around that many to the database
> > (different table though). Application B at the same time inserts records into
> > Table A. Do the inserts from one application slow down the inserts from the
> > other application because the heads have to move around?
> >
> Well, inserting data into a database certainly does produce disk I/O,
> particularly against the transaction log. The rule of thumb is that the
> transaction log and database files are place on seperate volumes, with
> the transaction log being on an I/O channel that provides the fastest
> possible write throughput.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> In our case the logs are on separate volumes. I assume based on what you are
> saying that doing a select while inserts are being done will have an impact
> on both the select and the insert?
>
I was simply responding to your question about the "heads moving around".
An INSERT can block a SELECT if that insert has locked data pages (or
even the entire table) that the SELECT is attempting to read. The
SELECT will be forced to wait until the lock has been released.
Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
is currently reading, the INSERT will potentially have to wait. It all
depends on the lock types being used. Having good indexes in place to
support the SELECT statements will help to alleviate some of this.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I guess the real question I have is this. In a case where locking is not
occuring is it reasonable to assume that selecting from a database while
doing inserts to it will cause both the select and insert to take longer than
if they were to be done independantly. If that is the case what exactly
accounts for this?
thanks
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > In our case the logs are on separate volumes. I assume based on what you are
> > saying that doing a select while inserts are being done will have an impact
> > on both the select and the insert?
> >
> I was simply responding to your question about the "heads moving around".
> An INSERT can block a SELECT if that insert has locked data pages (or
> even the entire table) that the SELECT is attempting to read. The
> SELECT will be forced to wait until the lock has been released.
> Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
> is currently reading, the INSERT will potentially have to wait. It all
> depends on the lock types being used. Having good indexes in place to
> support the SELECT statements will help to alleviate some of this.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> I guess the real question I have is this. In a case where locking is not
> occuring is it reasonable to assume that selecting from a database while
> doing inserts to it will cause both the select and insert to take longer than
> if they were to be done independantly. If that is the case what exactly
> accounts for this?
> thanks
You will ALWAYS have locking - that locking may or may not cause
contention, which will appear to slow things down.
If things slow down during these large inserts, the only reasons that I
can think of are:
1. Locks being created by the INSERT are blocking other processes
2. The disks are not capable of keeping up with the I/O demands
produced by the influx of new data from the INSERT
3. The new data from the INSERT is requiring the database and/or
transaction log file to grow, resulting in a delay while that growth
takes place.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I would add:
4) Not enough memory to hold all the pages required for the insert and all
the pages required for the select in memory at once causing extra IO and
processor activity swapping pages in and out.
5) Enough data being transferred in and out to cause network contention on
the TDS connections
6) Becoming processor bound - especially if the select requires sorting of
results
7) Checkpoint interval low enough so inserts are causing frequent
checkpoints saturating the mdf disk drive
In general, if you're running that close to the margins, you probably need
to bump up your timeouts.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45537D29.2060702@.realsqlguy.com...
> mlblastvm wrote:
>> I guess the real question I have is this. In a case where locking is not
>> occuring is it reasonable to assume that selecting from a database while
>> doing inserts to it will cause both the select and insert to take longer
>> than if they were to be done independantly. If that is the case what
>> exactly accounts for this?
>> thanks
> You will ALWAYS have locking - that locking may or may not cause
> contention, which will appear to slow things down.
> If things slow down during these large inserts, the only reasons that I
> can think of are:
> 1. Locks being created by the INSERT are blocking other processes
> 2. The disks are not capable of keeping up with the I/O demands produced
> by the influx of new data from the INSERT
> 3. The new data from the INSERT is requiring the database and/or
> transaction log file to grow, resulting in a delay while that growth takes
> place.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com