February 23, 2016 at 5:21 am
In my EDW Environment we have 512 GB RAM on SQL Server 2012 and in that we gave 471 GB as Max memory.I Have gone thru Perfmon but Total and Target server memory is same.We strongly believing memory is getting under utilized and wants to reduce max memory and want to allocate to new SSRS instance.But before doing that we wanted to know actual memory utilizing by SQL when peak load is there.How to find out that and also want to know memory using by each query??
February 23, 2016 at 10:40 am
Check out the sys DMV's dm_exec_cached_plans, dm_exec_query_stats, etc.
February 26, 2016 at 3:16 pm
If I good understand you want to know how much sql really use..
I use these query
First is Free Memory,Target and Total SQL Memory ... and second is Total memory on Server
SELECT object_name
,counter_name
,cntr_value / 1024 "MB"
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Free Memory (KB)'
,'Total Server Memory (KB)'
,'Target Server Memory (KB)'
)
--FOR SQL 2012>=
SELECT physical_memory_kb/1024 "MB"
FROM sys.dm_os_sys_info
--FOR SQL 2008<=
SELECT physical_memory_in_bytes/ 1048576
FROM sys.dm_os_sys_info.
My result are below
SQLServer:Memory Manager | Free Memory (KB) | 9732
SQLServer:Memory Manager | Target Server Memory (KB) | 140000
SQLServer:Memory Manager | Total Server Memory (KB) | 102738
AND
176116
Totally we have on server 172GB
SQL maximum memory is 140GB
SQL has allocated 102GB and inside this is about 9GB free
What I checked If you have Target and Total same, you have allocated all memory what you have allowed for SQL Server, so try to check what is free...
EDIT: Today I tested something, and after deleting all cached plans I had about 19GB Free but allocated is still 102GB
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply