We have an SQL server 2000 server with four Intel Xexon cpu's. The
version of SQL Server is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
This database is an datawarehouse where multiple users are running an
combination of small and big (measured in query time) SELECT Queries.
The users run these queries through an webapplication. This is an ISAPI
DLL which run's queries on behalf of an visitor and returns a nice html
report.
The problem is that SQL Server only use one CPU. I measured it that
when three people log on, the total time spend before they get an
report is also multiplied with three. The ISAPI DLL dispatch an
seperate thread for each visitor and each thread uses it's own
connection to database. As far as I can see, the bottleneck is in SQL
Server. I was expecting an CPU usage for example 50%, handling the
queries at the same time.
SQL Server is configued as follows:
affinity mask 0
allow updates 0
awe enabled 0
c2 audit mode 0
cost threshold for parallelism 5
Cross DB Ownership Chaining 0
cursor threshold -1
default full-text language 1033
default language 0
fill factor (%) 0
index create memory (KB) 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 255
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
open objects 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 600
scan for startup procs 0
set working set size 0
show advanced options 1
two digit year cutoff 2049
user connections 0
user options 0
I changed the cost threshold for parallelism to 4, 3, 2, 1 and 0 with
no effect. I also changed the max degree of parallelism to 1 and 2,
with no effect.
What I am missing, I really don't know what is going on.
Thanks in advance.
Rene