Better SQL Server memory defaults in 2019

In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias. SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has
-> Continue reading Better SQL Server memory defaults in 2019

Proposed SQL Server defaults: max server memory

A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ad hoc workloads to enabled Set max server memory (MB) to a
-> Continue reading Proposed SQL Server defaults: max server memory

SQL Server logo

Fix SQL Server with one click

Tempting headline, isn’t it? It might even seem like clickbait, but that’s not the intention. The SQL Server default configuration is not recommended for production environments, and yet I have worked on many production environments that have been set up by people who don’t know that the default configurations are not recommended. These same people
-> Continue reading Fix SQL Server with one click

Post image

What you need to know about memory limits on SQL Server

Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation: Reminder: Max Server Memory is *not* just for the buffer pool. The RAM limit Standard Edition can use *is* just for the buffer pool. Joey D’Antoni was quick to remind me that this only applies to SQL
-> Continue reading What you need to know about memory limits on SQL Server

Post image

Max Server Memory and SQL Server 2016 Service Pack 1

Everything changed for SQL Server Standard Edition on 16 November 2016, and how memory limits work. On that day, a slew of Enterprise Edition features made their way into editions across the board, including Express Edition and LocalDB. The memory limit of 128GB RAM applies only to the buffer pool (the 8KB data pages that
-> Continue reading Max Server Memory and SQL Server 2016 Service Pack 1

Post image

Updated Max Server Memory Script

Earlier this year I released a free T-SQL script that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance. After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team’s GitHub repository and discovered something similar, but not quite the same, in the
-> Continue reading Updated Max Server Memory Script

Post image

Max Server Memory Matrix

When we perform diagnostics on an existing SQL Server instance, or if we are setting up a new machine, it’s very important to make sure that we change the Max Server Memory setting to an appropriate level. If we don’t, then there’s a good chance (especially on Enterprise Edition) that SQL Server will use all
-> Continue reading Max Server Memory Matrix