Is SQL Server Multithreaded?

Just curious if SQL Server is considered a multithreaded application
Yes, in task manager in the process pane, it shows I am using 26 threads.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jayme" <jayloub@.comcast.net> wrote in message
news:1159878407.068638.128370@.m73g2000cwd.googlegr oups.com...
> Just curious if SQL Server is considered a multithreaded application
>
|||Of course. It would not be very useful for a real DBMS to be single threaded.
Linchi
"Jayme" wrote:

> Just curious if SQL Server is considered a multithreaded application
>
|||Well, on a multi-threaded OS like Windows anyway.
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> Of course. It would not be very useful for a real DBMS to be single threaded.
> Linchi
> "Jayme" wrote:
|||Hi Jayme
SQL Server is not only multi-threaded, it also has an asynchronous work
scheduling engine known as the "User Mode Scheduler" (UMS) in SQL 2000 or
the "SQL Server Operating System" (SQLOS) in SQL 2005.
Using this architecture it is quite common for SQL Server to have many
threads running concurrently, each of which is being controlled by the UMS /
SQLOS in terms or work priority etc. For example, if you have a few hundred
user connections performing user work continuously, you might have somewhere
from 60 to 200 threads running.
You can monitor how many threads SQL Server is using via the Performance
Monitor's Process object, Thread Count counter for the sqlservr instance.
Also, keep in mind that although the UMS / SQLOS prioritises which threads
should be running, Windows is also doing this at the O/S level, complicating
things slightly further...
There's also quite a bit of history behind the implementation of SQL
Server's multi-threading architecture (UMS / SQLOS). In the early days, SQL
Server was developed in partnership between Microsoft & Sybase (originally
also with Ashton Tate). The Microsoft team wanted to design SQL Server as a
simple, multi-threaded application which relied solely on Windows' thread
scheduling capability (to highlight this Windows NT capability). Sybase
didn't want to tie SQL Server to Windows & wanted to control thread
scheduling within the database engine. MS & Sybase ultimately split company
primarily over this issue & MS designed 6.0 & 6.5 as a simple multi-threaded
applications (with no UMS / SQLOS) but they ended up doing a back-flip &
going back to the approach Sybase wanted to pursue by implementing the UMS
in SQL 7.0 for performance reasons..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Jayme" <jayloub@.comcast.net> wrote in message
news:1159878407.068638.128370@.m73g2000cwd.googlegr oups.com...
> Just curious if SQL Server is considered a multithreaded application
>
|||Excellent post Greg! I liked the detail and history lesson.
Unlike other posts which are clearly from people who are uninformed,
and have nothing too add.
Thanks for sharing.
Izzy
Greg Linwood wrote:[vbcol=seagreen]
> Hi Jayme
> SQL Server is not only multi-threaded, it also has an asynchronous work
> scheduling engine known as the "User Mode Scheduler" (UMS) in SQL 2000 or
> the "SQL Server Operating System" (SQLOS) in SQL 2005.
> Using this architecture it is quite common for SQL Server to have many
> threads running concurrently, each of which is being controlled by the UMS /
> SQLOS in terms or work priority etc. For example, if you have a few hundred
> user connections performing user work continuously, you might have somewhere
> from 60 to 200 threads running.
> You can monitor how many threads SQL Server is using via the Performance
> Monitor's Process object, Thread Count counter for the sqlservr instance.
> Also, keep in mind that although the UMS / SQLOS prioritises which threads
> should be running, Windows is also doing this at the O/S level, complicating
> things slightly further...
> There's also quite a bit of history behind the implementation of SQL
> Server's multi-threading architecture (UMS / SQLOS). In the early days, SQL
> Server was developed in partnership between Microsoft & Sybase (originally
> also with Ashton Tate). The Microsoft team wanted to design SQL Server as a
> simple, multi-threaded application which relied solely on Windows' thread
> scheduling capability (to highlight this Windows NT capability). Sybase
> didn't want to tie SQL Server to Windows & wanted to control thread
> scheduling within the database engine. MS & Sybase ultimately split company
> primarily over this issue & MS designed 6.0 & 6.5 as a simple multi-threaded
> applications (with no UMS / SQLOS) but they ended up doing a back-flip &
> going back to the approach Sybase wanted to pursue by implementing the UMS
> in SQL 7.0 for performance reasons..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Jayme" <jayloub@.comcast.net> wrote in message
> news:1159878407.068638.128370@.m73g2000cwd.googlegr oups.com...