locked
sql server using very less memory RRS feed

  • Question

  • 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


    pols

    Thursday, May 30, 2019 7:39 AM

Answers

  • Yes. SSIS,SSAS,SSRS all services are running.

    So that may be the answer to why SQL Server are not using all memory it has been alotted. Are you actually using these services? Then you should consider moving them to a separate machine (which will cost you one more license!).

    LPIM as suggested by Shanky is not likely to be bad thing, but my prediction is that you will not see any difference. Or, well, you will have to restart the server, which clears the plan cache, so if the issue was one or more bad plans due to parameter sniffing, you may have better luck this time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by joo123 Tuesday, June 25, 2019 12:55 PM
    Thursday, May 30, 2019 11:31 AM
  • Trying to understand why you are focusing on the less memory utilization by sql server , how much is the total memory usage in server and are you seeing any memory pressure in the sql side ?


    Thanks, Nithin

    • Marked as answer by joo123 Tuesday, June 25, 2019 12:55 PM
    Thursday, May 30, 2019 11:38 AM

  • LPIM as suggested by Shanky is not likely to be bad thing, but my prediction is that you will not see any difference. Or, well, you will have to restart the server, which clears the plan cache, so if the issue was one or more bad plans due to parameter sniffing, you may have better luck this time.

    Two things that can cause SQL Server to not use allocated memory 

    1. SQL Server actually does not needs that much memory and so it will just not use it

    2. something is internally pulling memory like OS, other process SSIS/AS/RS, Vmware host

    Since we both have no information about other factors we will have to make some assumption 


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, May 30, 2019 12:39 PM

All replies

  • Are there other processes on the server consuming memory outside SQL Server? That could be the reason that SQL Server is not getting its full dosage.

    But it could also be that SQL Server does not need more memory. That is, the access to the same 72 GB of data.
    Or have you concluded that SQL Server is performing a lot of physical I/O and that is why is response time is slow? If not, my gut feeling is that you are looking in the wrong place for the explanation for the poor performance.

    The most common reason for bad performance is badly written queries and inappropriate indexing. In this case you need to identify the slow queries.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 30, 2019 7:58 AM
  • I have a hunch there is paging happening. Can you post output of query

    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,

    (virtual_address_space_committed_kb/1024) Total_memory_use,

    process_physical_memory_low, process_virtual_memory_low from sys. dm_os_process_memory

    You can also run below query in SSMS and post output here

    sp_readerrorlog 0,1,'significant part of'
    go
    sp_readerrorlog 0,1,'request taking longer'
    Is there any other instance of SQl Server running ? Are there SSIS ,SSAS, SSRS running


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Thursday, May 30, 2019 8:18 AM
  • Hi Shashank,

    Thank you very much for your reply.

    As you suggested i run the queries.below is the output.

    Memory_usedby_Sqlserver_MB= 73463
    Locked_pages_used_Sqlserver_MB=0
    Total_VAS_in_MB=134217727
    Total_memory_use=73870
    process_physical_memory_low=0
    process_virtual_memory_low=0

    For below commands:

    sp_readerrorlog 0,1,'significant part of'
    go
    sp_readerrorlog 0,1,'request taking longer'

    Output :

    0 Rows affected.
    0 Rows affected.


    Yes. SSIS,SSAS,SSRS all services are running.

    Please let me know your understanding as per the readings..

    Regards,

    Jo



    pols

    Thursday, May 30, 2019 10:08 AM
  • The SQL Server service account does not have Locked pages in memory privilege(LPIM), this might be one reason for SQL Server memory being low. Something is pulling memory and it would be SSIS/AS/RS or windows. Provide LPIM to SQL Server service account. This would need restart so plan accordingly

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, May 30, 2019 10:11 AM
  • LPIM means lock pages in memory options?

    How to provide LPIM to SQL Server service account?

    Shall i follow below instructions to provide LPIM ?

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-2017

    Afther that i need to restart the sql server services.. 

    Raegrds,

    Jo



    pols


    • Edited by joo123 Thursday, May 30, 2019 10:31 AM
    Thursday, May 30, 2019 10:26 AM
  • LPIM means lock pages in memory options?

    How to provide LPIM to SQL Server service account?



    pols

    Yep it means the same. Please refer below. Please note the right has to be provided to SQl Server service account i.e account running SQl Server service

    https://blog.sqlauthority.com/2017/06/15/sql-server-enable-lock-pages-memory/


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, May 30, 2019 10:30 AM
  • Ok, i will do that. and let you know the update. 

    Thank you so much for your quick response.


    pols

    Thursday, May 30, 2019 10:33 AM
  • Yes. SSIS,SSAS,SSRS all services are running.

    So that may be the answer to why SQL Server are not using all memory it has been alotted. Are you actually using these services? Then you should consider moving them to a separate machine (which will cost you one more license!).

    LPIM as suggested by Shanky is not likely to be bad thing, but my prediction is that you will not see any difference. Or, well, you will have to restart the server, which clears the plan cache, so if the issue was one or more bad plans due to parameter sniffing, you may have better luck this time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by joo123 Tuesday, June 25, 2019 12:55 PM
    Thursday, May 30, 2019 11:31 AM
  • Trying to understand why you are focusing on the less memory utilization by sql server , how much is the total memory usage in server and are you seeing any memory pressure in the sql side ?


    Thanks, Nithin

    • Marked as answer by joo123 Tuesday, June 25, 2019 12:55 PM
    Thursday, May 30, 2019 11:38 AM

  • LPIM as suggested by Shanky is not likely to be bad thing, but my prediction is that you will not see any difference. Or, well, you will have to restart the server, which clears the plan cache, so if the issue was one or more bad plans due to parameter sniffing, you may have better luck this time.

    Two things that can cause SQL Server to not use allocated memory 

    1. SQL Server actually does not needs that much memory and so it will just not use it

    2. something is internally pulling memory like OS, other process SSIS/AS/RS, Vmware host

    Since we both have no information about other factors we will have to make some assumption 


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, May 30, 2019 12:39 PM