SQL Server 2008 R2 High memory usage

Question

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

Answer 1

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.

Answer 2

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.

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE 

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).

Answer 3

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:

http://www.mssqltips.com/sqlservertip/1746/performance-data-collection-and-warehouse-feature-of-sql-server-2008-part-1/

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:

SELECT TOP 10
    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.execution_count,
    qs.total_logical_reads,
    qs.last_logical_reads,
    qs.total_logical_writes,
    qs.last_logical_writes,
    qs.total_worker_time,
    qs.last_worker_time,
    qs.total_elapsed_time / 1000000 total_elapsed_time_in_s,
    qs.last_elapsed_time / 1000000 last_elapsed_time_in_s,
    qs.last_execution_time,
    qp.query_plan
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