locked
memory settings RRS feed

  • Question

  • Hi All,

    In our environment we have server with 8GB RAM with default sql memory settings is 2147483647 MB and we have got SSIS, SSRS and SSAS running.

    Now based on above settings what would be the recommended memory settings for SQL server?

    Thanks.

    Tuesday, September 2, 2014 5:14 PM

Answers

All replies

  • Hi,

    IMO 8 G of memory is less when you have SQL Server with SSIS,AS,RS. In current scenario you must configure max server memory and also give SQL Server service account Locked pages in memory privilege

    You can read below link for how much memory SQL Server would require

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

    You must use perfmon counters and use counters mentioned in below link to read exact value for Max server memory

    http://social.technet.microsoft.com/wiki/contents/articles/22316.sql-server-memory-and-troubleshooting.aspx#How_to_test_that_your_SQL_server_is_facing_memory_crunch



    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 Articles

    • Proposed as answer by Sofiya Li Wednesday, September 3, 2014 9:20 AM
    • Marked as answer by Sofiya Li Wednesday, September 10, 2014 9:03 AM
    Tuesday, September 2, 2014 5:22 PM
  • Hi

    If you dont throttle SQL it will use as much as possible. What is the requirement for your application? If it is 4Gb then make SQL that but dont let it use everything and then the server starts paging, this causes high disk IO.


    Hope this helps. Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, September 2, 2014 5:22 PM
  • One important point here is, you have to always analyse the useage of your databases ( SQL Instance or SQL Instances ), and also any other applications on the same host, so based on all these calculation and findings we can allocate the  memory to sql and OS.. sometimes sql instance might require more memory based on the usage and some times  host applications may required more memory based on the applications high resource usage.

    Its worth of starting with this below article :

    http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

    Best is to baseline your database server usage during your full business cycle as that will give you the best number based on your workload using below PERFMON counters :

    • SQL Server:Buffer Manager\Page Life Expectancy
    • SQL Server:Buffer Manager\Page reads/sec
    • Physical Disk\Disk Reads/sec
    • Memory\Available Mbytes
    • SQL Server: Memory Manager - Total Server Memory
    • SQL Server: Memory Manager - Target Server Memory

    If you are going to use any formula or online calculator to calculate SQL Server memory configuration then best is to read below link....

    http://www.sqlskills.com/blogs/jonathan/wow-an-online-calculator-to-misconfigure-your-sql-server-memory/

    Also read below links to get more idea  before following any method.. I am sure there is no direct method to decide the memory settings, its all based on your sql instance usage and application usage...

    https://sqlmem.codeplex.com/

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

    Script to calculate Max memory..

    http://gallery.technet.microsoft.com/Calculate-SQL-Server-max-981d404f


    Raju Rasagounder Sr MSSQL DBA


    • Edited by RAJU RG Tuesday, September 2, 2014 10:26 PM
    Tuesday, September 2, 2014 10:20 PM
  • Is it 32 or 64 bit server? Start with setting MAX memory to 6GB

    sp_configure 'show advanced options', 1

    RECONFIGURE

    GO

    sp_configure 'awe enabled', 1

    RECONFIGURE

    GO

    sp_configure 'max server memory', 6144


    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

    • Proposed as answer by Sofiya Li Wednesday, September 3, 2014 9:20 AM
    • Marked as answer by Sofiya Li Wednesday, September 10, 2014 9:03 AM
    Wednesday, September 3, 2014 4:43 AM
    Answerer