1

This may be a bit of a noob question, but I'm curious:

How can a database handle multiple simultaneous requests, most notably reads? Is every request placed in a queue, so it really occurs one at a time? Or, with a multicore processor is it possible to actually handle multiple requests at the same time?

I guess an obvious extension to this question then would be: how much does having a multiple core CPU matter to a database server?

I'm specifically thinking about MS SQL Server 2008, but this question is probably applicable to most database servers.

2 Answers2

1

The execution internals are described spread across several topics in MSDN

Also Slava's blog, starting with SQLOS - unleashed has a lot of information.

To understand the fundamentals, a good starting point is Transaction Processing: Concepts and Techniques.

A short synthesis would go like this: each SQL batch creates a request. Requests are picked up by a task and tasks are executed by a worker. You can think at workers as similar to 'threads' (there are some differences). This implies that a batch (=request) runs on a single thread. Certain statements inside a batch can benefit from parallel execution, those statements will execute on multiple tasks, see Parallel Query Processing.

To answer whether multiple CPUs will benefit a database: it depends. I would say that "adding CPUs to a processor bound workload will increase the workload throughput and performance".

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
0

There are lots of different resources that are used to provide a database service (CPU, records on disk, memory locations, etc), and any time there's contention, yes, everything that needs to use that resource gets in line. Whenever there isn't contention (two threads on a multicore system, attempting to access different records in a table) then those requests can proceed in parallel until they contend for some resource again.

womble
  • 95,029
  • 29
  • 173
  • 228