May 30, 2019 at 7:40 am
Hi,
we are using sql 20014. system specifications :4CORE &128GB RAM . We configured max memory 110460 mb & min memory as 1024 kb.
Users complaining that SQL peformance is very slow.
when we checked the task manager sql.exe utilising 62 % memory.
when i tried to check the memory using below query its showing that 72 gb memory is using.
select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory
o/p:
Memory_usedby_Sqlserver_MB =72720 mb.
somehow it is confirmed that SQL Server is not using configured memory.
How to make sure the sql server using high memory? please help. I need to ressolve this as soon as possible.
Thanks,
Jo
May 30, 2019 at 9:52 am
Why do you believe that memory is the problem? If users are complaining of slow performance I'd first be checking things like the query plans for the queries that they are running, the indexes of the objects that are being used, how old the statistics are, etc, etc. Memory os not going to be the likely cause here in my view; especially when the Server appears to not be using everything it has available.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 30, 2019 at 12:37 pm
SQL Server will grab the amount of memory it has available and continue to grab more of that to the max. It does this completely automatically, stopping only at the max. There's no other setting you have to do in order to make that happen. So, while memory contention might be a problem, it's unlikely that it's memory allocation. The problem exists elsewhere.
Start with the wait statistics to understand how the server itself is behaving. Use the queries here. That will tell, generally, what is causing the server to slow down. From that you can then determine what it is you need to do. It may be system level stuff. Or, it may be queries (and it's usually queries). Then, since we're talking 2014, you need to go to Extended Events and capture the query behaviors. Use the wait statistics, which are going to tell you CPU or IO, to drive how you sort your queries. Find the ones that are using the most resources that are causing you pain. Then, tune those queries.
For lots more detail on all this, buy my book in the link.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply