SQL Stored Procedure Question???
-
What will happen if 1000 users trying to execute the stored procedure at a time? How it will work? Single thread or multi thread or FIFO manner?
A person might be able to play without being creative, but he sure can't be creative without playing.
-
What will happen if 1000 users trying to execute the stored procedure at a time? How it will work? Single thread or multi thread or FIFO manner?
A person might be able to play without being creative, but he sure can't be creative without playing.
When multiple users try executing the same stored procedure, multiple instances will start running asynchronously. What happens next depends on the body of the stored procedure: if different instances of the stored procedure only read the data, or if they update distinct rows, all instances would run asynchronously to completion. Otherwise, some of them would block, following the usual rules of locking for your transaction isolation level. As a consequence, you can also force synchronization in the body of a stored procedure by selecting for update (or selecting with rowlock, etc.)
-
What will happen if 1000 users trying to execute the stored procedure at a time? How it will work? Single thread or multi thread or FIFO manner?
A person might be able to play without being creative, but he sure can't be creative without playing.
When a task request is received, SQL Server will attempt to allocate the task to an available worker thread. If no worker thread is available, a new one will be created to handle the task, up to the configured maximum number of threads. Once that maximum limit has been reached, task requests will queue until a thread becomes available. As you might guess, it's quite a lot more sophisticated than that, but that is essentially what it boils down to. If you want to know more detail, there are plenty of articles on Google that will explain it in as much detail as you want.