June 29, 2003 at 8:50 pm
Is it possible to set connection priority with MSSQL 2000? The problem is this. I have a stored procedure that runs a huge insert to a single table which could take up to 10 minutes. During that time, other selects, inserts or updates to other tables takes forever, if not timeout. I am definate that the stored procedure is not locking any other tables. And the processor utilization is low. I am running on Dell PE4600 with dual 1.8Mhz Zeon, 4GB of RAM and RAID5 100GB. What am I doing wrong?
June 29, 2003 at 11:33 pm
There could be so many locks taken that locks are escalated to page or even table locks. You may want to run Profiler and look at lock acquisition and lock escalation.
The massive insert could maybe be broken down into smaller chunks that can be committed regularly and thus increase concurrency.
Cheers,
- Mark
June 30, 2003 at 7:42 am
Or you're disk bound, CPU can't work until the drives catch up.
Andy
April 7, 2005 at 12:24 am
I'm also having this problem. It's like (simplified version, that is)
I run one select-insert stored procedures, selecting from table A, inserting into table B, and then updating table A. This takes more than 10 minutes, and I'm ok with it.
In the meantime, some other user tries running simple selects and inserts on other tables - say tables X, Y and Z. But these sqls are all getting very slow.
Is there a way I can tell SQL Server to run initial stored procedure a bit slower, to make other jobs run fast? It is acceptable for the stored procedure to take 30 minutes or more. Something similar to setting priority in Operating System?
Thanks,
Laj
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply