As soon as I start SQL Server with no application querying, in the task manager it starts to consume memory very fast till it consumes around 15GB. How can I resolve this issue?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com Nov 21 '12 at 21:13
This question came from our site for professional and enthusiast programmers.
To determine which queries are pulling objects into the buffer, use a server side trace or a tool like SQL Server Profiler. This will capture each query that is being ran on the database server, as it is being executed.
You didn't say which version of SQL Server, but if 2005 or higher, I'd strongly recommend reading Aaron Bertrands Determine SQL Server memory use by database and object post. The first query returns the databases which have objects in the buffer, and the number of pages and space they are consuming. Find the top database, and use the second query to determine which of the databases objects are taking up most of the buffer.
If you are on SQL Server 2008 or higher AND CPU is not a bottleneck AND you have memory pressure and can't get more memory added, I'd also review Edwin Sarmiento's Implementing Data Compression in SQL Server 2008. You may try data compression on a few of the top objects from the memory by object query. Compressed data takes up less space on both the storage substem and in the memory buffer, so you can fit more data pages in memory. When more data pages fit in memory, it reduces the number of reads from / writes to disk. As Edwin describes: "Data compression reduces the storage costs and increases query performance by reducing I/O and increasing buffer-hit rates." The tradeoff is a slightly higher CPU Utilization.
Yes SQL Server will consume as much memory it can get, right click on the server instance in SSMS and select properties. Then click on the memory page and put a value in for the maximum server memory to restrict how much memory the server will take.