How does SQL Server maintain connections?

Question

Does SQL Server 2005 create one thread per connection? or How does it manage connections?

Answer 1

No, it has a pool of threads, which get created or terminated as necessary. Connections are managed separately from worker threads that do the actual query execution. A query can also result in more than one thread being used for parallel execution plans.

Some older unix-based DB's, such as Informix SE or older versions of Postgres would fork a single process per connection, maintaining shared data structures by attaching to a shared memory pool. Oracle can also work in 'Shared Server' mode, which (IIRC) also works this way.

The third book in the 'Guru's Guide' series has a detailed look at the internal architecture of SQL Server 2000; in most respects SQL Server 2005 is not significantly different. Amongst other things, this discusses thread management. As a bonus, the first half of the book has a good overview of key aspects of Win32 systems programming, presented as a backgrounder for the architectural review of the database server.