Architecting Microsoft SQL Server on VMware vSphere has the following sample values: Memory (MB) In VMware, the exact amount of overhead used by the VM will vary after it starts up, but the initial memory used at startup is a good estimate. Watch the following counters and monitor them for deviations from baseline values:įor a SQL Server on a VM, you will also need to allocate memory for the overhead of powering on the VM itself. In both the physical server and VM cases, monitoring overall memory use will determine if the OS and applications require more memory. For VMs, allocated memory can be increased as long as the active memory across all running VMs does not approach host memory capacity. The challenge when working with a finite amount of memory on physical servers, is to balance providing enough memory for SQL without unduly limiting the memory available for the OS and applications.
The exact amount of memory required for satisfactory OS and application performance is dependent on the server’s function and load. The thread memory is based on processor architecture: x86įor example, for 8 圆4 processors, the default maximum number of threads is 576 (see ), so the ThreadStack memory would be: See Configure the max worker threads Server Configuration Option for the default maximum number of threads per CPU.Įach thread has memory associated with it.
HOW TO DETERMINE RAM ALLOCATION 32 BIT
SQL threads are used to handle query requests – the maximum number of threads depends on the number of CPUs and whether the OS is 32 bit or 64 bit. More memory is needed if Target Server memory is greater than Total Server memory. Memory Manager: Target Server Memory vs Total Server MemoryThe Target Server memory is the amount of memory that SQL wants to consume – Total Server memory is what it is actually consuming.More memory is needed if this value is >=1. Memory Manager: Memory Grants PendingIdeally there should be no processes waiting for memory and this should = 0.If the ratio drops below this value it could indicate memory pressure, but it might also indicate badly constructed tables and/or indexes. Buffer Manager: Buffer Cache Hit RatioThe Buffer Cache Hit Ratio is the percentage of pages that were found in the buffer pool rather than needing to be read in from disk – this should be > 90%.Monitor for a persistent drop below the baseline value. Buffer Manager/Buffer Node: Page Life Expectancy (PLE)PLE indicates the number of seconds a page in memory has to live if it isn’t touched – if there is memory pressure, this value will decrease.To determine if more memory is needed for SQL, monitor the following Performance Metrics: The recommendation for Miscrosoft SQL 2016 Standard and Enterprise editions is that 4 GB memory be available for each SQL instance, with increases in memory as the SQL databases increase in size. SQL Server Maximum + SQL ThreadStack + OS/Application + VM Overheadĭownload Achieving Peak SQL Performance – Learn how an improved monitoring strategy can help optimize database performance and reduce the amount of dedicated resources needed for database monitoring and management In order to calculate the initial memory allocation for a VM, take into account the following components: On a Virtual Machine (VM), since you can add more memory as needed, you can adjust not only the memory allocated to SQL, but also the overall memory on the VM.
HOW TO DETERMINE RAM ALLOCATION HOW TO
On a physical server, with a fixed amount of memory, that means making an initial estimate of how to divide the limited system memory between SQL, the operating system, and other applications on the server.ĭetermining how much memory to allocate to SQL without compromising the performance of other applications or the OS requires making an initial estimate, and then adjusting that based on observed performance metrics. Microsoft SQL Server uses memory to improve performance by caching data, and it will use as much memory as it can based on how much it has been allocated.
0 kommentar(er)
