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.

We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite acceptable, but the memory consumption gets more and more until all available memory (30GB) is exhausted. After that, the system gets slow and slower.

Commands like Dbcc freeproccache have no effect.

There are not many transactions in select * from sys.dm_tran_session_transactions (not more than when the system is fine), some times this list is empty.

The first result of dbcc memorystatus is

VM Reserved               42136628
VM Committed               1487176
Locked Pages Allocated    24994048
Reserved Memory               1024
Reserved Memory In Use           0

A restart of SQL Server solves the problem for a while.

  1. What causes this behavior? How can it be avoided?
  2. If a real solution for the cause is too difficult, is there a command that forces SQL Server to actually release all memory without a complete DBMS restart?

Thanks

share|improve this question
2  
What else is running on this server? Is it strictly dedicated to this OLAP/OLTP workload? Do you have scheduled jobs? –  swasheck Dec 31 '12 at 5:48
    
Yes, we had scheduled jobs, we disabled them for a while, not change. We have The mid tier servers, not more than 2GB memory usage. We restart all such applications and no change. –  Alireza Dec 31 '12 at 5:52
1  
Which version of SQL Server are you using? –  Mark Storey-Smith Dec 31 '12 at 11:48
1  
So you are running multiple things on this server and not just SQL –  swasheck Dec 31 '12 at 16:50
1  
Is the SQL Server running on dedicated hardware or is it a virtual machine? –  Liam Confrey Jan 2 '13 at 18:06
show 3 more comments

2 Answers

up vote 7 down vote accepted

I would suggest collecting performance metrics on this server, so you can eliminate the guesswork from troubleshooting these types of problems. See this article for a more complete guide if you don't know where to start with this.

In particular, I would check the performance counters Memory\Available MBytes and Paging File(_Total)\% Usage because you said the issues only start occurring when the buffer pool is full. The numbers you get back from these counters may indicate that the max server memory setting needs to be adjusted (either up or down) for the amount of physical memory allocated to the server. As I mentioned here, I don't recommend basing the max memory setting on the amount of physical memory except as an educated guess for a starting point. Always measure the result, and adjust from there.

If the amount of free memory is too low (< 500), or the page file usage is over zero, this may indicate that the SQL Server instance is overcommitted: on SQL Server 2008 R2, the max server memory setting only controls buffer pool size, and not other memory usage such as the plan cache. SQL Server also does not care about other applications you may have running on the system. This extra memory usage can put memory pressure on Windows -- or the other applications -- possibly leading to disk swapping. This is something you want to avoid at all cost, particularly if the page file exists on a volume backed by just a simple RAID 1 mirror. My feeling is that this is the issue, and backing off the max server memory setting should fix the problem.

If the amount of free memory is high (> 1000) and the page file usage is zero, you can probably bump up the max server memory slightly (in 256 MB increments) to maximize the memory usage of the server. This most likely won't solve the problem, however, and you'll need to look elsewhere, probably at the physical disk counters and the buffer pool page life expectancy. If queries are thrashing the buffer pool, there's nothing you can do except improve disk performance, increase the amount of physical memory available to the server so all the data pages can fit in memory at once, or modify the database to not take up as much physical space (maybe by using row or page compression, or by rebuilding indexes with a higher FILLFACTOR).

I've published an article on this topic here that goes into more depth about this issue and how to solve it.

share|improve this answer
1  
@Alireza: Correct. It's possible the SQL Server buffer pool memory is actually getting swapped out to disk, which is why I say to reduce the max memory setting (somewhat counter-intuitive). I've seen this happen before on servers where the max memory setting was set to the default (unlimited) and a huge table scan was requested. –  Jon Seigel Jan 3 '13 at 14:16
1  
@Alireza: SQL Server is just another application that runs in Windows; in that respect, there's really nothing special about it. The behaviour is the same whether it's SQL Server or some other application. SQL Server 2012 got a new memory manager and the max server memory setting much better reflects the total maximum it's going to allocate, which makes things a bit easier to initially guess. I still recommend monitoring these metrics, though. –  Jon Seigel Jan 9 '13 at 14:20
1  
@Alireza: Hmmm... I'm not sure; you'd have to test it. SQL Server does respond to low memory conditions. Disabling the page file entirely wouldn't let Windows swap it's own memory to disk, so it's possible less physical memory would be available to other applications (i.e., SQL Server)... so in a sense I think it's better to at least have some page file. –  Jon Seigel Jan 13 '13 at 22:11
1  
@Alireza: Ideally zero (I put emphasis in the answer to make a point), but not necessarily. That number is a percentage -- 1% of 4 GB is not the same as 1% of 100 GB -- so it depends on the size of the page file. Assuming a reasonable size, keeping it below ~1% (say, 100 MB) is fine, as long as it's stable. If it's higher than that, or you find it's creeping up over time, that's a good indication of overcommit. This is why you want to continue monitoring 24/7 even after solving this the first time. Adding one more database to the instance could push it "over the edge" without readjustment. –  Jon Seigel Jan 14 '13 at 14:19
1  
I have now also published a blog post on this topic here: voluntarydba.com/post/2013/03/19/… –  Jon Seigel Mar 19 '13 at 14:16
show 8 more comments

Generally the trend for slowness over time should be reverse, because as databases pages move into cache, performance should improve (page life expectancy and buffer hit ratio increase with time), have you set your max memory to (total_physical_mem - 2GB)?

share|improve this answer
    
Exactly what we expect, and exactly the reverse of what we get. in Server properties > Memory > Maximum Server Memory (in MB) is set to 25000. –  Alireza Dec 31 '12 at 6:45
1  
We expect SQL Server to become faster over time (since pages start living in memory), in your case it is becoming slower.. That maxmem looks correct.. Can you define how your system is "slow"? –  Rohan Dec 31 '12 at 7:17
    
The same query takes longer to run. The application creates some medium-weight queries (less than a second to run), lots of tiny queries (just a few milliseconds to run) and occasional heavy-weight ones (more than a few seconds). While the server is below full memory usage, all live together well. After it fill the whole memory, after a few hours most queries become much slower. This is most notable in tiny queries. And this is mostly what slows down the application. –  Alireza Dec 31 '12 at 7:41
1  
Yep sounds like a couple of those queries is causing the SQL Server to page out and page in a lot of stuff.. You could try resource governor (msdn.microsoft.com/en-us/library/bb933866.aspx) to limit the memory consumption of the big queries and medium ones, so that the application queries always have enough buffer available.. –  Rohan Dec 31 '12 at 7:43
2  
The memory limitations in Resource Governor only control query memory, not buffer pool memory. (There's probably a reference on the page you linked to, but it appears MSDN is down right now.) –  Jon Seigel Jan 1 '13 at 16:12
add comment

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.