Finding the right problem for SQL Server 2014's in-memory OLTP

Consultant Denny Cherry talks changes, misconceptions and what people really want from SQL Server 2014, which is generally available on April 1.

Microsoft SQL Server 2014 was released to manufacturing March 18. The general release will be April 1. Denny Cherry, principal consultant at Denny Cherry and Associates Consulting, shares his expertise in deciphering the new release.

Microsoft has been hyping the new in-memory capabilities and the hybrid cloud. Which of the new features are actually attracting the most interest?

Denny Cherry: I'll give Microsoft a one out of two on that one. The in-memory stuff -- definitely. Project Hekaton or Project Apollo, also known as in-memory OLTP or column store, are definitely technologies that people are interested in deploying for tier-one applications. As far as AlwaysOn Availability Groups to the cloud or backups to the cloud, I'm not getting good feedback on that stuff yet from my customers. I think access to the cloud is only going to be the first thing people look at if they're interested in that sort of thing. For tier-one apps, people aren't really looking to push stuff to the cloud all that much. Microsoft is really all-in on the cloud. Unfortunately, their customers are not in.

What trends have you noticed regarding interest in SQL Server 2014?

Cherry: I haven't noticed any trends in any specific verticals yet. People are looking for in-memory OLTP, but those are companies that have really high OLTP workloads they need to deal with. I have noticed, especially at the SQL Saturday I was at this weekend, a lot of guys come up to me expecting me to tell them that in-memory OLTP is going to be able to solve their problems, but their problems are not the kinds of problems that are going to get solved by it. There's a lot of misinformation out there about what in-memory OLTP is going to do and what problems it's going to help solve for people. I think there is still going to be a lot of wading through the marketing to find out what problem it is actually built to solve.

You've spent a lot of time testing the beta version of SQL Server 2014. What are your thoughts?

Cherry: I think SQL 2014 looks like a good upgrade. Microsoft has spent a lot of time working on the features. I think they've come a long way. They're ready for prime time. It's just a matter of getting people through the upgrade process, but I don't think this is the kind of system where you have to wait for the first service app to come out in order to get the system deployed. It's going to be ready to go to bed. There are no show-stoppers in it. There are a lot of great features in it that are worth upgrading to if you've got the right problems to solve.

What are the right problems for SQL Server 2014 to solve?

Cherry: There are basically two kinds of speed impairment. There is writing data slowly and reading data slowly. Writing data slowly can be solved with the in-memory OLTP feature. If you're having problems with reading data because you've got huge reports you need to run or huge volumes of data to process, that's where the other in-memory technology, the column store, comes into play. It's just a matter of figuring out which one of those you actually need and making use of it. SQL Server 2014 also has AlwaysOn Availability Groups. You can scale them out bigger if you need to put servers in multiple data centers. Say you need three servers per data center -- you couldn't do that before with AlwaysOn and SQL Server 2012. Now you can with AlwaysOn and SQL Server 2014. This is because you can have eight secondary replicas now, including the primary. That gives you three servers per data center and three different data centers.

Is SQL Server 2014 addressing customer demand well?

Cherry: The features in SQL 2014 are definitely addressing read and write issues well. Those are big issues people are complaining about, which is why those features were introduced in this platform. These features were driven 100% by customer demand.

Are you anticipating problems with implementation?

Cherry: The only problems that we're going to see with implementation are going to be for the people who are implementing the wrong feature to try to fix their solution. But if you pick the right feature to fix your problem, then the implementation should be fairly straightforward.

What are the most serious changes you've found in SQL Server 2014? How will these changes affect companies?

Cherry: There are some pretty big changes in how some rather in-depth pieces of the engine work, including the cardinality estimator. If they build their app expecting the cardinality estimator to function one way, but now it's going to function another way, it could lead to problems. That's a really big change, because the cardinality estimator is something that hasn't been touched in 15 to 20 years.

It's been changed due to customer request because we've needed it changed for a long time. The cardinality estimator is a very core piece of the database engine, so it took a long time for Microsoft to be comfortable making changes to it, because if they did any little bit of it wrong it could have destroyed the entire engine. So the fact that they've made the change and released it means that they've done a thorough amount of testing, but it is a change in how the cardinality estimator makes decisions. So, people are going to have to understand this change and what it means to the platform.

How has the cardinality estimator changed?

Cherry: In a nutshell what the change was -- say you have an invoice table where you're always adding new data to the bottom. Then you go looking for the row you just added. In SQL Server 2012 and below, SQL Server assumed the row didn't exist, because it didn't know that invoice number had been issued yet. Whereas, in SQL Server 2014, they've made a change so that SQL Server now assumes that the row does exist, even though it doesn't know the invoice number has been issued. That's a pretty core change to how the system functions. People have been building apps based on the old assumptions for a long time now, so there may be some changes people need to make in their apps. People will see the cardinality estimator responding differently than it did before. The response should be for the better almost all the time, if not all the time.

Do you have any last recommendations?

Cherry: The biggest feature that I think a lot of companies are going to be able to take advantage of is that the memory limits have been raised for Standard Edition. This is something we've been asking about for a long time because it's so easy now to have a server with more than 64 GB of RAM, and 64 GB used to be our limitation. That's a change to the Standard Edition, which is something we don't get all that often. Usually everything goes into Enterprise Edition.

Dig Deeper on Microsoft SQL Server Installation