SQL Server Performance

Lots of Memory - But is SQL Server Using It?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jbates99, Jun 16, 2009.

  1. jbates99 Member

    Hi everyone,

    Months ago, I changed the configuration of my SQL Server 2005 server, increasing the minimum and maximum merory allocations. It is Windows 2003.

    The max server memory is 19,456 MB (approximately 20 GB)
    and min server memory is 6,144 (approx. 6 GB)

    I have enabled AWE and changed the boot.ini to include the PAE switch. Then rebooted server

    How can I be *sure* SQL Server is actually using the additional memory?

    ** When I look at Task Manager, the sqlserver.exe shows Memory Usage is 311 MB and CPU is around 25 % **

    Does the amount of memory used by sqlserver.exe have anything to do with the min and max memory settings?

  2. deepakontheweb New Member

    Check out these two perfmon counter on the system, it will tell you how much memory is being used by SQL Server. SQLServer Exe by default does not use all memory for itself but instead creates a buffer pool being managed by SQL. 1) MSSQL:Memory Manager: Target server Memory (KB) - [Total amount of dynamic memory the server is willing to consume]2) MSSQL:Memory Manager: Total server Memory (KB) - [Total amount of dynamic memory the server is currently consuming]
  3. jbates99 Member

    Thank you again, Deepak.

    It appears that SQL Server IS using all the memory we threw at it.

    Target Server memory is 19,922,944
    and Total Server memory is also 19,922,944

    thanks for the information on these counters.

  4. jbates99 Member

    as for sqlserver.exe, it usually shows (in Task Manager) 1.7 GB on most of my SQL Server 2005 servers. But on the server I posted about, it shows 300 MB. I do not undestand why it is different than the other servers.

    Not important though. You helped me confirm that it is using all the allocated memory for processing.

    Thanks again, John
  5. nmavra New Member

    I also wanted to check how SQL deals with memory because in task manager it seems its only consuming around 350mb which is certainly not the case.
    So this 2 performance counters help a lot.
    This is how my server looks like at this moment
    It seems that the actual CPU is a bit slow in dealing with all the sql processing, right?
    This is a dedicated SQL2005 server by the way, the disks are a fragmented but Disk Load shows only 1% on drive E:, which is the drive where I have my databases. (The tempdb and its log is in disk C:)
  6. satya Moderator

    <P mce_keep="true">Paging and Memory bottlenecks are quite common when a performance problem strikes on your SQL Server. As we discussed here previously and relevant blog posts below talks about them: Paging and Available Memory for Operating sysem, what you need to know? SQL Server Memory - what's your method on Configuration and Troubleshooting Issues Using PERFMON to get memory counters, what you need to check? By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources....(<A href="http://sqlserver-qa.net/blogs/perftune/archive/2008/04/15/3922.aspx"><FONT color=#003b6e>read more</FONT></A>)<IMG alt="" src="http://sqlserver-qa.net/aggbug.aspx?PostID=3922" width=1 height=1> </P><P><I>Read the complete post at <A href="http://sqlserver-qa.net/blogs/perftune/archive/2008/04/15/3922.aspx"><FONT color=#003b6e>http://sqlserver-qa.net/blogs/perftune/archive/2008/04/15/3922.aspx</FONT></A></I></P>

Share This Page