Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Thank you

share|improve this question

migrated from stackoverflow.com Jun 14 '12 at 9:13

This question came from our site for professional and enthusiast programmers.

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 '12 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 '12 at 4:54
    
@tjscience Max RAM=2147483647 (24GB) AWE not enabled –  SDM78 Jun 14 '12 at 5:03
    
@Chris So it will not effect the server performance? –  SDM78 Jun 14 '12 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 '12 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.

share|improve this answer
    
Sound advice, +1. –  Mark Storey-Smith Jun 23 '12 at 1:07

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

share|improve this answer
    
Thank you for the tips :) Petr –  SDM78 Jun 14 '12 at 5:07

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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