SQL Server Performance

Sqlservr.exe shows little memory in task manager

Discussion in 'Performance Tuning for DBAs' started by agw, Sep 8, 2006.

  1. agw New Member

    This is a Windows 2003 X64 Enterprise with 32GB of memory running SQL 2000 SP4 32bit with hotfix SQL2000-KB899761-v8.00.2040-x86x64-ENU.exe to solve the AWE issue with using only half of the memory allocated.
    The issue I'm having is that at first when AWE was not enabled task manager would show the process sqlservr.exe at about 3,800,000 and the PF usage at about the same.
    After enabling AWE but before the patch the sqlservr.exe would show about 180,000 and the PF usage about 16GB; after applying the hot fix the results are sqlservr.exe at about 300,000 see and the PF usage about 30.8GB. I have the max memory set up at 30720.
    While everything seems to be working just fine and the performance counter seems to confirm that the SQL is using all of the allocated memory (3.0151e+012), it bugs me that the task manager process is not showing that. Is this a confirmed bug from Microsoft or is the server not using what is supposed to use.
    Any comments?
    Below is the documentation.

    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)

    name,minimum,maximum,config_value,run_value
    affinity mask,-2147483648,2147483647,0,0
    allow updates,0,1,0,0
    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,30720,30720
    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,0,0
    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,1,1
    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

  2. Luis Martin Moderator

    How do you set SQL Memory?.
    Don't use Task Manager to know how much memory SQL use. You have Performance Monitor for that.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. agw New Member

    Luis, I agree not to look at the Task Manager but that is my question.
    In the reserved memory I have 3.0151e+012 which makes no sense, does it to you? I thought that number was expressing KB.
    This is the wai I set it up using sp_configure advanced option 1
    name,minimum,maximum,config_value,run_value
    awe enabled,0,1,1,1
    max server memory (MB),4,2147483647,30720,30720
    The rest I posted earlier.
    One more notice is that from the SQL startup log I see:
    8 CPU detected
    4096MB Allocated
    AWE Enabled
    The 4096 is disconcerning...
  4. satya Moderator

    Sometimes you will tend to see such negative or unprecedented numbers with Task Manager and I would agree with Luis to run PERFMON (SYSMON) to get correct settings.

    Follow the 2 KBAs for more information:
    http://support.microsoft.com/kb/274750/
    http://support.microsoft.com/kb/110983/EN-US/

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. agw New Member

    Ok, so we all agree that Task manager is unreliable and buggy. Let's forget that issue.
    Now, I'd like to address two other issues:
    1) SQL log saying" 8 processor(s) and 4096 MB RAM Detected"
    It should really say 30720 MB RAM Detected, shouldn'it?

    2) Performance Monitor shows this amount for Total Allocated Memory of SQL Server instance
    (3.0151e+012) which in "e" notation should equal to 3,015,100,000,000
    This number makes no sense to me as even if it expresses bits the number of GB would be 351
    See this table:
    GB GB GB
    Source if KB if B if bits
    3.0151E+12 2,875,423 2,808 351

    Am I reading the wrong indicator or am I just converting wrong?

    Thanks for your insight.
    Andrea
  6. sas New Member

    Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
    May 31 2003 16:08:15 Copyright (c) 1988-2003
    Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    Dear all,
    We also face the same problem , on the task manager physical memory usage 20GB , but on the performance monitor process/working set/sqlsvr only 235 MB ( exactly same with task manager process memory usage by sqlsvr.exe) , unfortunelty page faults/sec on process and memory average 30 , is it indicate we should add more ram ?
    the box is dedicated for sql server .
    thx
    Sas
  7. joechang New Member

    pay attention to counters that matter,
    ie, do you have high disk IO, is high IO causing problems

    ignore silly errors, like the fact that task manager shows too low a value for sqlservr memory

    do you know what a 32-bit signed/unsigned integer is?
    do you know the range of values a 32-bit integer can represent?
    if some wrote a program that stores the memory used as a 32-bit int,
    what happens when the actual value is > 4G
  8. agw New Member

    Hi Joe, nice talking to you again.<br /><br />It's hard for me to ignore silly errors but I'll do my best <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />I don't have disk io problems, especially after all the work we did on the configuration.<br /><br />I like the quiz...let's see<br /><br />do you know what a 32-bit signed/unsigned integer is?<br />Signed has the MSB set to 1 for negative and 0 for positive (Most Significant Bit)<br />Unsigned does not<br />do you know the range of values a 32-bit integer can represent?<br />-2,147,483,648 and a maximum value of 2,147,483,647 (inclusive)<br />if some wrote a program that stores the memory used as a 32-bit int,<br />what happens when the actual value is &gt; 4G<br />It would overflow as 4G = 34,359,738,368 bits<br /><br />So are you going to tell me what (3.0151e+012) really means for total memory?<br />Even if I take off the last 0 and make it (3.0151e+011) I still get 35G which is more than there is in the server.<br /><br />Andrea
  9. joechang New Member

    well apparently it does not cause an overflow error but rather just takes the lower 32/31-bits of the actual 64-bit value
  10. gurucb New Member

    When AWE is enabled on the server, I believe they use different set of API to allocate memory and task manager is not counting those memory allocations.

    If AWE is enabled, in perfmon look for

    Target server memory and total server memory.

    In sql server 2000 these values should be same with awe is enabled. Since in sql server 2005 AWE is dynamic there are chances that Target and Total may vary. Along with that if we look at Available MBYTES on box it should tell us whether sql srever is using memory or not.

    The other option would be to run dbcc memorystatus and check Target and total there and they should be equal to Max server memory set.


    As regards to logs in Errorlog aroung 4 GB allocated, can you please provide entire log message as I have never seen sql server log that sort of message indicating that it had allocated 4 GB of memory.

  11. agw New Member

    Joe, so you're saing that performance monitor is buggy also...

    Gurucb, the entire log is posted on the first post. You'll see the memory allocation there.
    I think you may have a point in the way task manager counts the memroy under AWE.
    Here is the result of DBCC memorystatus:

    Buffer Distribution,Buffers
    Stolen,2360
    Free,1876
    Procedures,20574
    Inram,0
    Dirty,12114
    Kept,0
    I/O,0
    Latched,834
    Other,3860512

    (9 row(s) affected)

    Buffer Counts,Buffers
    Commited,3898270
    Target,3898270
    Hashed,3873460
    InternalReservation,158
    ExternalReservation,0
    Min Free,256
    Visible,433168

    (7 row(s) affected)

    Procedure Cache,Value
    TotalProcs,7145
    TotalPages,20574
    InUsePages,8836

    (3 row(s) affected)

    Dynamic Memory Manager,Buffers
    Stolen,22934
    OS Reserved,1048
    OS Committed,1026
    OS In Use,1000
    General,2930
    QueryPlan,20412
    Optimizer,0
    Utilities,11
    Connection,229

    (9 row(s) affected)


    Global Memory Objects,Buffers
    Resource,2134
    Locks,118
    XDES,46
    SQLCache,508
    Replication,2
    LockBytes,2
    ServerGlobal,31

    (7 row(s) affected)


    Query Memory Objects,Value
    Grants,0
    Waiting,0
    Available (Buffers),316482
    Maximum (Buffers),316482

    (4 row(s) affected)

    Optimization Queue,Value
    Optimizing,0
    Waiting,0
    Available,32
    Maximum,32

    (4 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  12. gurucb New Member

    Looking at memory status sql server max server memory and committed are 30 Gigs. so sql server should be using 30 Gigs.

    I think when AWe is enabled since sql server locks pages (and can not be paged) windows internally maintains all that memory and does not show that in task manager under sql server.



Share This Page