Interrupt processing of a large insert process in SQL server 2000.

[Oleg Berfirer]

I'm running a resource-intensive stored procedure, which reads a file
with about 50,000 lines with a BULK INSERT into a temp table, then
goes through it and inserts a record for each line into another table.
While this procedure is running, SQL server stops accepting any other
requests coming from the website.
Question:
Is there a way to make SQL server "listen", or emulate an "interrupt"
to other requests while in the middle of a long intensive process?

I really appreciate your replies.
Thank you,
Oleg.

[Simon Hayes]

Re: Interrupt processing of a large insert process in SQL server 2000.

oberfire@excite .com (Oleg Berfirer) wrote in message news:<27da5ca7. 0310151439.147c 0975@posting.go ogle.com>...[color=blue]
> I'm running a resource-intensive stored procedure, which reads a file
> with about 50,000 lines with a BULK INSERT into a temp table, then
> goes through it and inserts a record for each line into another table.
> While this procedure is running, SQL server stops accepting any other
> requests coming from the website.
> Question:
> Is there a way to make SQL server "listen", or emulate an "interrupt"
> to other requests while in the middle of a long intensive process?
>
> I really appreciate your replies.
> Thank you,
> Oleg.[/color]

It's not clear from your description what "stops accepting" means, but
I guess that perhaps your load is locking the final table, so other
processes cannot access it. You could check this using sp_lock while
the load process is running.

If that is the case, then you may need to review the insert from the
temp table - are you processing each row individually in a loop or
cursor, for example? If possible, a single INSERT statement would be
the most efficient way to do it, but without more information on
exactly what you're doing and how, that's just a guess.

Simon

[Ian T]

Re: Interrupt processing of a large insert process in SQL server 2000.


"Oleg Berfirer" <oberfire@excit e.com> wrote in message
news:27da5ca7.0 310151439.147c0 975@posting.goo gle.com...[color=blue]
> I'm running a resource-intensive stored procedure, which reads a file
> with about 50,000 lines with a BULK INSERT into a temp table, then
> goes through it and inserts a record for each line into another table.
> While this procedure is running, SQL server stops accepting any other
> requests coming from the website.
> Question:
> Is there a way to make SQL server "listen", or emulate an "interrupt"
> to other requests while in the middle of a long intensive process?
>
> I really appreciate your replies.
> Thank you,
> Oleg.[/color]

Begin transaction followed by temporary table creation may lock the tempdb
until the transaction is committed.

[Oleg Berfirer]

Re: Interrupt processing of a large insert process in SQL server 2000.

Hi Ian,
I tried using a permanent table instead of a temp one, truncating it
before the transaction, but it did not solve the problem...
Please see a more detailed description of my setup in a reply to Simon.
Thanks!
Oleg.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Oleg Berfirer]

Re: Interrupt processing of a large insert process in SQL server 2000.

Hi Simon,
I have the following setup: There's a machine running IIS 5 and the
Application server with a CRM website; they're using a SQL 2000
database, located on a different
machine. The website is using the App server to interact with the SQL
server.
"Stops responding" means that no database-related action can be
performed on the website while this insert process is running. I tried
opening pages that are not in any way using the tables I'm inserting
into so it doesn't look like the problem is due to table locking. During
the process, IIS is not transmitting anything back to the user except
for status of the job when it's done, but until then a page i'm opening
basically hangs...
When I run the process, CPU utilization on the IIS machine is 1-2%, but
on the SQl machine it jumps up to 50-60%.
What puzzles me is that I tried running some queries right on the SQL
machine while it's running my large insert, and I do get results. So it
seems like the Queries which the APP server is requesting to run are
blocked, but other users can still use SQL server.
The more I dig into it, the more it seems like the problem is not in SQL
server, but in the App server.

Thanks very much for your suggestions.
Oleg.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Re: Interrupt processing of a large insert process in SQL server 2000.

Oleg Berfirer <oberfire@excit e.com> wrote in message news:<3f8ee1a5$ 1$199$75868355@ news.frii.net>. ..[color=blue]
> Hi Simon,
> I have the following setup: There's a machine running IIS 5 and the
> Application server with a CRM website; they're using a SQL 2000
> database, located on a different
> machine. The website is using the App server to interact with the SQL
> server.
> "Stops responding" means that no database-related action can be
> performed on the website while this insert process is running. I tried
> opening pages that are not in any way using the tables I'm inserting
> into so it doesn't look like the problem is due to table locking. During
> the process, IIS is not transmitting anything back to the user except
> for status of the job when it's done, but until then a page i'm opening
> basically hangs...
> When I run the process, CPU utilization on the IIS machine is 1-2%, but
> on the SQl machine it jumps up to 50-60%.
> What puzzles me is that I tried running some queries right on the SQL
> machine while it's running my large insert, and I do get results. So it
> seems like the Queries which the APP server is requesting to run are
> blocked, but other users can still use SQL server.
> The more I dig into it, the more it seems like the problem is not in SQL
> server, but in the App server.
>
> Thanks very much for your suggestions.
> Oleg.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

As you say, if you can get results by querying the database directly,
then your issue is most likely caused by whatever the app server is
doing. I would use Profiler to trace the SQL coming from the app
server, so you can see exactly what's happening at the time it's
blocked. That should give you some more information to help
investigate the problem.

Simon