September 26, 2015 at 2:47 pm
This is from stackoverflow:
I think I have a basic question here that many might have encountered. When I run a query in SQL Server it will load in memory all the data it needs for query execution (for example, if there is a join then it would load the necessary data from those two tables) but when the query finishes executing the memory consumed by SQL Server is not released.
I noticed this because a few days back I was analyzing a query that takes up a lot of tempdb space. When I used to run the query it would (by the end of execution) consume up to 20 GB of RAM. This 20 GB RAM would not be released unless I restarted the MSSQLSERVER service.
How do you guys do SQL Server memory management? This is clearly an issue right?
I would also like to hear if you do something specific to clear the memory used up by a single query.
=============================================================
My problem is similar except we query a database from an app server and we use min and max server memory to allocate
15 gig of ram to SQl Server and 5 gig to the OS. AFter running a couple of large processes, the application will freeze up.
I use Idera diagnostic manager and the product says memory is the problem. It uses all the memory I allocated to SQL server and paging.
I believe we need a way to release memory after each process is complete. I've been researching this and most techies say the only way to do this is restart the server.
Not so good.... Say it isn't so...
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
September 27, 2015 at 12:30 am
Quick question, what else is running on the server beside the Sql Server?
😎
September 27, 2015 at 5:44 am
fizzleme (9/26/2015)
I think I have a basic question here that many might have encountered. When I run a query in SQL Server it will load in memory all the data it needs for query execution (for example, if there is a join then it would load the necessary data from those two tables) but when the query finishes executing the memory consumed by SQL Server is not released.
Normal, expected, documented behaviour.
SQL caches data and plans to avoid the cost of reading from disk and generating plans. Once it's allocated memory, SQL won't release it unless the OS comes under memory pressure or the SQL service is restarted.
How do you guys do SQL Server memory management?
Set the max memory that it's allowed to have, monitor to ensure that memory isn't being overutilised and otherwise leave it alone. Oh, and ensure that servers running SQL have good amounts of memory on them.
This is clearly an issue right?
No, it's not. It's expected behaviour. SQL has very good memory management functionality built in, to ensure that it's using the memory it has in the most efficient way possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 27, 2015 at 1:32 pm
For all the reasons Gail stated, high memory consumption is actually a feature, not a flaw. Any time you can avoid going to disk and read pages from memory instead, you're going to boost performance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply