INSERT query using linked servers is slow.

Good day everyone,
I am setting up a simple data mart on a server running SQL 2005. It gets
updated nightly from another server with SQL 2000. Originally both database
s
were on the same server under 2000. Now that they are on different servers,
some of the INSERT queries seem to be running for an abnormally long time (s
o
long that I end up having to kill them).
All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
from [Local-Tables]". Most only join together 3 or 4 tables, using key
fields. Only a couple of them are running long, the others complete in abou
t
the same time as before. The problem tables are not at all large compared t
o
the ones that work fine, and in some cases even have less activity (new
records).
I am pretty sure the two servers are linked correctly, as the majority of
these SQL commands still work fine. The remote query timeout parameter in
sp_configure has been set to 0, since the default of 600 was causing
problems. Other than that, no changes have been made on either server.
Any suggestions for other things I might check? Thanks in advance for your
help.Couple of things:
Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TRAN
on linked servers.
Have you checked all the usual rules which apply to large INSERTs? eg
- presumably there is no-one else logged in to the database when you are
doing these large inserts ie no danger of locking.
- are there a lot of indexes on the table you're inserting to; this will
slow things down
- are there any triggers firing? Think about disabling them
- is there any other audit stuff going on, traces etc?
- make sure all table names are fully qualified eg
server01.northwind.dbo.authors (presumably you have to do this anyway)
- if you've got IDENTITY columns, particularly as primary keys on the target
table, I believe these _can_ cause hotspots, although these are supposed to
be a minor concern on modern hardware
- think about breaking up your inserts, say 10,000 rows at a time so you can
keep track of their progress. I've seen techniques for doing this in loops
on the web using either SET ROWCOUNT or TOP
Hope that helps.
Let me know how you get on.
Damien
"Chris F" wrote:

> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both databa
ses
> were on the same server under 2000. Now that they are on different server
s,
> some of the INSERT queries seem to be running for an abnormally long time
(so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in ab
out
> the same time as before. The problem tables are not at all large compared
to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for you
r
> help.
>|||Chris
How much data do you insert?
Consider script out all indexes (remove them) and run the INSERT ,now that
after inserting re-create all indexes
"Chris F" <ChrisF@.discussions.microsoft.com> wrote in message
news:18089707-C1D6-4296-B937-C17D34DCAE65@.microsoft.com...
> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both
> databases
> were on the same server under 2000. Now that they are on different
> servers,
> some of the INSERT queries seem to be running for an abnormally long time
> (so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in
> about
> the same time as before. The problem tables are not at all large compared
> to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for
> your
> help.
>|||Thanks for your help.
Actually I am not using any form of BEGIN TRAN, since I keep getting a 7391
error in all cases. I am running a stored procedure consisting of several
delete and insert statements. Now that they are through the backlog (from
not having run for several days) all but one of the procedures have
acceptable run times.
Going down your list of checks, there are no other users, the only index is
the PK, no triggers, no audits/traces, no identity columns. I have been
fully qualifying the table on the remote server but not the local one where
the stored proc kicks off, I can try this and see if it helps. Will also
look at breaking up the query (I need to wait until the current run finishes
,
I found out over the wend that it does eventually). The only table we ar
e
still having problems with is the largest in the DB.
"Damien" wrote:
> Couple of things:
> Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TR
AN
> on linked servers.
> Have you checked all the usual rules which apply to large INSERTs? eg
> - presumably there is no-one else logged in to the database when you are
> doing these large inserts ie no danger of locking.
> - are there a lot of indexes on the table you're inserting to; this will
> slow things down
> - are there any triggers firing? Think about disabling them
> - is there any other audit stuff going on, traces etc?
> - make sure all table names are fully qualified eg
> server01.northwind.dbo.authors (presumably you have to do this anyway)
> - if you've got IDENTITY columns, particularly as primary keys on the targ
et
> table, I believe these _can_ cause hotspots, although these are supposed t
o
> be a minor concern on modern hardware
> - think about breaking up your inserts, say 10,000 rows at a time so you c
an
> keep track of their progress. I've seen techniques for doing this in loop
s
> on the web using either SET ROWCOUNT or TOP
>
> Hope that helps.
> Let me know how you get on.
>
> Damien
> "Chris F" wrote:
>