I am running MS SQL 2000 server. The table involved is only about
10,000 records. But this is the behavior I am seeing.

The local machine is querying the table looking for a particular
record. Everything works fine.
The remote amchine goes to clear the table and then insert all 10,000
records, into the table the following happens.

1) the local machines queries do not compilete until the remote
machine is done.
2) the remote machine can take up to 6 minutes to do these 10,000
insert operations. So nothing on the local machine works right for
these 6 minutes.

I do not have access to the remote machines source to see what is
running but I am told it is simply a for loop with a insert query in
it. Nothing of a locking natture.

Any idea the types of things I should look for to track this down? I
found this by doing SQL profiler profiling and finding the remote
operations. Turn these operatiiosn off and the local machine works
fine again, with no other action.

Thanks,
David

On Fri, 25 Jan 2008 15:02:52 -0800 (PST), quincy451 (AT) yahoo (DOT) com wrote:

I take it the code on the remote server is something like this:
truncate table SomeTable
for i=1 to 10000
Insert One Row
next i

Inserting a record will put an Exclusive lock on the table. From BOL:
Exclusive (X) Used for data-modification operations, such as INSERT,
UPDATE, or DELETE. Ensures that multiple updates cannot be made to the
same resource at the same time.

If you don't have control over the remote server process, you will
have to live with it. If you do have control, you could investigate
more efficient data import strategy (perhaps BCP) as well as more
acceptable timing (2AM).

-Tom.

On Jan 25, 9:25*pm, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote:
So you are telling me INSERT in a loop like that will lock and unlock
the table
repeatedly, but possibly because of the tightness of the loop never
let me in
to get my stuff done. And are you saying UPDATE, and DELETE work the
same
way? Now for the dumb question: What is BOL: Exclusive (X)? I guess
it
remains a open question what might make the remote side take 6 minutes
to
complete these insert operations. But knowing what will lock the
other side
out is very important.

Well, the other side is under my influence. I am the developer of the
local
application. The other side is someone else's code for which they
have
full control. I can advise and point in a different direction, if I
have that direction,
and most likely get things done. We are currently doing the late
night trick But
not always...

On Jan 25, 10:38*pm, quincy... (AT) yahoo (DOT) com wrote:
Ok I found this reference in the documentation. I am wondering if
locking hints on
the select query on the local side my help this?

On Fri, 25 Jan 2008 20:59:06 -0800 (PST), quincy451 (AT) yahoo (DOT) com wrote:

My gut feeling is you should try to improve the Insert side of the
equation, not the Select side.
BCP or other bulk inserts (a proper DTS package, for example) of 10000
rows should happen in a few seconds, not in 360 of them.

-Tom.

Locking hints can have some unexpected behavior.

In particular, the use of (nolock) during your SELECT could result in
either missing rows, or selecting the same row twice. This is due to
the way SQL Server will split & re-arrange pages during the INSERT
operation. Because some pages are being split and re-arranged, the
SELECT...(nolock) will not realize the pages are being rearranged, and
either miss, or skip certain rows/pages.

As a result, (nolock) is helpful for ad hoc queries, but in many
mission-critical production roles, (nolock) can cause many problems.

While the locking is happening, you can you sp_lock to see what
objects are being locked, and sp_who2 to verify the user/process
causing those locks.

I would highly recommend working to change the way the INSERT is done,
if there is anyway to do that. One possibility would be to have the
FOR insert into a temp table, then use INSERT INTO [Production Table]
SELECT...FROM [Temp Table] . This would allow for the insert to
occur in one operation, rather than 10,000 smaller inserts.


On Jan 25, 11:59 pm, quincy... (AT) yahoo (DOT) com wrote:

(quincy451 (AT) yahoo (DOT) com) writes:
That may be what Tom is saying, but, no, that it's not the way it works.

A single INSERT should only lock what is needed to insert the row. However,
if the remote process performs the DELETE and the INSERTs in a single
ransaction, the result is the same. As long as the inserted rows have
not been committed, you cannot read any rows from the table.

And, I like to add, nor are you interested in reading any rows. I don't
know what data this is about, or how much that changes when the remote
process refreshes the data. But if you were able to perform a SELECT
when the old rows have been deleted, and only 10 rows have been inserted,
you SELECT would probably come back empty. Which cannot be right.

So the remote process is probably right in keeping all in one transaction
and locking you out. But six minutes to load 10000 rows is not acceptable.

I don't think there is not much you can do on your side. Had you been on
SQL 2005, snapshot isolation could permit you retrieve the old version of
the data until the remote process has completed. But on SQL 2000, you only
work with the author of the remote process to see what it can do to
improve.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Without seeing exactly what your code /configuration is like: You could try
such things as :
1)BULK INSERT strategy.
2) use of TABLOCK
3)Potentially change to Simple Recovery (although I strongly warn you to
consider whether this is acceptable)
4)Break the BULK INSERTs into smaller chunks

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




<quincy451 (AT) yahoo (DOT) com> wrote