Compañeros! You heard rumblings about how awesome SQL Server 2016 is, right? Well the story in this episode will blow you away. Episode 38 of the SQL Data Partners Podcast covers a SQL 2016 case study that really shows the value of a knowledgeable SQL Server DBA using the latest SQL Server toolset.
Quote from Episode 38:
“Exactly the same queries, same amount of data, so we got the exact same number of records back from the queries, et cetera. And on two of those queries, on the Oracle racks it took over nine minutes and on our little VM it took us about a second.” – Rick Heiges on one client’s results with SQL 2016
I talk with Rick Heiges about the wins he’s seen for his clients using SQL Server 2016, including how one client’s move from a large Oracle rack to SQL Server garnered them huge improvements in query performance. As well, I ask Rick some #SQLFamily questions and learn more about the productivity tools and business advice he can’t live without. Episode 38 also includes:
- Why one Oracle shop moved entirely to SQL Server 2016
- The results of a showdown between a SQL Server VM and an Oracle Rack
- SQL 2016 Features
- Reasons why the Upgrade Advisor makes Rick nervous
- Why Rick says that waiting for SP1 is a bad move
As always, please join me on the trail by starting a conversation below or connecting on Twitter. If this podcast is helpful to you, please leave a review on iTunes. Subscribe to the podcast via: iTunes, Stitcher, or Podbean.
About Rick Heiges Rick Heiges is a Principal Solutions Architect and Data Platform MVP at DB Best. He’s served on the PASS board as Director and as VP of Marketing and has spent the last four as the Director Emeritus of PASS. Rick spearheaded the 24 Hours of PASS event and also started a local PASS User Group in 2002. Resources Connect with Rick Heiges on Twitter Follow Rick on LinkedIn Migrating Oracle to SQL Server (MSDN) Rick’s article about SQL Server Availability Groups
Transcription: SQL Server 2016
Carlos: So Rick, welcome to the program!
Rick: Thank you, great to be here.
Carlos: Yeah, it was nice catching up with you at the Richmond SQL Saturday. You were telling me about this interaction you had. I guess I should back up a moment. So, at the data-driven event, or the soft launch of SQL Server 2016, there was a big announcement in which they were going to start paying customers to actually switch from Oracle to SQL Server. We then started talking a little bit about the features of 2016 and you had told me about a customer engagement that you had where they were trying to do just that, either make the migration or considering making the migration. So I guess, tell us a little bit about that and maybe the reasons why they decided to give SQL Server a look.
Rick: Sure. Yeah so, at the data driven event they had this offer where you can trade in Oracle licenses for SQL Server licenses and there’s lots of caveats around it of course. So talk to your Microsoft rep for more details on that. But basically what they really want people to switch to SQL Server from Oracle and we were in a situation where we had a customer who’s running Oracle where a data warehousing system that’s being constantly updated with all this new data all the time, just flowing in. And they really were having some performance issues. And they were considering switching to SQL Server, and DB Best of course we do a lot of migration work, and so was put on this engagement to help them test out some of the new features in SQL Server 2016. Namely the clustered columnstore index. So to kind of paint the picture, they had a big old Oracle rack system. They had like four nodes and I think half a terabyte of RAM across the nodes, et cetera. And so that was the system that they had in their current production environment and also in their test environments. And we were given a teeny, tiny VM initially with only four CPUs and 8 gigs of RAM and we started testing. And we got some good results but I was like, “This is not a fair comparison.” So we got them to bump up the specs of the VM, nowhere near to what the rack is but still good enough to call it a bit more even, if you will. That was one of the big challenges we had starting off.
Carlos: Okay, so then what was the experience once you had gone through the columnstore process or introduced all these new features, what was the experience that they had?
Rick: They had a lot of queries that really were not performing well on their Oracle system and so we came in and helped them configure their VM, and their Windows, and their SQL Server, all the different configurations to set up to make it optimal. And we also helped them choose how to partition the tables accordingly and to also choose why type of indexes to go on each of the tables, because using columstore indexes are great. But they’re not the best for every situation. So we kind of went through that analysis as well. We’ve had some amazing results.
Carlos: Yeah, so let’s talk a little bit about them. From the way that it sounded, it totally blew them away.
Rick: Yeah, we ran five queries. Exactly the same queries, same amount of data, so we got the exact same number of records back from the queries, et cetera. And on two of those queries, on the Oracle racks it took over nine minutes and on our little VM it took us about a second to return the same.
Rick: Yeah, it’s amazing. And I was floored.
Carlos: That’s impressive.
Rick: And we would clear out the cache, et cetera every time before we ran the queries. And we still blew them away. And it’s just amazing to me what that really means to the business. I mean, they can make faster decisions, they can do more analysis, you know, get a better product. Which is really what they’re after.
Carlos: How much data was in that environment? Just for comparison, for people listening who want to think if they’re in the same boat.
Rick: Right. On the one main table that we were looking at, which was uncompressed on the Oracle system, it was about 100 gigabytes in size. There were other tables but that’s the one table we were focusing on. And after we put the columnstore index on it, we got down to 8 gigabytes of space. So, super compression and that’s really where all the performance comes from.
Carlos: I see. Now was that running on Linux by chance? Did you do a 2016 implementation on Linux?
Rick: No, this was before that announcement.
Carlos: So just a quick tangent here, when they announced that SQL 2016 was going to be on Linux, the Twitter universe kind of exploded with, “Ahhh, this is so great! This is so exciting!” Am I the only one that’s not excited about running SQL Server on Linux?
Rick: I think there’s lots of questions to be answered around that, right? But, if you look at it from a high level point of view, it gives customers who hate Windows another option for their database backend. So they don’t have to set up an Active Directory and other stuff just to run SQL Server. They can have it on Linux. So from that point of view, you know it’s big in that way. Also for ISV’s, they now have another choice they can provide their customers. Especially if everything else they do is in Linux and you know, they don’t want to set up Windows for SQL Server for that reason.
Carlos: Okay, so kind of some specific use cases.
Rick: That’s the way I see it, yeah. I’m sure there are lots of other use cases out there, as you mentioned, that are probably more exciting. But those will come to light of course as the launch of that becomes nearer next year.
Carlos: Right, yeah. They’re in preview now, so they’re letting people have access to it. And you have to sign up and get approved, and all that stuff. And I’m not sure what the requirements are there but I’m sure there’s some process. They’re trying to monitor it and get feedback. All that other good stuff. So it almost makes me wonder with that, because with 2016 there are lots of new features that have been available. Of course we’re going to dive into columnstore indexes here in just a minute. But do you almost think that, and Microsoft wanted to point out that, they were in the top right quadrant of the Gartner chart but they’re better than Oracle now and many of these facets and what-not. Do you almost think that SQL Server is…I don’t want to say too big…but there’s a lot of stuff in SQL Server now. Do you think there’s like an opening for Postgres and these other players to sneak up and grab, I don’t want to say the Access market, but in between?
Rick: Right, and I think to a certain degree that might be true. That might be another way to look at it but you know Microsoft has been chasing Oracle for quite a long time. And finally with all this new innovation they have with stuff born in the cloud and the vision that they have, it is really they want to tout that, “Hey, we beat oracle. We’re the leaders now. They just can’t catch us.” And I applaud that action for them.
Carlos: Yeah, I think it’s definitely great for us and for database administrators in the SQL Sever space. Lots of really nifty things we’re going to be able to leverage and columnstore indexes is one of those. So you had mentioned the compression of the indexes as being a factor. So maybe we should take a couple of minutes and go over a basic overview of what the columnstore indexes are.
Rick: Sure. So we’re always used to seeing data in the form of a table. Horizontal row over row with various columns and that’s the only way we see it because that’s the way we’ve always been taught to see it. And that’s the way we’ve been storing data forever. And so what they’re doing is they’re turning it on its side for storage, so that all the first names are going to be in a single column, all the last names are going to be in a single column, all the states, et cetera. Just think about that. Since they’re all in the same domain, if you will, you’re going to get ultra compression. Let’s just say on the state column, right? There’s fifty states plus D.C. and Puerto Rico and some other two-letter abbreviations. But you’re gonna have, you know, fifty different combinations. So your compression for state is going to be very high. And so that really lends itself well to data warehousing, because whenever you are doing queries for data warehouse you are usually aggregating many rows together and so if they’re all the same type of data, you know a similar type of data that you’re aggregating, then it just makes it go faster. Plus, it doesn’t decompress the rows when it reads into memory. It stays compressed in memory until it accesses those rows. So it really saves space there.
Carlos: So the whole columnstore index gets saved into memory? Rick: Well, segments of it, right? Like a page.
Carlos: So as it’s reading, it reads it to the buffer. And those pages are in that sense compressed.
Carlos: So, I know there have been some neat updates in 2016, and I know columnstore indexes were presented in 2014 but there were a number of restrictions. One of which was that you couldn’t update them.
Rick: Actually, that was 2012.
Carlos: Oh was that 2012? Okay, I’m going to have to go back in time then. So 2012… I have some 2012 instances. I should probably look at columnstore indexes. So let’s talk about some of the new features in 2016.
Rick: Yeah so one of the ones I talk about when I do my session on 2016, like I did at Richmond SQL Saturday this past weekend, I did a demo of row-level security. So that is now part of SQL Server. And so what that is is a way that you can assign like what type of data a user can see. A particular login or group of people can see. So as to the whole security thing, you have dynamic…
Carlos: Forgive me, before we jump into that, so I guess my question was more: can you give me some of the updates for columnstore indexes in 2016?
Rick: So in 2012, they introduced what they called the “non-clustered columnstore index”. And that was non-updatable, so that meant that when you put that non-clustered columnstore index on that table, you’ve effectively made that table read only. In order to update that table, you could do a sliding windows scenario with partitioning, or you could drop the index and rebuild it. Drop your index, add your data, and rebuild it, which doesn’t seem logical. In 2014, they added a clustered columnstore index and you still had the non-clustered columnstore index, but you could only have one of those. Actually, you could only have one of those in 2012 as well, so you could have one clustered columnstore index and a clustered columnstore index in 2012 had lots of great compression but they did even better work on it in 2016. So the compression is better, performance is better all around.
Carlos: And those indexes are now updateable as well? So I don’t have to do the sliding window for them?
Rick: Correct. And the other really cool thing about indexing in general is that you can have both your regular row store indexes and column store indexes on the same table. Which you say, “Well, what does that mean?” Well this really lets us get into something called operational analytics, where essentially, let’s say on a single node you have your production database where you’re doing all these transactions, right? And maybe you have an availability group on that and you have a replica of that exact same database. So you have columnstore indexes defined as well as your row store indexes for your production activity, and then you have your replica that’s readable. And so now I can have my production workload go to my primary replica doing all my transactions, then on a replica the exact same index structure and everything. The exact same databases, right? So I can redirect all my reads to my read-only replica to do my analytical type of processing. SO really what this enables is things like real-time fraud alerts. So I don’t have to wait until I load it into a data warehouse, hours or even days later to go through all the transactions to see if there’s fraud. I can do real-time fraud detection in that manner.
Carlos: So just to clarify in that scenario, in that high availability scenario, the columnstore index lives in both instances, but you’re really only utilizing it for the analytics in the read-only instance.
Rick: Correct, because a replica of a database is exactly the same on both nodes, yes.
Carlos: Very cool. So now we’ve talked about from the analytics perspective and all of the examples that I’ve seen or been exposed to have been in the data warehouse space. Is this really a data warehouse only feature? Or I guess you just gave a potential example of like a fraud detection environment. But are you seeing other scenarios where your people are using that in the OLTP environment, if you will?
Rick: Yes, yes. So I’m always like, “Use the right tool for the right problem.” So we still have row stores. They’re going to be very much in play for our transactional type of systems, because usually in transactions we’re going to do single lookups off of certain tables to make sure something’s there to bring it back, et cetera. And columnstore indexes are not good at that. They’re good at reading a whole page… many many rows of data… and doing an aggregation on that. So yes it is more of a data warehousing type of index, although like I said in the example I gave, you can kind of get the best of both worlds as well.
Carlos: Very neat. So, ultimately we talked about using the columnstore indexes in the aggregate form and choosing that best tool so I guess where are folks trying to implement columnstore indexes where it’s not helping them or potentially hurting them?
Rick: So if you’re just using a columnstore index without a row store index or traditional indexing, and you’re trying to do what I call singleton type lookups, that’s to where you’re not going to get very good performance.
Carlos: Right, because you’re not optimizing what the index is for.
Rick: Yep. Yeah, kind of like, we would say that in other indexes where you have created an index but don’t create all of the columns you need, for example, then SQL Server will say, “Well it’s faster for me to do it this other way and that’s the way I’ll go about doing that.”
Rick: Yeah, with the covering query or the covering index. Yes.
Carlos: Okay, well very good. Now we had kind of gotten into some of the other features. Do we want to circle back to row level security now?
Rick: Sure, I just mentioned that it’s a big thing for us, for DB Best, because row level security has been a part of oracle and IBM DB2 for quite a long time. And so now we have a feature that we can easily convert to or migrate the customer to, where it was a big pain in the butt before. So that’s it. Other security features like Dynamic Data Masking and Always Encrypted are going to be big in 2016. Stretch Database, even, where you can have part of your database on-prem and the other part in the cloud. Those are some of the cool features that I look at whenever I think about 2016.
Carlos: Yeah so I’m a little nervous about one of the features. And that is, so JSON coming to 2016. And IO was listening to Jovan Popovic, I think he’s the program manager that was talking about that. I thing that’s from the single instance of that conversation, and he was saying that you basically it can fit in any data type column because it’s just JSON. So you can stick it in a character field, a varchar field, whatever and then just start using JSON. I think it will be interesting to see how that plays out in different environments. Rick: Yeah, and when I see JSON, I just think of XML.
Carlos: Exactly, very similar.
Rick: Very similar to that. And as you said earlier, SQL server has so many different things in it, especially 2016. It’s really hard to get your head all the way around everything in this release.
Carlos: And one of the things that’s also really cool, and we’ve mentioned this, in that SQL Server or Microsoft ultimately wants to get you in the cloud and wants you to start using Azure. And the migration wizard or the prep tool to identify your upgrade to 2016 has a lot of neat bells and whistles in it in that it will help identify not only if you’re ready for 2016 but if you’re ready to go into Azure. And to use some of these other services, like Azure SQL Database or Stretch like you mentioned, and so I think there is a lot more hand holding or direction on if you’re ready to use these features and if not, here’s what you need to do to correct it before you go forward and knock your head on the wall for a couple of hours trying to get something to work that isn’t ready to do so.
Rick: Yeah and you’re talking about the updated Upgrade Advisor? Yes, and I have good and bad feelings about that. It’s great that they want to help them upgrade to the latest version, maybe to Azure. But the Stretch Database Analyzer that’s part of that, to me, it seems like they’re forcing that idea on you. And even if you don’t need it, if you would run it, it would say, “Yeah, sure! You can use Stretch DB. We don’t see anything wrong with it.” And just because you can doesn’t mean you should. And that’s part of the good/bad thing about it. ]
Carlos: Right, it’s going to show you the world, “Here are all of the things you can do!” yes. So with that data comes some responsibility to pull in the reins a bit and like you said, use the right tools for the right problem.
Carlos: Well, I think everybody is excited about 2016. We’re at release candidate 1 right now, and I think though no date was specified in the data-driven event, I think everyone is expecting it to come out here in the next little bit, at least in the summertime. We’ll see what the adoption is and how people go about looking at that. Another item there is that I think there’s still some sentiment out there in the companies where they’re waiting for, like, a Service Pack 1. But now with the rapid release cycles that we’re seeing, particularly in Azure, the mindset is going to have to change a bit there.
Rick: Definitely. And I’ve already been talking to my customers over the past couple of years about, “Hey, all these features that you see in the new version? They’ve already been tested in Azure.” And so the old adage of waiting for SP1 before you upgrade doesn’t really apply as much anymore these days. And actually they probably already have SP1 already on the board at Microsoft just for that reason, you know?
Carlos: Yeah, it’ll be interesting to see how long the delay is between the release and Service Pack 1. That will be interesting. I think in the past, now don’t quote me on these numbers, but I have a feeling that it was like a year. Is that not right?
Rick: I know in 2005 SP1 came out five months later. But that was mainly because of database mirroring.
Carlos: So there were some things they needed to resolve there very quickly. Very good. So Rick let’s do SQL Family. So one of the things I always like to talk about with folks is tooling to get work done. So tell us about your favorite SQL tool or tool that you use to get work done, why do you use it, and what does it do for you?
Rick: Well I’m a consultant, so I don’t actually do any work, so I really don’t use tools.
Carlos: [laughing] Yikes.
Rick: Seriously, actually, I’m just fine with the Management Studio. I’m used to it. And because I go into so many different environments, it’s ubiquitous wherever I go. So that’s mainly what I use.
Carlos: Interesting thought there. What do you think about the tighter integrations of the Visual Studio? Any chance that you’re going to start using Visual Studio User Interface?
Rick: Probably, as more customers upgrade, et cetera, and you know I’ll be going in that direction as well.
Carlos: Awesome. So you just inherited one million dollars. What are you going to do with it?
Rick: [Pausing] Probably put it away for retirement, because, um, I like working and I don’t think I’m going to retire anytime soon. That’s a ways off anyway, but still. I’ll just put it out there for retirement and go that way.
Carlos: You know you’ll be set. Very good. So what’s the best piece of career advice you’ve ever received?
Rick: Probably to sound confident and don’t be afraid to say no.
Carlos: So saying no when people approach you and say, “Hey, can you do this?” Or saying no when I don’t know something? Rick: Well, I say that anyway, because I don’t know everything and I’ll readily admit that. No, to say no whenever I feel like I’m getting too much stuff on my plate. I’m not afraid to give that advice to my people who come on-board with us. I say, “Don’t be afraid to say ‘no’ if you feel like you are getting overworked, because if you don’t say no, it’s going to be your fault.”
Carlos: I think that is a good piece of advice. At least, trying to frame that a little differently. Pointing someone in the right direction, there might be some nice ways to say, “I can’t take this right now, but so-and-so can help you.” But yes, that’s a good piece of advice. Rick, our last question for you today: if you could have one superhero power what would it be and why would you want it? Rick: It would be to impose my point of view on everyone around me.
Carlos: [laughing] Wow, okay. I don’t know, I’m assuming there’s a super or a hero out there that’s like that. None I guess comes to mind. Maybe the mind meld folks.
Rick: Just think about it, right? It’s the ultimate weapon. You have other people think the same way that you do, so it works great.
Carlos: Oh boy, we’re going to have a whole bunch of Rick Heiges running around.
Rick: That’s right. Domination.
Carlos: Rick, thanks so much for being on the show today.
Rick: Great, it was a lot of fun and I really enjoyed this. It was great seeing you this past week and I look forward to seeing you again soon.
Carlos: Thank you.