0

We are experiencing memory issues with MS Sql 2008 R2. There appears to be a memory leak that is consistently increasing in memory usage until it gets to about 1.5 gb and then stops.

We have used the following script to try and isolate which database could be the cause. However we don't seem to see any evidences with the results that links to any particular database. What are the best practices locating an issue such as this.

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters 
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT 
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767 
       THEN 'Resource DB' 
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3), 
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
5
  • I don't know MSSQL, but are you sure this isn't normal behaviour and due to some caching?
    – Sven
    Jul 23, 2014 at 9:20
  • 2
    How have you determined that it is actually a memory leak? If you're just looking at what SQL Server is using then remember that: 1) SQL Server will use as much free RAM as it can within whatever limits are set for it, and 2) memory that isn't being used is memory you wasted money buying needlessly.
    – Rob Moir
    Jul 23, 2014 at 9:26
  • We maybe be wrong diagnosing it as memory leak. We notice that the memory is always increasing until the server crashes. So when we restart ms sql it usings about 200mb and increase to about 1.5 gb until the service crashes. This cycle takes about 24 hours.
    – Frosty
    Jul 23, 2014 at 9:54
  • 1
    In 16 years of SQL Server experience, I've seen more memory leaks in monitoring software than in SQL Server itself. How much memory is in your server, in total? How much RAM is used by the Windows OS and other programs and services? Are you sure that the memory is not defective? (Is it a new machine, have you added memory lately? If it's an old machine, have you blown the dust out of it or re-seated the RAM modules lately?) Is there anything in the SQL log or event logs that gives a hint as to what causes the crash? Are there memory dump files? Jul 23, 2014 at 15:34
  • 1
    The "server crashes"? Sounds like you aren't limiting the maximum memory available to SQL server. You also need to specify if you are running x86 or x84 SQL/Windows, and the total amount of system memory.
    – Greg Askew
    Aug 4, 2014 at 21:54

1 Answer 1

0

If this is a dedicated SQL Server box that is not running replication, not running DTS/SSIS, and not running any other Process that also needs significant Private Bytes (per perfmon.msc's Process counters), as a start, take your total physical memory in MB (run msinfo32.exe if not sure), multiply that number by 0.8, round the result to a whole number, and in a query window run:

EXEC sp_configure 'max server memory (MB)', 'result'

where 'result' is the whole number just calculated.

For now, please ensure the SQL Server startup account has not been given the "Lock pages In Memory" right (via gpedit.msc).

If there are other process that require significant Private Bytes, you will need to further reduce max server memory. The more you reduce max server memory, the less SQL Server can use memory for caching disk IO (and other caching needs), thus the greater the risk that SQL Server will become slower than what is thought to be acceptable. Adding more memory can be a viable choice (especially considering the cost of 1.5 GB RAM).

After adjusting max server memory, use perfmon.msc to monitor the Process object's counters for Private Bytes and Virtual Bytes, the Memory object's Avail MBytes counter, and the SQL Server Memory Manager object's Target Server Memory and Total Server Memory counters. Use a 5-15 second polling interval, and leave perfmon running until the problem is experienced, Once experienced, stop perfmon and inspect its results. When a system's Avail MBytes drops to ~4-5 MB, SQL Server is expected to lower its Target Server Memory, with its Total Server Memory following that Target.

A 32-bit process (such as a 32-bit SQL Server) cannot address more than 2 GB of physical memory, unless AWE is being used. A 64-bit SQL Server can address more memory than you can afford :).

Finally, please offer us further details about what is meant by "the service crashes". What does SQL Server's errorlog report, at the time of a crash? What does Windows' system event log report, at the time of a crash?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .