• Repost - originally posted 10-10-2012

SQL Server memory allocation


Whilst reading a SQL newsletter the other day from a well respected software development company and Microsoft Gold Partner, the article suggested that to get the best performance from SQL Server you should set the memory maximum to 256MB less than the total available server memory (512MB at most). My thought is that this would strangle the operating system and cause paging and therefore poor performance. Most Windows operating systems require up to 4GB of memory and Windows Server 2012 and above require even more. So my default is 4GB to the OS, the rest to SQL Server on a dedicated database server. In a virtual environment remember to give the virtual hosts OS at least 16GB and then carve up the rest to the VMs. Even then you must remember that the VMs OS needs its own memory as well (4GB as my default), then SQL Server can have the rest.


2 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t