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?
3
-
This post may be of some use.– DMKCommented Nov 21, 2012 at 13:18
-
2It's doing what it should, for best performance. What issue are you referring to? Is this causing any other effects? Or are you just concerned about the amount of memory used?– MatBailieCommented Nov 21, 2012 at 13:20
-
3You have queries on the server, you just don't know you do.– Remus RusanuCommented Nov 21, 2012 at 13:21
Add a comment
|
2 Answers
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.
-
But with the same settings for memory in my personal computer it does not take all the memory unless query is being run.– RobertCommented Nov 21, 2012 at 13:19
-
Are you using the AWE option that existed prior to 2012 on any of them ?– NeoafterCommented Nov 21, 2012 at 13:22
-
Neoafter is right. Sql server uses memory and for other application like SSAS, where in properties is some percent which shows how many memory Analysis services can take. Probably and Users if there are any, then they connect to database or Analysis services are using memory. Sometimes then I work there is lag on server then I minimize the memory usage, but after work I set it almost to the maximum.– JustinCommented Nov 21, 2012 at 13:47