Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tkizer
Almighty SQL Goddess
38200 Posts |
 Posted - 2003-11-05 : 15:02:53
|
| We've got a database server in our test environment that has multiple instances installed on it. It's got 4 CPUs. One of the instances (the performance instance where we are testing batches or queries) is configured to use CPU 0 and CPU 1, while the others are configured to use CPU 2 and CPU 3. While performing some work on the database server, I noticed that the performance instance was only using CPU 1. So I wondered if maybe I didn't correctly configure the CPUs in SQL Server. So I double checked my work and also performed a reboot just in case. But still only CPU 1 is being used. I then ran DBCC DBREINDEX on a large table that has a few indexes on it. I did this to see if CPU 0 would see some activity, which it did. So I know that this instance is configured correctly to use 2 CPUs, but I'm wondering why the test script which is running batches of queries using a Rational tool is only occurring on CPU 1. I'm wondering if this is occurring due to the "Minimum query plan threshold for considering queries for parallel execution" setting. The default is 5. I have not changed this setting. Should I set this value lower? Does this setting have anything to do with what I am seeing? All of the queries are from stored procedures. All of the tables at least have a primary key on them. The ones that are used the most have multiple indexes on them.Tara |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-05 : 16:15:49
|
| You're on pure hallowed dba ground...so a stupid questionIs the CPU maxed out..does it have to be maxed out to distribute work load (sound hokey to me).Could it be a thread thing?If sure you already read about the degrees of parrallesim...How much memory is there and how much data? Is the whole db in memory?Hey, if I had twin carbs on an Astin Martin, I'd want to use them...Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-05 : 16:29:53
|
| Yes CPU 1 is maxed out while the test cases are being run. And no it doesn't have to be maxed out in order to distribute the work load. There is 2GB of memory on the database server, but we've only allocated 1GB to this instance. The database is about 3GB in size, so no the entire thing is not in memory.Tara |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-11-06 : 03:13:13
|
| You can try changing the threshold to 1 and seeing if the query uses both processors then. If it does then Sql Server is determining that it's not worth using the other processor based on your (default) setting of 5 seconds of time for the single processor cost.-------Moo. :) |
|
|
|
|
|
|
Queries only using 1 CPU