SQL Server Performance Forum – Threads Archive

Query causes programs to halt

Hello, I have a query that I run to copy data every half hour from my production db to my archive db. That query will get 1000’s of rows. I just completed it and tried it, and it caused my programs that were using the production database to lock up. Apparently they couldn’t get information from the db while my query was running. Is there a way to break up my query to allow my production programs time to get in and get the information they need. We’re running a high speed sorter that needs to look up barcode info and divert boxes according to the info found in the database. This needs to happen in the subsecond time frame. It works fine when my large query for transferring data is not running. Here is a small excerpt from that query: /*RecDoc selected by datetimestamp*/
print ‘RecDoc’
update ArchRecDoc
set ArchRecDoc.RecDocID = ProdRecDoc.RecDocID, …rest of the columns
from [dcsterm].cryrep.dbo.RecDoc as ArchRecDoc inner join [dcsdb3].bbprod.dbo.RecDoc as ProdRecDoc on ArchRecDoc.RecDocID = ProdRecDoc.RecDocID
where ProdRecDoc.DateTimeCreated > @datetimedt insert into [dcsterm].cryrep.dbo.RecDoc select ProdRecDoc.RecDocID,…rest of the columns
from [dcsdb3].bbprod.dbo.RecDoc as ProdRecDoc
left join [dcsterm].cryrep.dbo.RecDoc as ArchRecDoc on ProdRecDoc.RecDocID = ArchRecDoc.RecDocID
where ArchRecDoc.RecDocID is null and ProdRecDoc.DateTimeCreated > @datetimedt Any help is greatly appreciated. Thanks in advance, Ben

Looks like you’re copying data to an "archive" database. Prioritize …

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan Looks like you’re copying data to an "archive" database. Prioritize … <hr height="1" noshade id="quote"></blockquote id="quote"> Bingo! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] To quote from the original question: <blockquote id="quote">quote:<hr height="1" noshade id="quote"> I have a query that I run to copy data every half hour from my production db to my archive db. <hr height="1" noshade id="quote"></blockquote id="quote"> Friday strikes again. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Well Frank, that was the start of the question and I kind of got lost after that.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Anyway, my suggestion is still that he has to prioritize – in this case, the archiving gets in the way of ‘production’, so the archiving has to occur at a quieter moment. Isn’t it an option when you schedule a job to have it execute whenever server activities are low? If it’s a 24/7 system, then perhaps log shipping might be an option. Other than that, you’re already doing the update before the insert, which is the correct order. Does RecDoc.DateTimeCreated in the archive database have an index? If there are any other indexes (besides the PK) on this table, you can drop them to speed up the insertion and update. If there are any triggers on this table, you should see if they are really needed. You might try to see how much time it takes if you use SET ROWCOUNT 100, and then let it run every minute. Try a couple of different numbers, and perhaps different intervals. You may see less interference with production. You can also keep the 30 minute interval, again use SET ROWCOUNT, and repeat in a WHILE EXISTS loop for the same criteria. You should also add a BREAK for when the loop has been running for 10 seconds – again, try some different timings.

]]>