SQL Server uses more memory than "max server memory"

今天我遇到的一个现象就是虽然"max server memory"已经设置成了58.9G，但在windows task manager里面看到sqlservr.exe使用的内存已经达到了61G。

原因是

SQL Server will consume as much memory as you will allow it. By default, that number would encompass 100% of your numerical memory on your machine. That's why you're seeing what you're seeing. If you give SQL Server 24 GB of memory, then SQL Server will do its best to use 24 GB of memory. Then you have SQL Server and the OS battling for resources, and it'll always result in poor performance.

When you set the max server memory configuration limit, you are limiting how much SQL Server can allocate for the buffer pool (virtually where it stores data pages and the procedure cache). There are other memory clerks within SQL Server, so for your particular version (2008 R2 and below), max server memoryjust controls the buffer pool. But this is always going to be the biggest memory consumer.

也就是说"max server memory"限制的仅仅是buffer pool size而不是SQL Server使用的全部内存的上限。而SQL2008 R2之后的版本没有这个问题。因此2008R2及之前版本，我们没有办法设置SQL Server使用的总体内存的上限。当发现SQL Server占用过多内存导致OS内存不足时，唯一的办法就是重启SQL Server以释放内存。