Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58

    Wink Unanswered: SQL Memory issue

    Hi all,
    I have a vmWare virtual server on IBM X440 series dedicated for sql with following config...

    Win 2003 OS,
    SQL 2000 Enterprises edition with SP3a,
    8 GB RAM,
    4 Xeon Procsessor,
    32 GB of DB size. and 100+ concorrunt users

    Now when i check the memory utilization of sqlservr exe in taskmanager it shows all the time 1.72 GB occupied... Never more than it..

    though i have configured sql to use max memory till 8GB, it never go up..

    what should I do??? is there any option/patch/service pack require.. or just memory utilization shown by graph is in actual is true.

    (do 1.72GB only is require for sql exe to work as of now according to db use & nos of user)

    pls help to clear my doubt.

    Thanks in adv..
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    THe best option to know the memory handled in the SQL server is to collect PERFMON (SYSMON) counters using Performance Monitor [Start --> Programs --> Administrative tools ---> Performance Monitor]

    A handy article [http://www.sql-server-performance.co...rs_memory.asp] in this regard.

    BTW what is the memory setting on SQL server and level of service pack?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    pls check the output of sp_configure from server,,,


    name minimum maximum config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    affinity mask -2147483648 2147483647 0 0
    allow updates 0 1 0 0
    awe enabled 0 1 0 0
    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 8191 8191
    max text repl size (B) 0 2147483647 65536 65536
    max worker threads 32 32767 200 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 65536 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 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
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Collect PERFMON counters for Physical disk, Memory, Processor, process, SQL Server -Memory for more information during the process.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    Satya,
    I suppose SQL Server exe of Enterprises edition can use up to 4 GB of RAM. if it is so why its mem utilization is not going up do we have less amount of work load on server.
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    When you start SQL Server, memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB or even more.

    By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool will continue to grow.

    As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

    Refer to Books Online; topics: "Effects of min and max server memory"; "Memory Architecture"; "Memory Architecture"; "Server Memory Options"; "SQL Server Memory Pool" for more information on memory architecture.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    Thanks Satya,

    But mem utilization is almost fixed on 1.72 GB, it gradually move up when i start server.
    but after around 10 mins of time it stops increasing after 1.72 gb point.... and till the time we don't restart the server it stays on the same.
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If you don't have any issues of performance at this juncture then ignore this and plan accordingly to maintain the performance level.

    How often you restart the SQL server?
    Any other applications sharing the server resources?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    We wanted to take out some resource from server thatz why i was warrying about it...
    we do restart sql services only not OS once in a week.
    No other application is using this server.
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  10. #10
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    well if i increase the min memory per query from 1024 to 2048
    what will be the effect on server... i suppose that performance will rise up and no issue will be their cause i have enuff RAM unutalized...

    Need ur suggestions on this regards
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  11. #11
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If the current scenario of utilising memory not having any performance issues, then you test the pattern before implementing it on the production.

    Increasing the value of min memory per query may improve performance for some small to medium sized queries, but could lead to increased contention for memory resources.

    Any reason behind restarting SQL services once in a week?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  12. #12
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    Restarting sql services in not scheduled.. Its just a overall average as we do not have 24*7 operations.
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  13. #13
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    Satya,

    can u check the sqlperf(lrustats) output for this server... is it possible to have cache hit ratio over 100%


    Statistic Value
    -------------------------------- ------------------------
    Cache Hit Ratio 100.03175
    Cache Flushes 0.0
    Free Page Scan (Avg) 0.0
    Free Page Scan (Max) 0.0
    Min Free Buffers 331.0
    Cache Size 4362.0
    Free Buffers 128779.0
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  14. #14
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    This of course assumes you are using the default SQL Server 2000 memory setting of "Dynamically configure SQL Server memory" . Then this would be the behaviour. If your buffer cache ratio is over 90%, you can be fairly sure that SQL Server has all the RAM it needs to run as efficiently as it can. On the contrary if you were to allocate a staic memory value then this is going to be allocated statically and even when the OS needs some it is not released ... Hence kepping a Dynamic configuration can be benificial most of the cases.

    Moreover it is also recommended that you reserve the Servers activity to SQL Server use only, i.e. try to use the SQL Server as a dedicated server rather than just using the same for multiple activities.

    If SQL Server does not need the RAM, then it will give it up to the operating system. But that would happen only when the OS askes for the same. The OS reseves the memory till someone asks for that.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  15. #15
    Join Date
    Sep 2003
    Location
    Columbia, MD USA
    Posts
    95
    one thing I didn't see in there was the edition of Windows 2003 Server you are using... if you are using Standard... it only supports 4GB of memory and probably still has the 2GB process limit that Win2K Standard had... which is why you may be seeing a max of 1.7GB on the MSSQL process
    drew
    ------------------
    http://wjtechnologies.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •