Information Technology News.

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 working correctly.

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 their accounts.

"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.

Source: Microsoft.

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.

IT News Archives | Site Search | Advertise on IT Direction | Contact | Home

All logos, trade marks or service marks on this site are the property of their respective owners.

Sponsored by Sure Mail™, Avantex and
by Montreal Server Colocation.

       © IT Direction. All rights reserved.