May 12, 2014 at 6:28 am
Hi all,
I'm trying to decipher the myriad figures available for memory usage and need some help. My server has 16Gb physical memory and SQL is using virtually all of it. There's nothing else on the server, just SQL, single instance. The server has only very, very light usage and only 8 DB's which are quite small.
The information I have seen states that it is the Buffer Pool that is using the memory but after querying the dm_os_buffer_descriptors table and the dm_os_memory_clerks table it looks like it isn't used by databases as they are using only a few MB's each......
[font="Courier New"]cached_pages_count Database_name
3180 LOMODS_DEV01
2381 ResourceDb
1833 tempdb
1235 msdb
1228 CONFLICT_PRD01
451 distribution
399 master
333 LOMCMN_DEV01
63 model
39 LOMPERF_DEV01
39 LOMPROC_DEV01
39 LOMSKP_DEV01[/font]
dbcc memorystatus gave me this....
[font="Courier New"]Buffer Pool Value
---------------------------------------- -----------
Committed 1809447
Target 1825330
Database 9876
Dirty 401
In IO 0
Latched 0
Free 1784583
Stolen 14988
Reserved 0
Visible 1825330
Stolen Potential 1977252
Limiting Factor 13
Last OOM Factor 0
Last OS Error 0
Page Life Expectancy 47523[/font]
What I don't understand is two things:
1) How can the 'Free' value above (which I would imagine means 'not in use') be so high when 'Committed' (which I believe means in use) is also high.
2) What else could be using so much memory if it isn't the databases themselves, and more importantly, how do I find out what it is?
Any help or pointers would be appreciated.
Thanks,
Martin
May 12, 2014 at 7:31 am
SQL Server will just fill the memory on a machine right up to the limit you give it, or, to the point where it contends with the OS for memory. Best bet, put a limit on it. Probably, on a 16gb machine, 14gb, but there's room to go down from that to 13gb or even 12gb.
As far as what's in your memory right now, all the data in your databases and the queries in your cache. SQL Server will just load it all up in there and leave it until it's under memory pressure to flush it out to make room for something else. That's how it works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2014 at 7:59 am
I understand that as a concept Grant, but what I don't understand is.....
1) Only one of the databases are in production and they haven't been queried since the last restart, so I would have thought that there should be little, if any, data in the buffer
2) The query I ran on dm_os_memory_clerks backs up this point by indicating that there is little data from the DB's in memory.
I'm missing something here which I'm sure will make everything crystal clear when I find it!
May 12, 2014 at 8:11 am
You can just go and look at it. Here's a blog post from Microsoft that shows how.
Even if the database hasn't been touched for weeks, if it was in use, then stuff goes into memory and then stays there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2014 at 10:02 am
Sorry Grant - I'm sure it's just me missing something, I found that query but it just seems to back up my point which is that there are very few data pages from the databases in memory. So, the question still remains, if the committed memory is so high, and the data in there isn't from my databases, what is it?
May 12, 2014 at 10:15 am
Didn't the query show you what was in the memory? It came from somewhere on the system within your SQL server instance. Maybe it's just all index maintenance scripts that have been running or something along those lines. SQL Server will just load everything it can into memory and keep it there, up to the fixed limit. So any queries, of any kind, that have been run on the system will still have their memory use there until something comes along to clean it out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2014 at 3:14 am
Unfortunately the cluster service failed over last night so now I have nothing to go on. I will continue to monitor the box and come back to you if the issue reoccurs.
Thanks for your input with this, Grant.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply