SQL server concurrent accessing

Question

When sql server is receiving two queries (SELECT * From the_Same_Table), at exactly the same time, and if u have a server with multiple processors, Can sql server retrieve the data at the same time?

I am trying to understand what will happen if a cheap select statement that finish within .01 sec, and 1000 users run the same query exactly at the same time. I think what will happen that if the server is having four processors, then SQL server will serve the first four in .01 sec and serve next four users in 0.02 sec, extra.

Is that even close to what will actually happen?

I think I will try to use some kind of code and loggers to test that, or maybe there is reliable testing tools to do that.

Thanks

Answer 1

Each batch (request) that comes into SQL Server creates a task. Task are scheduled for execution and picked up by a worker. A worker is very similar to a thread. A task stays with a worker until it finishes, then it frees the worker to pick up another task. There are a limited number of workers in the system, configured by sp_configure 'max worker threads'. At a minimum there are 256 workers, from which about 35 are system. A worker needs a scheduler to run, and there is one single scheduler for each CPU core. Workers cooperate in sharing the scheduler.

Some tasks spawn subtasks, like parallel queries. These tasks are also queued for execution and need a worker to complete. A tasks that spawn subtasks cannot complete untill all the tasks it spawn are complete.

There are also some user action driven system tasks, like the login handshake. When a client opens a new conneciton, the handshake and login authentication/authorization are done by a task, which requires a worker.

When 1000 requests come to the server, 1000 tasks are created and queued for execution. The free workers pick up tasks and start executing them. As they finish one task, they pick up the next task, untill all tasks created by the 1000 requests complete.

The DMVs that show what's going on are:

• sys.dm_os_tasks
• sys.dm_os_workers
• sys.dm_os_schedulers
• sys.dm_exec_requests

These details are described in SQL Server Batch or Task Scheduling and in Slava's blog.

Further, once the task is in execution, the request will be compiled. Compilation will first look up the requests text in memory, and search for an existing compiled plan for a request with an identical plan. You can read my reply for Dynamically created SQL vs Parameters in SQL Server for a more detailed drill into how that happens. Also see Execution Plan Caching and Reuse. Once a plan is created, it is launched into execution. A query like SELECT ... FROM table will create a trivial plan that has a just a couple of operators that basically fetch each row and place it in the TDS stream back to the client. A query plan is a tree of operators, and the query is always executed by asking the root of the tree for the next row, in a loop until the root returns EOF. The query operators down the tree get more and more specific, until the bottom operator will be a physical access to the chosen access path (the index or heap chosen by the optimizer to satisfy the query). See SQL Statement Processing. The index access will always request the data from the buffer pool, never from disk. When the buffer pool does not have the requested page cached, a PAGEIOLATCH is placed on the page and a request to read the page is submitted to the IO subsystem. Subsequent requests for the same page will wait for this IO to complete, and once the page is in the buffer pool all other queries that need this page will it from the buffer pool. unused pages are evicted when the buffer pool need free pages, but if the system has enough RAM the page will never be evicted once loaded. Index and heap scan operations will requests read-aheads, anticipating that pages ahead of the current one in the page link-chain will be requested. Read aheads are limited by the index contiguos fragments, and this is when the index fragmentation comes into picture, as it reduces the size of read-ahead requests, see Understanding Pages and Extents.

Another dimension of the query execution is the logical locking of rows. For stability, a read may place row locks or range locks, depending on the isolation model, on the rows it reads, to prevent concurent updates while the query traverses a scan. Under SNAPSHOT isolation level the query will not request locks at all, but instead a version mark will be used to possible serve the data requested from the version store (see SQL Server 2005 Row Versioning-Based Transaction Isolation). Under READ UNCOMMITED isolation (or when nolock hint is used) the query does not requests locks on the rows it reads, but the reads are inconsistent if concurent updates occur (uncommited rows are read, same row may be read twice, or an existing row may be not read at all).

Answer 2

No, your assumption of sequential processing is not correct, this topic gets very complex and deep - trying to make what I know of it simple:

Each query has a thread assigned and the scheduler uses co-operative scheduling, not pre-emptive, so each thread can give up the allocation (quanta) of time before being forced to by the scheduler. Each query is going to have to make requests for data from the buffer pool, which can mean waiting on IO / network etc, so they will hit wait states and yield their quanta of processing time.

When a process is no longer waiting on the resource is can then re-enter the list of threads available to process and will then get another quanta of time to process the request on.

In effect your requests are run in parallel, but you could not deterministically predict the order in which the requests will finish.

To get real in-depth on this I'm thinking the SQL 2008 Internals book form Kalen Delaney (Adam Mechanic, Kimberley Tripp, Paul Randal) is a good starting point, or the older SQL Server 2000 Architecture book by Ken Henderson, which was also very low level.

I would have to refer back to course notes to remember the exact process - it get's to the point of asking - 'since you can not affect this directly, why do you ask?'