SQL Server only use one cpu

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Rene

    SQL Server only use one cpu

    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

  • Stu

    #2
    Re: SQL Server only use one cpu

    Rene,

    In Enterprise Manager, under the properties for for your registered
    server, there is an option for Processor. Check to make sure that all
    processors are checked, and that Parallelism is being applied to all
    processors.

    I'm sorry if this seems obvious, but that was the first thing that I
    thought of.

    HTH,
    Stu

    Comment

    Working...