SQL Server 2014 memory allocation
Reading the blog here:
https://blogs.msdn.microsoft.com/bharry/2016/02/05/vs-team-services-incidents-on-feb-3-4/
It appears there is a serious bug in SQL Server 2014.
In the SQL Server 2014 query optimizer they made significant changes to the cardinality estimation. I’m sure they were improvements but not for this query. The cardinality estimation was used to estimate a memory grant for the query (SQL preallocates memory for queries to avoid spills to disk, which are big performance problems and additional memory allocations, which create the possibility for deadlocks. The cardinality estimate is an important input into the memory request).
In this query, the memory grant estimation shot up from something pretty small to 3.5GB. Given that the server only has 48GB, that meant that it could run very few of these queries before it ran out of memory, causing every query in the system to back up and, essentially, serialize. That caused a traffic jam and resulted in so many of our customer requests timing out/failing.
The ultimate resolution, for now, is that we added a hint to the query that tells the query optimizer the maximum memory grant to use for the query. It’s expressed in % of memory and, for simplicity’s sake, we set it to 1% of the memory available for this (or more on the order of 160MB). That was enough to unclog the system and allow everything to flow freely.
It is not clear from the blog whether this is a custom version of SQL Server 2014 used internally by Microsoft, or whether it is the production release. If it is the latter, then anyone running SQL Server 2014 in SQL Server 2014 compatibility mode is likely to suffer issues with massive over-allocation of memory to queries and stored procs.
Maybe El Reg can clarify this?
Biting the hand that feeds IT
