Enterprise TechSQLHow to Configure SQL Server Memory Options for Best Practices June 8, 201612257 views0Share By IG ShareTable of ContentsNote: If you buy something from our links, we might earn a commission. See our disclosure statement. How to Configure SQL Server Memory Options for Best PracticesGeneral SQL Server Memory Best PracticesSQL Server Buffer Pool Extension Best PracticesIn-memory OLTP RecommendationsHow to Configure SQL Server Memory Options for Best PracticesSQL Server is a memory intensive application. When implementing SQL Server performance best practices, one of the primary best practice is to optimize the memory options for optimal performance. SQL Server has evolved over the years and offers various capabilities which were not available before. For example, Buffer Pool Extension, Column Store Indexes, In-Memory OLTP engine for high-performance transactional systems etc. In this post, I am going to talk about how to configure SQL Server Memory Options for Best Practices.General SQL Server Memory Best PracticesBy default, SQL Server will generally try to eat up all the memory from the Operating System. This can greatly stress the Operating System from performing its core tasks. In order to prevent this, perform the following:For Systems with 4 GB of RAM: Reserve 1 GB of RAM for the OS,For Systems of 16 GB of RAM: Reserve 4 GB of RAMYou need to reserve 1 GB for the OS for every 8 GB of RAM greater than 16 GB.For 32 GB Systems: Reserve 6 GB of RAM for the OS (4 GB till 16, then 1 GB for every 8 GB)You need to deduct the estimated memory requirements for the Operating System and set it accordingly in the “Maximum Server Memory” setting of the SQL Server Instance properties.For SQL Server with Higher Memory, Allocate 10% System Memory to OSSQL Server Buffer Pool Extension Best Practices source: msdn.microsoft.comOnly Implement if you have High-Speed Disk I/O Subsystems (Fusion IO or SSD).Performs best with OLTP workloads which are read heavy.Not recommended for Data-warehousing or write-heavy workloads.Recommended for Systems with memory ranging from 8 GB – 64 GBAlso, works in SQL Server Standard EditionsIn-memory OLTP RecommendationsMemory Requirements are outside the regular SQL Server and OS Memory Requirements.Size your In-memory Tables for memory size and allocate memory to system OS.Not recommended for Systems with Less than 64 GB of System Memory.I hope this was useful. Just a reminder to follow the following Official Links for considerations directly from Microsoft:Server Memory Server Configuration OptionsOptimizing Server Performance Using Memory Configuration OptionsSQL Server Memory Options Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...