none
does this a sign for memory pressuer? RRS feed

Answers

  • allowing SQL agent account privilege to lock pages in memory can enhance the memory??



    Take help of link I gave above for configuring MAX server memory setting for SQL server database.LPIM will not enhance memory but if (just in case) ,due to some rouge driver which starts leaking memory or some othet OS process, OS starts taking more memory and faces cruch it will ask SQL servr to trim down its consumption maye ask it to heavily trim down ,so this will cause SQL processes to be pages to disk and ultimately slow causing SQL server issue.With LPIM your memory will be locked and even with OS pressure it cannot be trimmed.

    But before giving LPIM please make sure you configure optimum value for max server memory and give enough memory to OS for working correctly.

    SSIS does not takes memory from buffer pool.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, February 3, 2014 10:31 AM
    Moderator

All replies

  • This is an indicator that you potentially have memory pressure. Have a look at this response by Jonathan Kehayias around a similar question - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1e51965d-fbb1-4f73-b977-5284516b3d45/total-server-memory-vs-target-server-memory-made-me-crazy?forum=sqldatabaseengine

    There are further pieces of information needed to give a definitive answer.

    Thanks


    Warwick Rudd (MCM - SQL 2008 | MCT)

    (SQL Masters Consulting | My SQL Server Blog | Twitter)

    Sunday, February 2, 2014 9:12 PM
  • How much physical memory? Do you have MAX MEMORY set?

    Memory configuration:

    http://www.sqlusa.com/bestpractices/memory-configuration/

    RDBMS optimization:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    • Edited by Kalman Toth Monday, February 3, 2014 9:38 AM
    Sunday, February 2, 2014 9:24 PM
  • Hello,

    Not sure whether this can be memory pressure ,I am talking about all inclusive conditions.In general terms if target server memory is greater than total server memory it is SIGN of memory pressure.Make note I used word sign means not sure but can be.

    Can you also provide output for counters mentioned in below article,in this article in TOC click on 'How to test whether my SQL server is facing memory crunch' you can test by yourself or if you like for more advice you can paste it here

    http://social.technet.microsoft.com/wiki/contents/articles/22316.sql-server-memory-and-troubleshooting.aspx?Sort=MostUseful&PageIndex=1


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, February 2, 2014 10:09 PM
    Moderator
  • I agree with Shanky. SQL is a Server level application and has been optimized to keep as much data as possible in memory. Total and Target server memory could be close to each other and that doesn't mean memory pressure. Is there any performance issue you are facing? Are the queries waiting for memory to execute? This counter alone can be use to make conclusion.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Monday, February 3, 2014 5:56 AM
    Moderator
  • Here  you can fond the answers http://mssqlwiki.com/category/memory/

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, February 3, 2014 7:19 AM
    Answerer
  • Installed on the server 16 GB of RAM. allocated for SQL Server is 8 GB

    Monday, February 3, 2014 7:59 AM
  • I thinks these all counters are related to SQL memory , however it seems fine ?? what do all think ?

    Thanks for your useful replies

    Monday, February 3, 2014 8:28 AM
  • I thinks these all counters are related to SQL memory , however it seems fine ?? what do all think ?

    Thanks for your useful replies

    You have max server memory of 16 GB but but you have only given 8 G to SQL server any specific reason?. Are there any other processes running like SSIS ,SSAS if not you can increase memory for SQL server .Use below link to find out value

    http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

    From what you pasted it doesn't seems to me as memory pressure.PLE is quite high no memory grants pending , all seems good to me.

    Your SQL server service account does not have Locked pages in memory privilege.PLease give that privilege to the account


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Monday, February 3, 2014 9:18 AM
    Moderator
  • Yes I have SSIS packages actually this a dedicated SSIS server and the SQL Server engine just host all SQL jobs run those packages (no databases are there)..so I allocated only 8 GB of ram to free memory for packages

    allowing SQL agent account privilege to lock pages in memory can enhance the memory??



    • Edited by SQL Kitchen Monday, February 3, 2014 10:21 AM
    Monday, February 3, 2014 10:20 AM
  • allowing SQL agent account privilege to lock pages in memory can enhance the memory??



    Take help of link I gave above for configuring MAX server memory setting for SQL server database.LPIM will not enhance memory but if (just in case) ,due to some rouge driver which starts leaking memory or some othet OS process, OS starts taking more memory and faces cruch it will ask SQL servr to trim down its consumption maye ask it to heavily trim down ,so this will cause SQL processes to be pages to disk and ultimately slow causing SQL server issue.With LPIM your memory will be locked and even with OS pressure it cannot be trimmed.

    But before giving LPIM please make sure you configure optimum value for max server memory and give enough memory to OS for working correctly.

    SSIS does not takes memory from buffer pool.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, February 3, 2014 10:31 AM
    Moderator