A few months ago, there was an interesting conversation that uncovered this recommendation for maximum & minimum server memory setting for VLDB instances. Memory is a crucial to any database servers; And it becomes more important for VLDBs. So it is important that we achieve optimal memory settings. As with anything else, the correct setting depends on several factors and it varies from instance to instance. Here we’ll cover general recommendation that could be used as baseline.

Usually database servers are dedicated machines. So, the only major application running on the OS is the Sql Server instance. So, we need to carve out memory for OS and Sql in a way that both run efficiently. For database server instances on VM or shared machines, memory configuration gets a bit more complicated; but this could used as a general guideline to start with and apply more relevant factors on top of it.

First, query your current settings:

For VLDB’s, generally the size of memory is larger than other database servers. As the server memory increases, the configuration changes. The general point is that we need to allocate as much memory to the Sql instance as possible. But this should not be at the cost of OS itself. So, we need to strike a right balance so the OS could run efficiently (without paging) while allocating as much memory to Sql instance as possible. To achieve this, we use the two settings available for each instance.

• min server memory (MB)
• max server memory (MB)

For VLDBs with more than 64 GB, we need to consider more factors on top of the guidelines above. We’ll dive into it in a future post.

Hope this helps,
_Sqltimes