Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What's using my memory!


What's using my memory!

Author
Message
webtekkie
webtekkie
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 354
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......

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




dbcc memorystatus gave me this....

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


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



Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17635 Visits: 32268
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
webtekkie
webtekkie
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 354
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!



Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17635 Visits: 32268
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
webtekkie
webtekkie
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 354
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?



Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17635 Visits: 32268
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
webtekkie
webtekkie
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 354
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search