SQL Server Performance

Upgraded Server not using resources available

Discussion in 'Performance Tuning for DBAs' started by born2swoop, Mar 29, 2006.

  1. born2swoop New Member

    We just bought a new server to upgrade our SQL server. The new server has 32GB ram and 4 Xeon processors running Windows Enterprise Server 2003 and SQL 2000 Enterprise 8.00.2040 (sp4) with AWE hotfix applied.

    AWE is enabled on SQL 2000, Boot.ini includes the following options:

    multi(0)disk(0)rdisk(0)partition(1)WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE

    SQL server came up and seemed to be running fine, configured to use all 8 processors, use as much memory as it wants and boost priority on Windows. Under load, it does not appear to be using all available resources. Processor traces show that it is using 4 of the 8 virtual processors (at about 25-40%), with the other 4 hanging out around 2-5%. The prior server was also a quad Xeon and did not have this issue. All 8 processors seemed to be the same on the traces.

    Also, both Target Server Memory and Total Server Memory on the server traces are hanging around at 1668272 or thereabouts, with Task Manager showing about 1.7 GB being used. The load on the server, which handles about a dozen databases, should be plenty to keep most of the 32GB of ram busy, but the traces seem to indicate that the server does not see the extra RAM.

    In short, it seems to be acting as if it is SQL 2000 Standard instead of SQL 2000 Enterprise, with no AWE enabled.



  2. joechang New Member

    what is the output from sp_configure ?
    include the advanced options
  3. born2swoop New Member

    Output of sp_configure is:

    name minimum maximum config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    affinity mask -2147483648 2147483647 0 0
    allow updates 0 1 1 1
    awe enabled 0 1 1 1
    c2 audit mode 0 1 0 0
    cost threshold for parallelism 0 32767 5 5
    Cross DB Ownership Chaining 0 1 0 0
    cursor threshold -1 2147483647 -1 -1
    default full-text language 0 2147483647 1033 1033
    default language 0 9999 0 0
    fill factor (%) 0 100 0 0
    index create memory (KB) 704 2147483647 0 0
    lightweight pooling 0 1 0 0
    locks 5000 2147483647 0 0
    max degree of parallelism 0 32 0 0
    max server memory (MB) 4 2147483647 2147483647 2147483647
    max text repl size (B) 0 2147483647 65536 65536
    max worker threads 32 32767 255 255
    media retention 0 365 0 0
    min memory per query (KB) 512 2147483647 1024 1024
    min server memory (MB) 0 2147483647 0 0
    nested triggers 0 1 1 1
    network packet size (B) 512 32767 4096 4096
    open objects 0 2147483647 0 0
    priority boost 0 1 1 1
    query governor cost limit 0 2147483647 0 0
    query wait (s) -1 2147483647 -1 -1
    recovery interval (min) 0 32767 0 0
    remote access 0 1 1 1
    remote login timeout (s) 0 2147483647 20 20
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2147483647 600 600
    scan for startup procs 0 1 0 0
    set working set size 0 1 0 0
    show advanced options 0 1 1 1
    two digit year cutoff 1753 9999 2049 2049
    user connections 0 32767 0 0
    user options 0 32767 0 0


    Sorry for the crappy formatting, but I can't figure out how to get a fixed width font.
  4. joechang New Member

    try the SQL Server error log, defaults to C:program FilesMicrosoft SQL ServerMSSQLLOG

    top line should be something like:

    2006-03-29 08:59:09.21 server Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
    May 13 2005 18:33:17
    Copyright (c) 1988-2003 Microsoft Corporation
    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

  5. born2swoop New Member

    Wow, excellent tip! I found the error message:

    Cannot use Address Windowing Extensions because lock memory privilege was not granted.

    I'm looking for why/where this isn't granted now.
  6. born2swoop New Member

    Ok, we've granted lock pages in memory to the SQL service account, we just have to wait until off hours for a restart. Hopefully that solves the memory issue. The brilliant part of this is that Books Online discusses this on the managing AWE page, it's just hidden behind a little + sign. DOH!

    I'm still not sure why SQL server is only using 4 of the 8 virtual CPU's. The logs show the startup going like:

    Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
    May 13 2005 18:33:17
    Copyright (c) 1988-2003 Microsoft Corporation
    Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    SQL Server is starting at priority class 'high'(8 CPUs detected).
    Cannot use Address Windowing Extensions because lock memory privilege was not granted. (Should be fixed on restart)
    SQL Server configured for thread mode processing.
    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
    Attempting to initialize Distributed Transaction Coordinator.
    Starting up database 'master'.
    Using 'SSNETLIB.DLL' version '8.0.2039'.
    etc. into starting all of the databases and ports.
  7. born2swoop New Member

    Update:

    Overnight reboot proved effective, server is now happily using 32GB of ram.

    Unfortunately, even though SQL server reports that it sees (and is using) 8 CPUs, traces still show it to be only using 4 CPUs.
  8. joechang New Member

    i would not worry about this
    SQL Server does not benefit much from the HT feature on Xeon processors
    there is some indication that S2K5 does benefit from HT on the new Monticeto, but i am trying to get confirmation from HP on this
  9. sundeip New Member

    Hi.

    What will be the impact on performance, if SQL server starts in "NORMAL" mode?

    In our setup log files shows like this.

    SQL Server is starting at priority class 'normal'(8 CPUs detected).

    And in the above reply it shows as follows.

    SQL Server is starting at priority class 'high'(8 CPUs detected).

    Thanks
    Sandy




  10. joechang New Member

    if the server is dedicated to SQL Server, then it doesn't really matter, as there are no other processes that need cpu time,
  11. sundeip New Member

    We have dedicated 14GB of RAM out of 16GB to SQL server.



    Thanks,

    Sandy

Share This Page