There was a hungry RAM-eating bug in SQL Server 2014 last week
Share on Twitter.
Sponsered ad: Get a Linux Enterprise server with 92 Gigs of RAM, 16 CPUs and 8 TB of storage at our liquidation sale. Only one left in stock.
Sponsered ad: Order the best SMTP service for your business. Guaranteed or your money back.
February 9, 2016
Earlier today, it was confirmed that Microsoft's technicians have pinpointed the critical
SQL query that brought MS' Visual Studio Team Services offline for several hours last week.
At first glance, it appears that the SQL query triggered a crippling SQL Server 2014 yet-undiscovered
bug that Microsoft is now trying to repair. A fix isn't ready yet, we are told.
Microsoft's cloud-based Team Services suffered a five-hour outage last week, leaving programmers
unable to access or manage their own accounts.
Team Services provides tools for sharing code, tracking changes, testing various builds, etc, when it's
We were told the downtime's culprit was "an SQL stored procedure that was allocating too much RAM memory,"
and thus overwhelmed the service's numerous database machines.
That critical SQL query was working fine on Microsoft's SQL Server 2008, but went totally haywire when MS'
engineers reconfigured their systems to run the procedure on SQL Server 2014. That's when all hell broke loose.
The complex issue lies in the rewritten 'cardinality estimator' in SQL Server 2014. Cardinality estimation
is supposed to improve a database's performance by predicting the number of rows a query is going to return,
and allocating resources early, Microsoft's SQL team said.
This simply means that a database server can randomly take any amount of RAM memory it needs before
getting stuck into executing the SQL statement, thus avoiding the need to allocate chunks in memory on-the-fly
(which can introduce deadlocks and other slowdowns) or swapping to disk which would drastically alter performance
and speed, not to mention crashing the whole server which is what probably happened last week.
Used by 'Team Services' to properly handle customer accounts, the crucial SQL query authorizes its users and is
supposed to pull up one row. Obviously, this isn't what happened last week.
The bug caused SQL Server 2014 to predict that the query would return many more rows than the single
one needed, hence the main cause of the whole issue.
As a matter of fact, the software thought it would require 3.5 GB of RAM, rather than a very small
amount to cope with the request, which is bad news on a server with just 48 GB of RAM to serve potentially
thousands of users.
This query caused MS' servers to rapidly run out of memory and leave customers unable to access
"In the SQL Server 2014 query optimizer, Microsoft engineers made significant changes to the cardinality
estimation. I’m sure they were improvements but not for this query," said Brian Harry, Microsoft's vice
president for cloud developer services.
"For now, the ultimate resolution is that we added a hint to the query that tells its optimizer the maximum
RAM memory grant to use for the query. Longer term, littering our code with memory grant limits is a very
unsustainable practice. We use query plan hints a lot but memory grant caps are too fragile over time," added Harry.
"So we are working with the SQL team to see about various changes in the cardinality estimator that
will do better for this query and others like it. There are also changes we can make to the way this
query is written that will lower the likelihood of mis-estimations. It turns out that in most cases, this
query only operates on one row, but from a very big table, the cardinality estimator guessed a lot more than
one row," added Harry.
In the meantime, if you're running SQL Server 2014 with big tables, be mindful of a sudden devouring of available
memory on your server, since that could be a red flag.
Order a powerful Linux Enterprise server with 92 Gigs of RAM, 16 CPUs and 8 TB of storage at our liquidation sale. Only one left in stock.
Get the best SMTP service for your business. Guaranteed or your money back.
Share on Twitter.