Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What's using my memory! Expand / Collapse
Author
Message
Posted Monday, May 12, 2014 6:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:02 AM
Points: 125, Visits: 338
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



Post #1569810
Posted Monday, May 12, 2014 7:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 15,517, Visits: 27,898
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1569845
Posted Monday, May 12, 2014 7:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:02 AM
Points: 125, Visits: 338
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!



Post #1569858
Posted Monday, May 12, 2014 8:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 15,517, Visits: 27,898
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1569864
Posted Monday, May 12, 2014 10:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:02 AM
Points: 125, Visits: 338
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?




Post #1569949
Posted Monday, May 12, 2014 10:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 15,517, Visits: 27,898
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1569954
Posted Tuesday, May 13, 2014 3:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:02 AM
Points: 125, Visits: 338
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.



Post #1570192
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse