June 24, 2006 at 10:00 am
The CTO at my job asked the question "What does SQL Server 2000 do with 4 processors? Anything good?". I know that I can go get MS's thought on this, but I wanted to ask the troops in the field - what are your thoughts?
TIA
Cory
-- Cory
June 24, 2006 at 4:12 pm
SQL Server is a multithreaded application. It can use the multiple processors just like any multithreaded application can. In addition, for costly queries, SQL Server can use parallelism, the ability to break queries up across multiple processors. So yes, there is a gain for SQL Server using multiple processors.
K. Brian Kelley
@kbriankelley
June 25, 2006 at 5:05 pm
index maintanance takes advantage of multiple CPUs as well
June 25, 2006 at 8:48 pm
how does index maintanance take advantage of muliti cpus? does it do this "out of the box" can one define what processes use what cpus and when?
-- Cory
June 26, 2006 at 1:59 pm
You can set what processors SQL Server uses and you can use query hints to tell SQL Server to either parallel queries or not, but other than that, SQL Server pretty much controls things. But in this, it's not different than say IIS.
K. Brian Kelley
@kbriankelley
June 26, 2006 at 2:20 pm
Reindexing can be done in parallel. SQL Server does it out of the box (unless number of CPUs that SQL server is allowed to use is limited to 1).
June 27, 2006 at 12:02 am
As per BOL
Microsoft® SQL Server™ 2000 provides parallel queries to optimize query execution for computers having more than one microprocessor. By allowing SQL Server to perform a query in parallel by using several operating system threads, SQL Server completes complex queries with large amounts of data quickly and efficiently.
During query optimization, SQL Server looks for queries that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. After exchange operators are inserted, the result is a parallel query execution plan. A parallel query execution plan can use more than one thread, whereas a serial execution plan, used by a nonparallel query, uses only a single thread for its execution. The actual number of threads used by a parallel query is determined at query plan execution initialization and is called the degree of parallelism.
June 28, 2006 at 6:21 pm
DTS is also a multi-threaded native SQL Server 2000 component.
DTS Packages can be written to execute many tasks simultanaously rather than serially. For example, you can define multiple connections to extract data from different data sources, and each connection will be independently chugging away.
On a single processor machine, there would be no advantage with writing a DTS Package this way as each data connection would be opened and processed serially... but it would look really nice in the graphical DTS interface.
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply