Episode 190: SQL Server 2019

Episode 190: SQL Server 2019

Episode 190: SQL Server 2019 560 420 Carlos L Chacon

Getting released just before the end of the year, SQL Server 2019 is now available in general release. While at first glance, it appeared this release was all about Big Data Clusters, and rightfully so–it brings ‘cloud first’ options to on-premises scenarios; however, it is not the only big feature. We spoke about some of the Polybase changes in Episode 180 and in this episode Javier Villegas guides us as we dive into some of the performance enhancements–and run out of time. This just means we need a few more episodes to dig into more features.

Episode Quotes

“It’s been a journey to get into SQL Server 2019. Every time that you get into a major SQL Server version, you say, ‘come on, what’s left?’”

“In this particular version, they are focusing very heavily on three pillars: performance, security and mission-critical availability.”

“This concept of notebooks is really great in where you can encapsulate your whole set of scripts in only one notebook and then share it with everybody, including the results.”

Listen to Learn

00:38     Intro to the guest and topic
01:50     Compañero Shout-Outs
02:50     Big Data Clusters is the star of the show
06:19     Soon, SQL Server will run everywhere, on any device
08:07     The three pillars of this version are performance, security and availability
09:59     Scala functions and UDF Inlining
15:09     Intelligent Query Processing doesn’t rely on Query Store
16:33     Big improvements related to memory grant feedback
19:39     Approximate Count Distinct has a 3% variation but so much faster
23:12     How to find out if you can use these new features
25:06     Rollback is much faster with Accelerated Database Recovery
28:13     In-memory TempDB uses metadata
31:41     Improvements to the setup process
33:47     A bunch of other new and improved features, including notebooks
38:58     SQL Family Questions
42:02     Closing Thoughts

About Javier Villegas

Javier Villegas is a Database Administrator Manager for Mediterranean Shipping Company. Javier has more than 18 years working with SQL Server on every version from 6.5 to SQL Server 2019, Including SQL Azure Database and SQL Azure Managed Instance. His specialization is Administration, Performance Tuning and High Availability.

He is a Microsoft MVP in Data Platform.

Javier is a frequent speaker in conferences and events such as SQL Saturday , PASS Virtual Groups, 24 Hours SQL PASS , NetConf, GroupBy and DataPlatformGeeks.

*Untranscribed Introduction*

Carlos:             Compañeros! Welcome to another edition of the SQL Data Partners Podcast. I am Carlos L Chacon and I am joined today by my special guest, Javier Villegas.

Javier:             Hello, hello. Hi, everyone.

Carlos:             Before we recorded, he said, “this is going to be my fourth episode,” which is kind of cool, right? And I know Kevin was counting episodes, once upon a time. I don’t know if he’s still counting episodes, but once upon a time he was.

Kevin:              Only for illegal purposes.

Carlos:             Yeah. And Javier makes a comment, he says, “well, somebody might get to four episodes, but they can never be the first.” So, Javier’s back, from the first episode and admittedly, I can’t remember the other episode you were on, but I know we did another one on Managed Instance.

Javier:             Managed Instance and we did one related to the features in SQL Server 2017, the community driven features.

Carlos:             There we go. Features.

Javier:             And there was another one, yeah, yeah.

Carlos:             Having said that, it is timely to have you back, because today’s topic is SQL Server 2019. And it’s been out for a little while, so we want to talk about that. So Kevin said hello. We also have Eugene with us, today, as well.

Eugene:           Howdy.

Carlos:             Okay, so before we get into SQL Server 2019, we do have a couple of shout-outs we want to give. And as always, compañeros, you’ll forgive me if I mispronounce these. So, shout-outs to Aaron Charrier and Wiluey Sousa. So, Aaron is a long-time listener and said that his favorite episode is Episode 124 with Greg Moore. That was on the run-book. And ironically, that was during that time, we were experimenting with different things and we tried to implement sounds or like special effects– special effects is a strong word, but sounds, other, like, music or things. Cause I was listening to other podcasts and I’m like, “hey, we should do something like that.”

Kevin:              Getting the drops in.

Carlos:             That’s right. And compañeros, no, they did not like it.

Kevin:              We did not. We had pitchforks, tar and feathers.

Carlos:             Yeah. That’s right, so that’s what stands out to me about Episode 124. Although Greg did have very interesting stories, as well. Okay, so the show notes for today’s episode will be at sqldatapartners.com/sql2019 or at sqldatapartners.com/190. Okay, and so we were joking with Kevin a little bit before, that he said that PolyBase was the only feature he remembered being released in 2019, for some odd reason. I’m sure it has nothing to do with the book that recently came out.

Kevin:              Yeah, oh, I also remembered, of course, there was Java and Machine Learning Services. How could I forget that?

Carlos:             Yeah.

Eugene:           Don’t forget there was better UTF encoding. You can save a lot of space, now.

Javier:             We’ll be talking about that, yeah, yeah, yeah.

Carlos:             Yeah, yeah, and so we’re kind of jumping in there, so I guess, Javier, I’ll let you take a first crack at it. So let’s do new features and we can talk about improvements. So, new features, what kind of jumps out to you or what stands out to you from a SQL Server 2019 perspective?

Javier:             Well, actually, it’s been a journey to get into SQL Server 2019. Every time that you get into a major SQL Server version, you say, “come on, what’s left?” This is a really mature engine. I mean, it was already 2017. And you say, “okay, when they announced 2019, they say, ‘well, it’s going to just have few minor cosmetic things,’” like what we were joking before, 2017 R2. But in reality, when you get your hands into the first previews, you see that it’s full of new features and big, big changes. Right? The most important, let’s say highlight of SQL Server 2019, is this thing called big data clusters. So, with this version, we can install SQL Server on a particular way, combining the power of Spark and SQL to do some sort of scale-out of SQL Server. Processing from in one end, storage from one end, having only one master instance, and from there, access SQL, but also, access other database engines, SQL, NoSQL, IoT, streams, a bunch of things. So, this big data clusters concept will be the new concept of the center of the star to access every type of data that you have in your organization. This is going to be huge, also, for when big companies have merged, or acquisitions and you get into the company that is being bought has a completely different structure in terms of data platform. This is going to be huge. And Microsoft put a lot of emphasis on this feature, so this is the star of the show, but it’s not the only one.

Carlos:             Sure, so you gave a couple of good scenarios in which big data clusters might come into play. I feel like Kevin wanted to jump in, here.

Kevin:              No, not really. We covered an entire episode, so if you want to learn more about big data clusters, definitely go check out that episode.

Carlos:             That’s 183.

Javier:             183.

Kevin:              See, Carlos does show prep.

Carlos:             That’s right.

Javier:             Yeah, yeah, yeah, I heard that episode was really great, focused exclusively on big data clusters. But what I would like to go through today is what the other features and improvements. Well, definitely, first thing to say is, of course, Windows, Linux, containers and Kubernetes, what is this? Initially, we all know, SQL Server runs exclusively on Windows. Then, starting 2017, we got Linux and the first steps with containers and Docker, and now Kubernetes. So, now you go to the download page of SQL Server 2019 and I remember just downloading one iso file for Windows and now you have really to go through and search through the one that you really want to proceed. We also have the concept of Azure, so it’s a similar thing, but also in Azure. So, SQL Server today runs, I would say, everywhere. Also, like a sneak peek, you know that they announced SQL Server on Edge, on ARM device, Raspberry Pis and these kind of things. So, this is in preview, so literally, it’s running everywhere, right?

Carlos:             Right. Which I’m not sure, should I be happy about that, or a little bit afraid?

Kevin:              I’m happy about it. I’m pretty happy, cause like that’s targeting things that SQL Lite will do, so it’s a lot better than SQL Lite.

Javier:             Yeah, it’s a good approach, so let’s try to run it everywhere with the same footprint, the same set of features runs on every engine. It doesn’t matter which OS, which technology you’re using to run SQL Server, they use the same base, which is really good.

Carlos:             Sure.

Javier:             Okay, so now going through the features, properly, in this particular version, they are focusing very heavily on three pillars: performance, security and availability, mission-critical availability. So, these are the three things that they are focusing very, very, very hard. Again, and when you start thinking of what they can do in the new version of SQL Server that they haven’t done before with 2017, one of the ones that I like the most is, remember in 2017 when they introduced this Adaptive Query Processing technology? The possibility of SQL Server engine gets its own feedback of the execution plans and adjusts on the fly. But I mean, this was huge. Something that I normally speak with my colleague is, “okay, now Microsoft is focusing on stability, on this problem that for sure nobody had in their data center that one day they received a phone call and whatever it is was running okay. Today it’s not, and instead of taking a few minutes, it’s taking hours. We all face that, so, Microsoft with this technology, is trying to at least mitigate those situations. So, in SQL Server 2017 with Adaptive Query Processing, the memory grant feedback, those kind of things were really a good entry-level for the V1 of this new feature. In SQL Server 2019, they went to a step further and they kind of renamed the feature actually, put an additional layer at the top called Intelligent Query Processing. So, besides all the features regarding to memory grant feedback, they have this extra layer. One of these features is called UDF Inlining. What is that? Maybe you remember that many DBA say, “okay don’t use Scala functions in SQL Server because they are the devil.”

Carlos:             Slow. Right, yeah.

Javier:             Right? Yeah. And also, I remember a few months back before you know we got SQL Server ’19, I saw an article in a technology newspaper saying, “Microsoft is finally addressing at 20 years back in SQL Server.” And well, this was this thing related to Scala UDF. Basically, just to summarize what the problem was is that you can– I mean developers love Scala UDF, right? You can put your whole business logic, within a Scala user function, and then you put it in a select statement, you can put it into the predicate under the WHERE clause, etcetera, but the SQL Server engine used to consider whatever is in that function that has zero wait. And then consider the whole execution plan based on that zero wait for the Scala UDF. But then, in reality this was not the case. If you get very, let’s say creative, as I said before, put in a lot of logic-accessing data from the function, etcetera etcetera, you can have a really hard time. And in SQL Server 2019, with this feature and their Intelligent Query Processing, they start addressing that problem for some sort of Scala functions in where the engine initially get what the body of the Scala UDF and then embed or reuse the whole code within the main execution plan. So, it gets the logic of what is encapsulated in the function and then uses it. So, the execution plans and end-result, the performance is more stable and more, let’s say, matching what the reality is. I mean, this thing, before, even in my company, I told the developers, “don’t use Scala UDF that has data access. You can use only Scala UDF if you want to, let’s say, SUM two variables, but nothing related to data access. So, in the Scala UDF, if you have a SELECT FROM something, it’s forbidden.” I remember many years ago, going to Microsoft saying, “give me a switch so I can disable this from the engine, because they are really evil.” So, you can hurt the performance. We used to bring down big, big servers with two or three of these functions under the predicate. But, in this version they are starting to address this long-term, I don’t know if we should call it back or what, definitely not the feature, but it’s being addressed. And they are doing a great job.

Carlos:             Yeah, so then ultimately, the enhancement is those scalar functions are now getting additional details in the execution plans?

Javier:             Correct, correct. So, before they were considered, the wait was zero.

Carlos:             So, at least now there is something, like they’re trying to say, “oh hey, I see this. Let me try to assign some value to it.”

Javier:             Correct, correct.

Kevin:              Right, think of it as the functions are brought in line, so instead of having some function that’s just out there and, “oh whatever, who knows how long it’s going to take.”

Carlos:             Yeah, almost like a remote query.

Kevin:              You’re actually bringing it to the plan.

Carlos:             Right. So then, in your, I guess testing, how much weight are those functions getting? Are they, you know, Javier, you mentioned kind of taking that first step. It is a useful step, or is it kind of like, “eh, okay”?

Javier:             Oh yes, no, it is. It really is. As I said, many years ago, we forbid the developers to use those, so we don’t have many today. But we definitely have cases in where we should be using them, but we were not, so now we are doing the tests of those cases, and yes, definitely. You should see the execution plan and immediately you notice the difference. And in terms of execution, especially, and combined with the other features, you really see an improvement. Being more stable. Stable, meaning the same time, the execution time and the resources usage is quite similar, it doesn’t matter if the server is idle or the server is pretty busy. Which is really good at the end of the day.

Carlos:             Sure. Now, one of the questions that I had was in terms of the Intelligent Query Processing, cause at least it seems like it’s built on top of the Query Store. Do those two things have go hand in hand?

Javier:             Well, I believe that you are talking about another feature. Intelligent Query Processing doesn’t rely on Query Store.

Carlos:             On Query Store, okay, so it’s actually separate. So, when it is actually just looking at the plan cache–

Javier:             That was Automatic Query Tuning back in 2017, that in SQL Server 2016, Query Store was released, which was good, so you can manually set the execution plan for the particular process up for any recent changes. You can manually adjust it and set it, or force it, how they call it. Then in 2017, there was an additional layer at the top of Query Store that automatically, if it detects one of these degradations of the query plan, it forces it to use it without any DBA intervention, which is really, really good. I mean, I’m using that feature a lot. But this is like in the same family, and also the same set of enhancements in where Microsoft is focusing, which is execution plans more stable, so it’s focusing on that. One of the ones that we really notice a big, big improvement is all the ones related to memory grant feedback. Initially when SQL Server combines the first execution plan and then you execute it, then your plan could take in memory, maybe too little, and then when it gets executed, it requires more. But since it has the plan already cached, it uses tempDB to steal and get the results. Or, if it gets a lot of memory, but then at the end it keeps using just a little bit, so the opposite case, maybe that doesn’t hurt a lot, if you are running only one. But, if you are running that process maybe a thousand times concurrently, you’re going hurt not only those sessions, you going to hurt the whole server, the whole world. So, now, with this memory grant feedback under Intelligent Query Processing, the first time that the query gets executed and gets compiled, it gets an execution plan. And then the second time right, it says, “hey, I got too low memory from the initial one,” so it gets adjusted on the fly. And also, the other way around; if the initial one got or reserved too much, and then it used just 10% or nothing, in the additional execution it’s going to be adjusted on the fly. Which is really good, especially, again, if you are considering a busy environment. Maybe if you have your laptop or a test server, maybe it’s not a big deal. But on production and really busy environments is really when you see those features, saying, “ah, this is really good.”

Carlos:             No, I have a Dynamics environment that would absolutely love that because we do have memory grants that the queries end up requesting these huge memory grants and it’s just like, “oh gosh.” And so, you have to go in there and start playing whack-a-mole because that’s the only way you can do it now, cause the whole server, like you said, gets affected.

Javier:             And enabling all these feature is really easy, because the only thing you have to do is just change the database compatibility level to the latest one, to 150.

Carlos:             So then, yeah, you talked a little bit about overhead. So, again, I’m going back to the Query Store, but in terms of where all that data get stored, does it live in like the master database?

Javier:             In the plan cache. No, no, no, it’s the plan cache. But now, the query engine has this new possibility of adjusting, but it’s still the plan cache. Nothing has changed.

Carlos:             Oh, so it all just lives– so, they’re just sticking extra stuff in the plan cache, then.

Javier:             Yeah, I mean forget about Query Store for a minute. This is not saving all the execution plans; it’s just adjusting the current one or the one that is in use.

Carlos:             Gotcha.

Javier:             So, it’s a compliment of Query Store. They are on different roads, but they are under the same umbrella. And finally, the last one that I would like to talk about this Intelligent Query Processing. It’s one that is called Approximate Count Distinct, and this is really useful for maybe big data environments. Let’s say that you have to produce a report for Power BI or something like that, and you have a table with, let’s say, 900 million rows. And then you have to do a SELECT COUNT(DISTINCT) of a particular column, just to see how many distinct values you have in a column in a table with 900 million rows. It doesn’t matter which kind of indexing you do, that’s going to take a lot. With this new approach of Approximate Count Distinct, which is a function, it’s a different function, you get the value in a fraction of time, let’s say, in one fourth of the time, plus or minus. But there is a payback. There is an error of the value that you get that is– I mean Microsoft said that it’s not more than plus or minus 3%. So you’re not going to get the exact count distinct that you have in this particular column, but you’re going to get it fast. For some cases, like for example, filling up a pipe in a chart of Power BI to 3% of variation in the value, it’s not going to move the needle at the end of the day, and you’re going to get that result more quickly. So, again, it’s not for everything, but it’s now available. And we are using it, as I said, especially for reporting purposes. When we try to get those count distinct faster than before, before we used to get some crazy things like trying to cache those values beforehand and then from Power BI or Reporting Services or whatever, just go and read those caches but this was not exactly. Now we just do it on the fly with this Approximate Count Distinct and we get the values, again with a little error, but really fast. And they’re keeping improving all this. This is what Microsoft said, “this is V1, V2 of Intelligent Query Processing and Adaptive Query Processing,” and so, in the future they will keep investing in this thing. So, this is Intelligent Query Processing. In terms of performance, definitely it’s where Microsoft put all the investment.

Carlos:             Sure. So, it’ll be interesting to see, where that goes and the adoption there. I think back to– it’s not quite the same thing, but I think about column store index. And so, for as cool as that was, and all the compression and whatnot, from an adoption perspective it doesn’t sound like it really got– I mean, it’s not to say that people aren’t using it, but and widely as maybe they were hoping, it sounds like it wasn’t quite as successful, if you will.

Javier:             Yeah, yeah, I mean, there are many cases like that.

Carlos:             Sure. I wonder if making that a little bit more approachable–

Javier:             Well, this is quite easy to implement. So you have the compatibility level for your use of that device in the latest one and these features are there for you. Of course, you have ways to disable it and go back to the legacy way right. But at least it’s available, which is good.

Carlos:             Sure. So in terms of, from a migration perspective, so I know that we have the tools that help us understand for example, data types is the big one. “Hey, these data types are deprecated, you need to change them, you know, functions,” although we don’t see that as much anymore. But in terms of being able to take advantage of some of these things, I guess you said, “oh, just turn it on.” I guess maybe my question is how would someone know that they were able to make use of it? Or do they just have to turn it on and see? Does that make sense?

Javier:             Yeah, yeah, yeah. Yesterday I was listening to the recording that we did for Managed Instance, and at one point I said something that I will repeat today. Test. This is the big thing. I mean, you have your process. You run it from Management Studio, and you try to get the execution plan. You run it once and you see all those yellow exclamation marks with too much memory or spill to TempDB or whatever. You run it the second time, and you won’t see those. And this is because this Intelligent Query Processing starts using or adjusting the plan. Also, you go to the properties of the execution plan and you see one that is ‘memory has been adjusted’ or something like that. I don’t remember the exact message, but definitely there are multiple ways of say, “oh, I’m being helped by this feature or that one.” So, yeah, there are ways. Yeah, and more than one. I remember there is a new set of DMVs and where you can get also these, but more importantly from Management Studio execution plan, you’re going to see it right away.

Carlos:             Gotcha. Okay.

Javier:             Okay, then we have the other feature, which is really, really cool called Accelerated Database Recovery. ADR. When you have a long-running transaction that for some reason it gets killed or it’s aborted or something, it has to do the rollback. So, for a long time you won’t be able to access the tables that were involved in that transaction. Or even worse, if you were running a transaction for hours, and the server crashed and when it comes back online, the whole database gets into this recovery pending state for a long, long time so the database is not available for none of the users. So, with this feature, Accelerated Database Recovery, just enabling it, which is really easy, it’s an altered database statement. You enable ADR and the mechanism of how the transaction log works just changes completely, how the checked ones are being done, etc, etc. The technical explanation a little bit more complex, but the end-result is that you get the rollback process in a tenth of the time than what it used to be. Also, there are several demos that you can see even that for certain types of big inserts it’s also even faster. One of the questions that I asked Microsoft when I had the opportunity is, “hey, this feature is really, really cool. Why don’t you enable it by default?” because by default it’s off. You have to go and enable it database per database, if you want to use it. And they have this idea of having everything as it was before so as not to break what you are migrating from.

Carlos:             That’s right, backwards compatibility is always the answer. How come we don’t do this? Backwards compatibility.

Javier:             Yeah, yeah, yeah. So, this feature is really, really cool. Maybe not for all the databases, but in the one in where you have long-running transactions, some sort of reportings or data warehouse, ETS, where you park the data–

Carlos:             Yeah, I can see that in the data warehouse, cause those tend to be big and long–

Javier:             Big, long-running transactions, yeah, yeah.

Carlos:             That’s right. I mean, so index rebuilds would be another piece, but when you have the long transactions and then your log ends up increasing.

Javier:             Yeah, we’re going to talk about the index management in a little bit, yeah.

Carlos:             Okay, but yeah, but that idea of being able to, I don’t know if subset is the right word, but you know siphon off those transactions so that if it changes the way the log works, then all of a sudden, you might have a different experience, and even from a backup prospective then those things can change or be a little easier to work around in the event that you do have an issue.

Javier:             No, I mean definitely it’s one of the features that we all like and I have it in my environment, in several key databases and I really notice improvement, because you save a ton of time. Jumping to the following feature, the feature name is In-memory TempDB. This is maybe another of the features that it was really huge, but the adoption is not exactly what they expect. At least, this is what I think. But in this case, they port this technology for the TempDB. So, there are certain types of objects, actually it’s not the data, it’s the metadata of those temporary objects, that instead of going to the TempDB on disc, it goes into memory. So, for example, let’s say that you have a case in where your procedures are basically creating and dropping temporary tables, sharp tables, constantly. If you go and check your system you will see a lot of blockings, basically, because of they are all trying to access the TempDB at the same time. With this, right, the object definition of the metadata of those temp tables, they will go to an in-memory space so end-result, you will be able to execute your stuff quickly, because it’s not going to be blocked. Again, it’s not the data that will go, from those temp tables, it’s the metadata, the table definition, if you will.

Carlos:             Is hashing operations included in that? I mean, cause you mention metadata, but I think hashing, that’s actually–

Javier:             No, no,  it’s object definition, no.

Carlos:             Okay, just object definition stuff, okay.

Javier:             Yeah, yeah, yeah. Again, for certain cases it’s really useful. I spoke with the PM’s and this is the V1 of the feature. They will really invest in the future. But actually, this is a good starting point. There are several benefits that you can get from this. This is something that you have to enable it at instance level. By default, again, it’s disabled, so you enable it, you have to restart the instance, and then the tempdb gets recreated with this feature on. And again, everything related to tempdb for certain times of operation will use the in-memory technology.

Carlos:             Does that mean that I have another– so I guess when I think about in-memory, I generally think about the table. I create the special table; that table is now in-memory. Well, in the terms of the database, I’m turning the feature on, does that mean that one of the files is now “in- memory” or is that like all internal? SQL Server’s taking care of all that for me, I still have my multiple TempDB files but internally it’s going to push some of those up into memory?

Javier:             Yeah, for in-memory technology, if you want to do it for a user database, you have to create this special file group with the special files, etc, etc, just to enable it. For the TempDB, you just enable the feature and that’s it. You don’t have to modify your application at all. So, everything is being handled internally. It’s a good thing. So, again, for certain workloads, you’re going to see the improvement.

Carlos:             And then I’m assuming that there are DMV’s and whatnot to then help me monitor all of that.

Javier:             Yes, yeah, yeah. Definitely, definitely.

Carlos:             Very cool.

Javier:             Okay, also, within the performance umbrella, there is something that maybe is not very noticeable, but to me it’s really important. There are several improvements in the setup process. Remember, when we all were junior DBAs or DBA by accident, we got the SQL media and someone said, “please install SQL Server,” and the only thing that we used to do is just hit next, next, next, next. Then we put our production workload and after a week or so, the manager was complaining, “hey, this is really slow,” and that was because we were putting TempDB, user databases, OS, everything on the same disc without changing the max degree of parallelism using the whole server memory, etc, etc, etc. Microsoft with SQL Server 2016 started with this approach with a TempDB set up through the installation process then there were some improvements in 2017. And now in 2019, the setup process detects and suggests to you or to the one who’s installing, the right max degree of parallelism for that instance and also the max memory settings. So, it’s going to take the total amount of memory and it will do some sort of math operations and will just set a certain percentage of that memory. I mean, it’s not going to do it without your approval. You can keep the defaults as they were before: MAXDOP 0 and memory, this huge number that is going to eat the whole server memory. But initially it gives these recommendations to you, so you can change them directly in the setup process. Really cool and really important, especially for those ones who, like me, many years ago, were it’s just hitting ‘next’ and installing production environment.

Carlos:             Sure, okay, very cool. So,, I know that with all the features and some of these, I know that we’re going to circle back to in future episodes. I feel like it’s been a while, actually, since we had a project manager on from Microsoft, so probably time to circle back to some of these. And I know that we probably could keep going, but I guess is there a last feature you want to leave us with, and then we’ll have to leave it there?

Javier:             Yeah, well, in terms of the security, I just can enumerate the feature, like Always Encrypted with secure enclaves, the modern hardware using the enclaves. Data classification and auditing, important especially for this GDPR regulation, to understand what kind of data you are dealing with. And for the mission-critical availability, resumable online index creation. Remember in 2017, you were able to do index rebuild online and resumable. Now in 2019, you can create new indexes with this resumable option. For the developers, SQL graphs enhancements, a ton of them. Also, enhancement in the machine learning services. And now for the external languages, remember R in 2016, addition of Python in 2017 and now in 2019 it’s Java.

Carlos:             Java.

Javier:             And the final thing that I would like to say, especially this is really, really huge, the tooling. Azure Data Studio, maybe not for the DBA who has to do fine-tuning and get those execution plans in tune, blah blah blah. But Azure Data Studio is huge. If the audience, they never heard about it, please do it. This concept of notebooks is really great in where you can encapsulate your whole set of scripts in only one notebook and then share it with everybody, including the results. It’s really, really, really good. So those are the major features that I wanted to talk about. We went deeply into the ones related to performance, the ones that I like the most.

Carlos:             Yeah, now so I’m curious, so I know that there’s been talk about notebooks and admittedly I haven’t migrated to Azure Data Studio. I played with in the beginning, it wasn’t quite there, and then I haven’t found a need to go back, frankly.

Javier:             You should. You should give it another try.

Carlos:             Okay, yeah.

Javier:             Definitely, definitely.

Javier:             This is a product that is evolving constantly.

Carlos:             Sure. And that’s true. It’s keeping up with almost like that Power BI cycle. Every month they have a new update. So, the concept of notebooks, so the idea of notebooks in, dare I say, other languages meaning that I can have multiple scripts and only execute little pieces of that?

Javier:             And combining multiple languages, which is also good, in the same notebook. This is great.

Carlos:             Ah, okay, that’s a piece of it, then.

Javier:             Yeah, for example, you can do Python, you can do R, you can do a bunch of things. But in my case, I combine a lot, Powershell and T-SQL in the same notebook. And then, for example, if you would like to prepare like a training, you can prepare one notebook with the T-SQL code, the expected result, and explanation with a rich text. You can write like a presentation, also put in graphics, pictures, etc, and then you just save that, and you distribute that. It’s nice, it’s beautiful. Again, not for everything. Not for DBAs, or maybe yes, but Microsoft, they have both have been alive. Actually, you can do calls from Management Studio to Azure Data Studio and the other way around. So, it’s really good to play with a little bit. It’s not exactly a SQL Server 2019 feature, because you can handle it with big data clusters, you can handle SQL in Azure in the past offering, and you can also handle SQL Server, any version. This is more tooling. But yeah, this is something to really keep in mind, Azure Data Studio.

Carlos:             Gotcha. Yeah, that is cool. One of the thoughts that I had, when you talk about sharing, I’m like, “well Management Studio has a project and I can share, I can create that.”

Javier:             Yeah, yeah, but it’s not exactly the same because  you want to distribute it with the expected result.

Carlos:             No, no, that’s right. So, it’s just code, it only has one language, that kind of thing. So, it can’t, you know, expected results, although I suppose I could paste a picture in there. It’s not exactly the reason to use that project, but yeah, that is interesting, particularly from a presentation perspective. I know I’ve heard folks talk about it. I don’t know that I’ve seen it in use yet, but maybe I just need to get to a few more presentations.

Javier:             No, I mean, and there is like a marketplace in where you can download extensions, you can, for example, create to a Postgres database directly from Azure Data Studio by downloading the proper plugin or extension, how they call it? Again, it’s really, really good.

Carlos:             Right. Okay, it’s been a while, but should we do SQL Family?

Javier:             Yes, yes, yes.

Carlos:             Here we go.

Javier:             Here we go.

Carlos:             So, because, again, long time guest of the show, we have a few new questions here for you.

Javier:             Yeah, yeah, I saw that you have changed it since, and well, those are good.

Carlos:             That’s right. So, where is your hometown?

Javier:             Well, my hometown is where I live today. It’s here in Argentina: General Pacheco, Buenos Aires Province. I was born a few kilometers away in a different town, San Fernando, but when I was three, I moved here with my family. Then I was away a couple of years in the US and a few other towns, but then 10 years back, I came back to my hometown and I really love and enjoy this place. It’s not a place with plenty of buildings and it’s not like the capital center, which is 30 kilometers away from here, but it’s really nice. You have a lot of rivers, lakes, trees. It’s nice, it’s nice.

Carlos:             Very good. Now, what’s your go-to order at your favorite hometown restaurant?

Javier:             Yeah, this answer hasn’t changed much from the previous time. Argentina, it’s a synonym of meat. So, here, when we meet together with friends and family, we just get some meat and we put it into the barbecue, the real barbecue, with the charcoal. So, yes, we love the barbecue. We call it down here, asado. And the second option that I may have if you are in a rush or something, it’s pizza.

Carlos:             Pizza, yes. So, in the northern part of Buenos Aires Providence. So those of you, compañeros who don’t know, I spent two years down in Buenos Aires in the Provincia, a little further south than where Javier is. But one of the biggest changes for me in terms of pizza was green olives on the pizza.

Javier:             Oh, yeah.

Carlos:             Do you like the green olives on the pizza?

Javier:             And the black olives.

Carlos:             The black olives.

Javier:             Possibly yeah.

Carlos:             Yeah.

Javier:             Any color, actually.

Carlos:             Yeah, so me, not being an olive fan, that was always a– you ask for a cheese pizza in Argentina, you’re going to get olives on it, which I always thought was– Okay, so what profession other than your own would you like to attempt?

Javier:             I was thinking a lot and maybe I want to be a doctor, like a surgery doctor. Sometimes I make– I used to make the comparison of a doctor is kind of the same of a person who is fixing a computer but with more responsibility.

Carlos:             Yeah, the outcome is a little bit more important.

Javier:             Yeah, yeah, yeah. But it’s really interesting to know how the body and the human being works inside. It’s great and being able to help in the health is really good, so definitely a doctor is the answer.

Carlos:             Very good. Okay, so you have your room, your desk, and your car. Which would you clean first?

Javier:             Well, room and desk go side-by-side, especially my office. I love to have it clean and especially my desk. Sometimes having young kids is complicated because they come to your office and they leave things that they have to play and say, “Come on, here. I am working and you give me this Paw Patrol or Beauty”. And say, “Come on.”

Carlos:             Whatever else.

Javier:             And then I like also to have my car clean, but I prefer first, the desk and the room.

Carlos:             There we go. Awesome. Well Javier, thank you so much for being on the program today. We do appreciate it.

Javier:             Thank you so much for the invitation and I really love to talk to you guys, so anytime.

Carlos:             Yeah, now you’ve given us some ideas of things that we need to get a little bit deeper into, as adoption of 2019 kicks up a little bit more. I think this year, folks might have migrations on their mind, so it gives us a couple more things to talk about.

Javier:             Yeah, down here in Argentina, definitely we are doing with the SQL Family, we have the group here, SQL Argentina, it’s one of the hot topics we are going around. We are trying to expand now, to go a little bit further to go and talk about this. And a topic that everybody in the community really wants to listen to and learn is SQL Server 2019.

Carlos:             Right. Very good. Well, compañeros, I think that’s going to do it for today’s episode. As always, you can reach out to us on social media. Javier how can folks get ahold of you?

Javier:             My Twitter username is Javier_Vill. The first four letters of my last name, Villegas. And then on LinkedIn, you go type my name and I’m going to pop up at the top. And finally, I have a YouTube channel: you type Javier Villegas SQL or Javier Villegas DBA and you’re going to get all the videos that I upload to YouTube.

Carlos:             Very nice. Eugene, you still with us buddy?

Eugene:           I’m still here, yes. I’m still awake. You can find me on Twitter @sqlgene and sqlgene.com.

Carlos:             Kevin.

Kevin:              If you run APPROX_COUNT_DISTINCT exactly 31 times on your largest facts table, plot the variants, convert those values to ASCII, that’s how you’ll get my handle.

Carlos:             And compañeros, you can reach out to me on LinkedIn. I am at Carlos L Chacon. Thanks again for tuning in, and compañeros, if you have connection to folks in the printing industry, or in healthcare, particularly those who are running the Centricity EHR application, I’ll invite you to send them my way and we’ll see if we can’t help them. Thanks again, compañeros, and we’ll see you on the SQL Trail.

Leave a Reply

Back to top