Windows Server 2012 R2 & SQL Server 2012 Memory low usage

0
votes

I can't find solution for my problem...

We have a Windows Server 2012 R2 and SQL Server 2012 installed on it. I have not installed this server so I can't say if there are some allocation of resources for each user made on start.

My issue is that if I connect to server via remote desk and run queries, they take really long to execute even if it's a short query, indexes / key are set but I found in task manager that I can't get over 200GB memory usage for SQL Server.

The max memory setting in SQL Server is set to 2147483647

Where can I find if I have only some amount of memory allocated to mine account?

Thanks

1
sql-serverwindows-server-2012-r2

1 Answers

0
votes

Memory usage may not be always an issue.You an check if queries are waiting for memory grants using below dmvs

--below dmv gives me text of queries which are waiting for memory grants

select * from sys.dm_exec_query_memory_grants mg
 join
sys.dm_exec_requests ec
 on ec.session_id=mg.session_id
 --left join
 --sys.dm_exec_connections con
 --on con.session_id=ec.session_id
 outer apply
 sys.dm_exec_sql_text(ec.sql_handle) txt
 outer apply
 sys.dm_exec_query_plan(ec.plan_handle) qp

Further you can check which memory clerk is using most memory

select (sum(pages_kb)*128)/1024 as 'sizein_mb',type as 'clerkttype'
from sys.dm_os_memory_clerks
group by type
order by (sum(pages_kb)*128)/1024 desc

Prior to this,you may need to check if you are having locking,blockings

select 
 session_id,status,blocking_session_id,wait_type,txt.text from sys.dm_exec_requests ec
cross apply
sys.dm_exec_sql_text(ec.sql_handle) txt

Hope this helps in your troubleshooting.Checking memory from task manager wont get you accurate stats..below question and answer has reason and some good links to check further.

https://dba.stackexchange.com/questions/35418/why-is-sql-server-memory-showing-in-task-manager