none
Minimum and Maximum Memory set in SQL Server

    Question

  • Dear Team,

    I have set AWE switch minimum and maximum memory.

    My question is is there any standard how much we should allocate to SQL server.

    Regards

    Naveed


    Naveed Amir

    Friday, October 05, 2012 4:58 AM

Answers

  • Naveed,

    You don't need to SET AWE in 64-bit Environments.

    For 32-bit Systems..you would need to enable AWE for SQL Server to use more than 2GB.

    In most  Servers that I came across which are Dedicated Server for and hosting only SQL Server......I saw folks using
    80/20 Rule which means setting the SQL Server Max Memory to 80% of total Server Memory.
    or incase of systems with 128GB of RAM....you can leave 2GB for OS and Give the Rest to SQL Server.

    Things may vary based on your needs and requirements.


    -SQLHarry
    SQLHarry Blog

    • Marked as answer by Naveed 1984 Wednesday, October 10, 2012 5:01 AM
    Friday, October 05, 2012 3:58 PM
  • There is no standard for min or max values, setting I prefer :-

    1) Enable AWE, if using 32 bit, no action in case of 64 bit

    2) Add /3G or /PAE switch in boot.ini file, if using 32bit, no action in case of 64 bit

    3) 4% or 5% of toble memory as min memory

    4) max memory = total memory - 10% of total memory

    5) Lock pages in memory rights for SQL server service account

    6) Max memory can be lowered in case of more then 1 sql instance, applciation on same server or other components like ssas etc.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by Naveed 1984 Wednesday, October 10, 2012 5:01 AM
    Tuesday, October 09, 2012 9:52 PM

All replies

  • Which edition of SQL Server do you use?

    32 bit or 64 bit?

    if you are using 32 bit AWE if correct. no 64 bit SQL Server no use of enabling AWE.

    if you are using Windows Server 2008 , then i recomend you to leave 1.5 GB to 2 GB and assign rest to

    SQL Server,

    there are few things to be considered,

    do you use IIS on the same server?

    do you use Reporting Services?


    Ramesh Babu Vavilla MCTS,MSBI

    Friday, October 05, 2012 5:05 AM
  • There is also backup Agents, System Centre Agents, Antivirus, SSAS. Is it a dedicated server what else is it being used for.
    Friday, October 05, 2012 5:34 AM
  • HI Babu,

    We required from you to recommened minimum and max memory for SQL server.

    We are using both x64 as well as 32 Bit SQL server enterprise edition.

    Windows server 2008 R2 Enterprise edition

    SQL server 2008 R2 Enterprise Edition

    AWE is not enabled

    Total CPU Memory is 32GB

    Recommended AWE?

    minimum=?

    Maximum=?

    SQL Server 2005 Enterprise Edition Sp2.

    SQL server 2008 R2 Enterprise Edition

    Total CPU Memory is 8GB

    AWE is Enabled

    Recommended AWE?

    minimum=1024

    Maximum=6400

    No we dont use IIS on same server

    No reporting Services is using 


    Naveed Amir

    Friday, October 05, 2012 5:55 AM
  • THis is only being used for DB server nothing else.

    Naveed Amir

    Friday, October 05, 2012 5:55 AM
  • The bottom line: A. Enough memory must be reserved for OS, SS Engine and other regular programs operations; B. The computer server should be dedicated to SQL Server.

    The following links on the topic:

    http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/288e0e92-f814-48a5-984e-09212b92641e

    http://blogs.msdn.com/b/buckwoody/archive/2009/09/24/sql-server-best-practices-set-a-fixed-memory-size.aspx


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, October 05, 2012 6:10 AM
    Moderator
  • There is no standard values for min and max memory setting. You need to analyse the system requirement and then set it to proper value. Jonathan worte a nice article on this you can refer to his article to know what parameters to capture to baseline memory requirements, based on this you can set min and max memory. Refer his article @ http://sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx

    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Friday, October 05, 2012 9:50 AM
    Moderator
  • Naveed,

    You don't need to SET AWE in 64-bit Environments.

    For 32-bit Systems..you would need to enable AWE for SQL Server to use more than 2GB.

    In most  Servers that I came across which are Dedicated Server for and hosting only SQL Server......I saw folks using
    80/20 Rule which means setting the SQL Server Max Memory to 80% of total Server Memory.
    or incase of systems with 128GB of RAM....you can leave 2GB for OS and Give the Rest to SQL Server.

    Things may vary based on your needs and requirements.


    -SQLHarry
    SQLHarry Blog

    • Marked as answer by Naveed 1984 Wednesday, October 10, 2012 5:01 AM
    Friday, October 05, 2012 3:58 PM
  • There is no standard for min or max values, setting I prefer :-

    1) Enable AWE, if using 32 bit, no action in case of 64 bit

    2) Add /3G or /PAE switch in boot.ini file, if using 32bit, no action in case of 64 bit

    3) 4% or 5% of toble memory as min memory

    4) max memory = total memory - 10% of total memory

    5) Lock pages in memory rights for SQL server service account

    6) Max memory can be lowered in case of more then 1 sql instance, applciation on same server or other components like ssas etc.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by Naveed 1984 Wednesday, October 10, 2012 5:01 AM
    Tuesday, October 09, 2012 9:52 PM
  • Thanks Rohit,

    One more thing i need to ask, I am facing slow responce of application, when i see the DB server logs i find the below mentioned issue..

    SQL Server has encountered 13 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\SQL_Database\MSSQL.1\MSSQL\Test_server 1.NDF] in database [Test_server] (5).  The OS file handle is 0x0000078C.  The offset of the latest long I/O is: 0x0000013f2c2000.

    i have done alot of RnD and found that this issue occure when Disk I/O rate is high in this case what shuold i do.

    Regards,

    Naveed Amir


    Naveed Amir

    Wednesday, October 10, 2012 5:38 AM
  • yes you are right, it means that your disk is under performing, you can you SQLIO to bench mark you Disk Performance

    Ramesh Babu Vavilla MCTS,MSBI

    Wednesday, October 10, 2012 9:11 AM
  • Now how can we rolve this issue which things we need to monitor SQL memory and Disk I/O


    Naveed Amir

    Wednesday, October 10, 2012 9:26 AM
  • Set perfmon counter for your E: drive for below counter to check more about I/O issue.

    • Current Disk Queue Length
    • Avg. Disk Queue Length
    • Avg. Disk Read Queue Length
    • Avg. Disk Write Queue Length
    • Disk Bytes/Sec
    • Disk Read Bytes/Sec
    • Disk Write Bytes/Sec

    It seems all your database files resides only in one drive. If you have other unsed drives on server, suggest to move the .ndf file to other drive to reduce the load from one drive. Segregation of files divide the load on one drive. Please ensure other drive is a part of physical partition. Logically partitioned drive will not give much benefit.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Proposed as answer by vr.babu Thursday, October 11, 2012 2:51 AM
    Wednesday, October 10, 2012 5:42 PM
  • Thanks Rohit,

     I really appreciate, i have found that one of NDF file has no space left  just 500MB remaining. now we should move these NDF file to other file right.

    Regards

    Naveed Amir 


    Naveed Amir

    Thursday, October 11, 2012 10:15 AM
  • If space space is not avilable in drive having NDF, you can move the file to other drive.

    I am still on 2 things stat above :-

    1) try to move file in dirrent drives to reduce load on one drive

    2) run perfmon counter to get the real pictur after file movement


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, October 11, 2012 5:39 PM