I have SQL Server 2008 R2 that runs multiple jobs over night, the problem is SQL Server will eat up all RAM and CPU, then it will not release the RAM resources after the job finishes, as a workaround this problem, I run a schedule task for windows to kill the SQL process (taskkill /im sqlservr.exe /f)

Would anyone know a better way to release RAM?

Below is the SQL server info:

Microsoft SQL Server Management Studio      10.50.1600.1
Microsoft Analysis Services Client Tools    10.50.1600.1
Microsoft Data Access Components (MDAC)     6.0.6002.18005
Microsoft MSXML                             3.0 5.0 6.0 
Microsoft Internet Explorer                 9.0.8112.16421
Microsoft .NET Framework                    2.0.50727.4216
Operating System                            6.0.6002
  • 2
    What are the memory settings for the sql server (Max RAM, AWE enabled, etc). Depending on these settings, sql server could be allocating this memory and purposely not releasing it, even if it does not need it.
    – tjscience
    Jun 14, 2012 at 4:53
  • 3
    SQL Server will only release the memory occupied if the OS asks for it. I think you're worrying about something you don't need to be.
    – Chris
    Jun 14, 2012 at 4:54
  • @tjscience Max RAM=2147483647 (24GB) AWE not enabled
    – SDM78
    Jun 14, 2012 at 5:03
  • @Chris So it will not effect the server performance?
    – SDM78
    Jun 14, 2012 at 5:05
  • ... that is, unless the SQL Server has consumed memory to the degree that it cannot release back to the OS, at which point in time we get starvation.
    – swasheck
    Jun 22, 2012 at 21:17

3 Answers 3


Per your comment:

Max RAM=2147483647 (24GB) AWE not enabled

The Max Memory setting is in MB, not bytes. The above setting (the default) is 2,048 TB (i.e., unlimited).

For a box with 24 GB of physical memory with a single instance of SQL Server, a good starting point for Max Memory would be 20480 (20 GB).

You may need to reserve more memory for the operating system or other applications. Note also that on SQL 2008 R2, memory allocated for the procedure cache is not included in the Max Memory setting, so generally you have to take a conservative educated guess (see above) and tweak from there.

How do you know if you guessed right? In addition to other PerfMon counters, I strongly recommend monitoring (recording) the Memory\Available MBytes counter to make sure it's >= ~500 MB at all times.

Finally, changing the memory settings in the server properties flushes the procedure cache -- I normally recommend making this change during an off-peak time, as this will create extra CPU load.


First of all you can restart service, not kill it (see net stop, net start commands).

From here: If SQL Server is running slow and operations are throwing errors due to lack of memory, it is necessary to look into memory issue. If SQL Server is restarted all the cache memory is automatically cleaned up. In production server it is not possible to restart the server. In this scenario following three commands can be very useful. When executed following three commands will free up memory for SQL Server by cleaning up its cache.


However you should read comments to the linked article carefully. This is not a normal operation, SQL Server must be configured so that memory management is not required. You should consider either increasing SQL Server machine physical RAM or configure it's memory usage (see 1, see 2).


hi there do not never ever do this : taskkill /im sqlservr.exe /f you should use the Performance Data Collection from sql server. see here:


this could be the first step to solve your problems.

you should look for kost expensive queries Using DMV. here you find an example how to identifiy this queries:

    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset
    END - qs.statement_start_offset) / 2) + 1),
    qs.total_elapsed_time / 1000000 total_elapsed_time_in_s,
    qs.last_elapsed_time / 1000000 last_elapsed_time_in_s,
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.