SQL Server Performance

Memory usage in relation to database size

Discussion in 'SQL Server 2008 Performance Tuning for Hardware' started by mcarney, Dec 16, 2009.

  1. mcarney New Member

    We are currently on Windows Server 2008 with SQL Server 2008 installed and used for a new application that is supplied by a Fuel Management vendor. The physical memory is a total of 2Gb. The database itself is a total of 1.6 Gb in size. When the server is restarted the sqlsvr.exe process is using a very low amount of memory. Within several hours the memory used by this process is now at 980Mb which does not make sense to me for the size database that we are looking at nor based on what it does. Unfortunately, throwing physical memory at a problem seems to be a fix-all.
    I understand that SQL Server is 'designed' to consume as much memory as is available and will do so, whether needed or not, unless the system is tuned for the need. I am also aware that SQL will not release unless the system is restarted.
    Question: Is there a viable reason why SQL Server would need a Gig or more of physical memory to handle a database of 10-15 Gb in size let alone a 1.6 Gb size db? Are there any suggestions?
    Assistance is appreciated.
  2. satya Moderator

    Welcome to the forums..
    Is your system suffereing from any performance loss due to this behaviour?
    As you referred it is by design that SQL Server uses the 90% of available memory on the server when the settings are DYNAMIC. Having said that SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments. The Database Engine does this by using the Memory Notification APIs in Windows operating system.
    In case of any performance within a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries do not get the minimum required memory for the bitmap. This can affect the query performance and if the sorting process can not fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. To resolve this problem add physical memory or tune the queries to use a different and faster query plan.
    As other applications are started on a computer running an instance of SQL Server, they consume memory and the amount of free physical memory drops below the SQL Server target. The instance of SQL Server adjusts its memory consumption. If another application is stopped and more memory becomes available, the instance of SQL Server increases the size of its memory allocation. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
  3. mcarney New Member

    Thank you very much for the info.
    In this case I have been told that the system performance is hampered. However, I am also aware that the system performance factors have not been reviewed to determine where the issue lies. For example, in this case we are in the implementation stage of an application that utilizes SQL Server 2008. However the number of I/O writes (when a vehicle is fueled, oiled and the amounts used, the mileage, etc.) would be very small in comparison to the average SQL implementation. The database size is a whopping 1.6 GB yet we are using the infamous 1.7 GB of memory on the server with almost 1 GB for the sqlsrv.exe process alone.
    My issue is not how much memory we have to place on the server or how much is available for the DB but rather how much memory does SQL server actually need to work efficiently in the specific instance. Especially since, from what I can see, SQL server is not being tuned at all but rather we are throwing hardware at it. Now we may very well may need the additional memory for other processes but without knowing or verifying this, adding memory may not help at all except for helping the economy out.
    Is there a process, and write up, on how to determine exactly what SQL's memory requirements are at peak?
  4. satya Moderator

    Start from here http://sqlserver-qa.net/blogs/perftune/archive/2007/12/19/2981.aspx on baseline & benchmarking, if you haven't done the basics of platform assesment.
    From BOL:
    To monitor the amount of memory that SQL Server uses, examine the following performance counters:
    • Process: Working Set
      • SQL Server: Buffer Manager: Buffer Cache Hit Ratio
        • SQL Server: Buffer Manager: Total Pages
          • SQL Server: Memory Manager: Total Server Memory (KB)

Share This Page