I should probably be able to find an answer to this but my Google-fu is weak today. When I have the same stored proc called multiple times by a web app, do the calls queue up or do they run independently?
A:
They would run independently. If they queued up, that would cause some massive scalability problems if you had a busy system that used lots of stored procedures.
This assumes of course that the sprocs aren't locking resources in a way that would cause one call to have to wait for another to finish.
Eric Petroelje
2009-09-23 17:06:47
+5
A:
Depends on the isolation level of what the stored procedure is doing. If the isolation level is set to READ UNCOMMITTED for all the transactions in the SP, there is no protection, and multiple threads can be performing the same transaction at the same time.
If it's set to a higher isolation level, then other threads may be locked out of the resources that your SP is dealing with until the transaction is completed, effectively "queuing" the other SP threads.
There is no explicit stored procedure queue though. As long as your database has free connections and resources available, it will spawn threads to satisfy requests.
womp
2009-09-23 17:07:21
Really the isolation level of the individual transactions and DML statements within the stored procedure, but otherwise accurate.
Christian Hayter
2009-09-23 17:19:25
Yeah I could probably make that clearer.
womp
2009-09-23 17:24:20
Edited for more clarity on transaction level vs. SP level.
womp
2009-09-23 17:26:28
Okay, that makes sense. Gracias!
Valkyrie
2009-09-23 18:01:24
Although technically accurate, I might quibble that the degree of "queueing" (or non-parallellism) which results from high isolation levels occurs at the level at which locks are created/released, not at the higher level of the individual queries themselves, and could justifiably be described as a totally different phenomenom from the "queueing" that most would interpret as behavior where an entire query cannot begin until others have completed.
Charles Bretana
2009-09-23 18:26:43
Depends on what operations. Read uncommitted only applies to reads ie selects. All data modifications lock regardless of the isolation level. So if multiple instances of the proc are trying to update the same rows, there will be blocking regardless of the isolation level.
GilaMonster
2009-09-23 20:51:35
+3
A:
Both. Each invocation of a stored procedure (more precisesly each request sent by the client) creates a task in SQL, visible in sys.dm_os_tasks. Tasks are assigned to schedulers (sys.dm_os_schedulers) and wait until a worker (sys.dm_os_workers) is available to run them. If the system is very busy then the tasks will queue up, and this is visible in the work_queue_count column of sys.dm_os_schedulers. For more details see Thread and Task Architecture.
Under normal operations though the queueing effect is not visible as the system immedeatly picks up the submitted tasks and start running them.
Clients can submit only one request per connection (MARS is the exception, not the rule). So from a client point of view, he must queue requests when using a connection, but this is hidden in the program control flow (ie. it must wait for a request to return before submitting a new one).
Remus Rusanu
2009-09-23 17:18:11
The link on Thread and Task Architecture is exactly the kind of thing I was searching for. Thanks!
Valkyrie
2009-09-23 18:02:35