SQL Server Performance

2005 Memory allocation issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by thomas, Mar 16, 2007.

  1. thomas New Member

    We have a server, 2005 Enterprise Edition 64-bit. This is fairly old, but it has 4GB RAM and 4 X 2.8GHz processors.

    It is running a new application with a database that has only just started to be used, thus is only 300Mb in size so far (expected to grow obviously). This database is mirrored to a partner server (equal spec).

    We had an issue with it this week, it started intermittently running very slowly. A simple SELECT query from a small table was taking 27 seconds when it should be a few miliseconds, and all queries were similarly affected. This would happen for a short while, then go away, then recur.

    I found that the server was only using about 200MB RAM. Even forcing lots of data through (e.g. by running a dbcc checktable on a big test table), the page life expectancy went right down and it was obviously flushing pages out of memory rather than using more. SQL Server just stubbornly sat there only using 200 MB.

    Memory was set to dynamically allocate.

    The slow query execution was caused by waits for memory as seen in the DMV sys.dm_exec_query_memory_grants and in perfmon in SQL Server:Wait Statistics - Memory Grant Queue Waits. Page life expectancy was very low too.

    Solution was to fix a minimum memory amount for SQL Server. This is dynamic. Soon as I did that the problem went away and SQL Server started getting as much RAM as it needed. Page life expectantly immediately started rising.

    Something to bear in mind if you normally leave RAM to dynamically allocate on 64-bit 2005 Servers.

    Of course - I have other 2005 servers that do not have this problem!
  2. MohammedU New Member

  3. thomas New Member

    There should be no need for AWE if it's 64-bit. We have not got Lock Pages In Memory set. It says in the link you supplied

    "AWE mechanism technically is not necessary on 64-bit platform"

    but it is there, and it can be beneficial. It does make you wonder!

Share This Page