January 12, 2004 at 3:24 am
Dear all,
now we finally got the 2-Xeon Hyperthreading Server, but SQL Server 2000 does not use its processors! Apart from parallel execution plans for a single query, which may be avoided by the optimizer for good reason, I at least expected the Server to run two parallel queries for two concurrently working users.
The users call a stored proc which reads production data and writes some stuff in tempdb, but should not lock anything substantial. When user1 calls the SP, it takes roughly 8 seconds and puts a load of 25% on the total CPU. If user2 calls the same SP while user1 is busy, the total CPU load goes to about 27% and the total time for each of the users is about 16 secs.
Apparently only one CPY is used at any time. However, the load is not constantly assigned to a single CPU, eachof the 4 CPUs does some stuff at some time, but the total load never goes over 30%.
OS is Win2003. "cost threshold for parallelism" is 5, "max worker threads" is 255.
Any ideas ?
January 12, 2004 at 12:46 pm
Run performance monitor to see whether there are too much workloads on the I/O disks your database reside on. I highly suspect that.
Increase the "cost threshold for parallelism" from 5 to 8 to see any performance improvement.
January 13, 2004 at 4:05 am
Hello,
I have absolutely the same problem - 2-Xeon server (4 virtual CPU-s), MS-SQL 2000 Enterprise Edition, Win2000 Advanced Server... And all 4 CPU-s are used up to 30-35%. Even when different users run queries on different databases, stored on different hard drives !
January 14, 2004 at 2:36 am
> Even when different users run queries on different databases, stored on different hard drives
So it can not be Harddisk I/O. My disks are not accessed that much either. I wonder what stops SQL-Server from using more processors.
Please help
January 14, 2004 at 3:32 am
I suspect there is a wrong semaphore (or similar multithreading control object) in MS-SQL that stops the processes and discredit the SMP.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply