January 7, 2015 at 5:09 am
Hi,
I'm a bit stumped on some memory use on a 2012 box - I have a total server memory of approx 26Gb (on a 32gb box), when I look at sys.dm_os_buffer_descriptors for use by database I see approx 10Gb in use by databases. Looking at sys.dm_os_memory_cache_counters the pages_kb gives me another 4.5Gb in use.
If I look at sys.dm_os_memory_clerks the total pages_kb is close enough to the total I have established from the other two tables.
ie So far I can account for around half of my memory use.
I know there is other use but I was expecting fairly minor items so can anyone please tell me where I can pin down the other uses of memory?
Thanks
January 7, 2015 at 6:24 am
dbcc memorystatus
partially documented online
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 7, 2015 at 6:39 am
Tried that, lots of data but have yet to manage to convert to something meaningful 🙁
January 7, 2015 at 7:26 am
What are you really trying to do here, and more importantly why are you trying to do it?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 7, 2015 at 7:47 am
We have a server that has had a performance issue, for us it is a busy server and normal service has been resumed since a rollback of something that was deployed (so that tells us everything we need to know on that front) but I have been asked to have a rummage to see if there are any actions out there to reduce risk for the future - an open "is there anything out there to fix".
My initial suspicion was a single monitoring database that before it went live I raised concerns over and I was unsurprised to find it is using more cache than what is being monitored.
This in itself isn't a big issue but what I spotted was the the total RAM used for the databases was < 50% the max server ram so I took a look at the amount of plan cache and things like userstore_tokenperm which has featured in the past and with the amount of ram I can see I am still several gigabytes short on explaining use of all the SQL allocated memory - I would not be surprised to find potentially hundreds of megabytes unaccountable as the box is live and the numbers will be constantly changing but several gigabytes (I have in my mind I got it down to 7Gb unaccounted for but out of 26 that is a large amount).
So I've gotten curious as to what is using it and it seems an interesting exercise to break down the memory use at a relatively high level as I'm finding it a little odd that most of the SQL memory isn't being used by data cache.
January 7, 2015 at 9:30 am
Balance (1/7/2015)
We have a server that has had a performance issue, for us it is a busy server and normal service has been resumed since a rollback of something that was deployed (so that tells us everything we need to know on that front) but I have been asked to have a rummage to see if there are any actions out there to reduce risk for the future - an open "is there anything out there to fix".My initial suspicion was a single monitoring database that before it went live I raised concerns over and I was unsurprised to find it is using more cache than what is being monitored.
This in itself isn't a big issue but what I spotted was the the total RAM used for the databases was < 50% the max server ram so I took a look at the amount of plan cache and things like userstore_tokenperm which has featured in the past and with the amount of ram I can see I am still several gigabytes short on explaining use of all the SQL allocated memory - I would not be surprised to find potentially hundreds of megabytes unaccountable as the box is live and the numbers will be constantly changing but several gigabytes (I have in my mind I got it down to 7Gb unaccounted for but out of 26 that is a large amount).
So I've gotten curious as to what is using it and it seems an interesting exercise to break down the memory use at a relatively high level as I'm finding it a little odd that most of the SQL memory isn't being used by data cache.
As long as you have set a reasonable max sql server memory I can virtually promise you that you are wasting your time trying to find some memory issue causing a performance problem on this box (outside of improperly virtualized environment anyway).
However, I can DEFINITELY PROMISE you that there are MANY things that are set up, configured, coded, maintained and or done improperly/suboptimally!! I have done performance tuning, performance/healthcheck reviews on SQL Server for well over 15 years now and EVERYONE (mom-and-pop to Fortune 100) does the same bad stuff! Consider getting a pro in for a few hours to point things out. Tremendous ROI to be had there!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply