Crash Course on Better SQL Development

Audio podcast:

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS
Leave us a review in iTunes

Target Audience:

All developers who write T-SQL code and aim to improve performance and discover free tools and resources.

Abstract:

SQL Development is a broad subject with many roads to choose from. Often times SQL Developers feel overwhelmed and unsure which areas they need to focus on. The purpose of this session is to give a high-level overview and present paths for further study and growth. Subjects reviewed in this session will include coding patterns, error handling, and execution plans. Demonstrations of free tools will also help developers find ways of simplifying their daily tasks. Attendees will leave this session empowered with knowledge and directions for further growth.

Duration: 75 Minutes

Why I Want to Present This Session:

  • This topic is my passion and my daily work.
  • I want to give back to community knowledge that I learned from my senior peers.

Slide Deck:

Additional Resources:

My Technical Blog

Zip file with presentation and demos on OneDrive: Crash Course Presentation

Session Transcript:

Brent Ozar: Vladimir Oselsky is going to be talking about a crash course on better SQL development, so take it away Vladimir.

Vladimir Oselsky: Well, thanks so much for inviting me and having me here, so my name is Vladimir Oselsky. At least that’s how people pronounce it in America. Back home it would be something like Vladimir Oselsky, which probably nobody can even understand. Currently senior software developer at the small software company. Yes, I was born and raised in Siberia since a few people are asking about it, and it does get cold there, but few people don’t realize it can get very hard. One of the places I lived it used to get above a hundred degrees, high altitude mountains, in the winter minus 60, somewhere over a hundred.

I have a bachelor’s degree in Software Engineering Technology, so I know some .NET and somehow got into SQL. Alright, that’s enough about me, let’s jump into the agenda. So wrote better SQL development, how to become better SQL developer? And that’s something I had to work through few years ago, trying to understand the platform because I came out of school with a bachelor’s degree in Programming but I still didn’t understand SQL Server. I might have wrote a few queries but that wasn’t enough to actually have a job. Then I started learning about all the different tools and then I start learning from other people on my team members, and most of them were senior Oracle people that were in the business for almost 30 years and they could write SQL with their eyes closed. One phrase that I learned not that long ago, and I don’t know who has it copyrighted, so apologies, apologies, it depends.

I learned that in SQL Server, you can’t always apply the same solution to all your problems, and that was one of my initial problems that I was doing myself. I was trying to use the same approach in all the different scenarios. So then we’re going to jump into T-SQL code and we’re also going to talk about this amazing command – oh my Zoomit’s not working, let me start my Zoomit. It doesn’t start automatically…

Brent Ozar: I thought for sure you were going to show truncate table.

Vladimir Oselsky: No. Alright, this one actually belongs to Brent Ozar, but I think I’m in a better position to say it. It’s DBCC cp because [crosstalk] database [inaudible] consistency.

Brent Ozar: I love it.

Vladimir Oselsky: Well, that’s enough with the fun and games, so when it comes to T-SQL coding, like I said, there are so many different ways to solve a problem, like sometimes you need to use a CTE, sometimes you use derive table sub-queries, sometimes table variables, temp tables, but you can’t always apply it to the same scenario. And then when it comes to stored procedure design, there are so many different ways people write stored procedures, getting the same results yet some of them are faster than others.

When it comes to cursors, like I said, one of my first jobs was working with Oracle people and they loved cursors. I wrote a cursor every day on the job and then I came to SQL Server and I was told never to use a cursor. I wasn’t explained why, I was just told not to use a cursor. So we’ll look a little bit into it, why a cursor is not a curse. Talk a little bit about error handling, execution plans, and some tools. So let’s go to next slide. When it comes to understanding the platform, back when I first started with SQL Server, SQL Server 2008 R2 was just coming out and they were adding all the geography, geometry data types, adding user defined table types, CLR and other functionality.

I wanted to learn all about it and then I realized that I don’t have time to learn everything there is about SQL Server. I found small niche that I can focus on and get better instead of trying to learn everything. But with all the different versions of SQL Server available, you need to understand what’s different between them and what each version can do better. Because sometimes – well, one of the things we’ll talk about, for example, RAISERROR versus THROW, and in SQL Server, THROW became available in SQL Server 2012, if I’m not mistaken. Trying to work in SQL Server 2008 R2 or prior can’t use that, so have to change the code and have to change how I’m handling stored procedures. And then we’ll talk about tools because when I was working with Oracle, there was pretty much the only one tool, Toad. Everybody worked with Toad and a few people used the SQL developer. When I came to SQL Server, everybody’s using SSMS. And then finally, Microsoft two years ago announced SSDT so now there’s more integration with Visual Studio and I’m actually going to be doing all the demos in the new SSMS versions 14, I believe it is now, which looks even more like a Visual Studio. And then there’s also the little program called LINQPad, I’ll demonstrate why I actually like using it.

When it comes to SQL tuning, we have to look at execution plan. I know there are some people that are able to do the query tuning strictly by looking at statistics and execution times, but I’m a visual person. I like to see what I’m working on, and by seeing that visual plan in SSMS, it just doesn’t work and I’ll show how much better it is in the Plan Explorer. And this is not a presentation trying to sell Plan Explorer because believe me, I can’t sell it, it’s free and I’m not a spokesperson for SentryOne and there’s just – won’t be enough time to even cover Plan Explorer even the smallest bit. We’re just going to look at it briefly.

So now let’s just jump straight into demo. Slides are okay but demos is where it is. Can you guys see my screen alright? Alright, so CTEs, if I’m not mistaken, they were introduced in SQL Server 2005 or so, and one of the first ways I started using them was trying to combine queries. So instead of writing subqueries and having this long mass that is hard to read and understand, I would just take the code and write it as a CTE. Because now I define it, I know what I’m selecting, what I’m working with, I can define multiple things and it’s all pretty. Everything has a place and once I go to joiner to it, it’s very simple, there’s no extra code to look at.

So I started using CTEs everywhere until I went to SQL Saturdays and I heard that CTEs could be bad.

So why can CTEs be bad? So let’s look at CTE, code runs fine, everything is good, we’ll look at the execution plan and right away we see a problem with execution plan in SSMS. There’s just not enough real estate to look at everything that you’re seeing, and most of the time you’re not going to be looking at the simple plan like this where there’s only one query. When you’re debugging stored procedures, you’re going to be looking at maybe a hundred different queries and you have the scroll bar and you’re constantly trying to find that one thing that you want to work on.

So CTEs, why can they be bad? Well, one thing I didn’t know, CTEs underneath the hood actually treated as views in SQL Server. So SQL Server will create a view and then select from it, so one of the problems that exist with the views if you nest them deep enough, SQL Server is going to lose all statistics into objects that you’re selecting from. So in that sense, you will only be able to – when you look at estimated row count, you will only see one row coming back from that CTE. So if you nest them like you do view, you’re going to screw up the execution plan and you won’t be able to – and you will get a bad execution plan because SQL Server is estimating one row versus how many are actually coming from that.

So what do you do if you need to nest it? Well, you write a sub-query because no matter how far you nest a sub-query, SQL Server is going to unwrap it for you before executing it, so there will still be statistics associated with it. So temp tables – we’ll show that in Plan Explorer. Temp tables, I love temp tables because one of the easiest things I’ve found, I’ll just select whatever I want and then I’ll just throw it into temp table because once in a while, I’m just a lazy programmer who doesn’t want to spend their time trying to figure out how to create the table, which column it’s going to be. So I’m just going to throw a select into statement and SQL Server is going to create all the columns for me, and then I’m going to select from it.

So let’s execute it. Forgetting my execution plan – so if we look at the execution plan, and we find one of the select statements from the temp table, we’ll see that estimated row count actually corresponds to the actual row count. So there is more things you can do with the temp table than you can with table variables, because with the temp table you can actually create indexes on it, they have primary keys, you can have non-clustered indexes, whereas table variable would only have a – you can have a primary key on table variable. But there is something else that comes with the table variables.

So now Plan Explorer, so I run the same command, and instead of using temp tables, I’m running to table variables and doing the same select. So query almost identical but if we look at execution plan, query is very different. Looking at select from table variable, we have estimated row count one, so then being a smart programmer, I decided well, I’ll just use table variables for very small data sets and then use temp tables for large data sets.

Well, going back to SQL Saturdays, I learned there’s actually a way to enable table variables to be a little smarter. And I don’t remember exactly who was talking about it but there is a trace flag, 2453, and it produces something interesting. So exactly same query running with the different trace flag, we have a different execution plan. Which, if we go back to temp table, it looks very much like the execution plan that came from a temp table. And if we look at what’s going on here, we’ll actually find that estimated row count and the actual row count are identical even though just before, selecting from that same table variable, we’re only getting one estimated row versus the whole everything.

So do your research, don’t go to your production system and start turning on trace flags, this is not what this session is about, this session is about different possibilities that you have to approach a problem. So now, this trace flag will enable some statistics in table variables and you will be able to get much better execution plan instead of just one row.

So what about running stored procedures? When we write stored procedures, normally our stored procedures can be very simple. We just have a parameter and have some work and return some data. But what happens when we want to get a whole list of parameters? Well, we concatenate strings, and they’re in the front end, and then we want to split those strings in the back end. That puts extra load on the SQL Server, and unless you’re using SQL Server 2016, you don’t have a string split built-in function for it. So you’re either going to write a CLR or do something else to split those strings. Well, what’s the alternative? Well, the alternative is using table variables.

So how do you create table variables? Well, very easy. You create a type, give it a name, last table, define your columns, and you can even specify there’s a primary key. So in my case, I just want to demonstrate just passing a single column to a stored procedure; so how does it actually look on the front end? I know not many of you might be .NET developers but when working with a .NET code and calling SQL stored procedures to pass the table, it’s called SQL data type structured, and you’re just passing a whole object to it.

So now instead of somebody who’s a front end developer having to do extra work of taking that data table that they had and converting it to comma separated string for you, they just take that whole IEnumberable structure and they’ll pass it to you.

So how does it look on the back end? So once we pass the whole data table to stored procedure, since it acts identical to table variable, we’ll just join to it. So now I don’t have to split my strings, worry about anything else, I’m just joining to it, and now I can make my stored procedure smarter and if I add option recompile or do something else to actually enable statistics into it, because just like the problem exists with table variables, there’s probably not going to be any statistics with it.

So let’s look what happens when you actually call it. Let’s clear the cache, make sure my flag is turned off, execute the stored procedure but only pass a single item to it. And now we have to find where it’s being selected and estimated rows, one. So we’ll – well, we passed one item and we’re getting one row, so I think we’re in a good scenario.

What happens if we’re trying to pass a hundred items? So let’s execute it, we’re running a hundred items to list and passing that whole list to stored procedure, we’re only getting 17 items back, but what happens in execution plan? We’re still getting the same number of rows, we’re still only getting one row. So I had – well, I guess table variables always estimates one row. It’s apparently not; so let’s free proc cache, around our hundred rows, go back, find it… Estimated number of rows, still one. Alright, now we execute this again and this doesn’t work all the time but what actually happens with SQL Server when it’s using table variables, it will store the number of rows during the first execution. So if your first execution was with a single row, you will get one row estimated for every subsequent execution.

Then the trace flag that we talked about earlier that affects table variables, it also affects table valid parameters. And in this case, we’re still getting one row; so something is still cached. Let’s try this again, and we’re still getting one row. Alright, I must be doing something wrong in here. That’s what happens when you’re trying to work with something that doesn’t generate exactly same result every time.

Brent Ozar: Oh well.

Vladimir Oselsky: So this trace flag will affect table valid parameters but be careful when working with it and make sure it’s exactly what you need and it’s not going to produce additional problems for you. So cursors, why is cursor such a big problem in SQL Server? And I’m not going to mention him because he might actually be listening, but this guy who shall be left unnamed, wrote this amazing blog post on the different options that you can associate – that you can include on cursors and what they do. See, when coming from Oracle, I knew that Oracle implementation, of course, was very fast. I didn’t understand why implementation in SQL Server was so bad. So then I learned that when SQL Server was implementing cursors, they created the cursor that can do absolutely everything. Cursor can go backwards, cursor was go forward, can traverse, can skip forward, can skip back. Well, most of the time when we’re running a cursor, all we need is the cursor that goes one row at a time and it’s only going forward. So the way we can define our cursor to do that is by using these two options: local and fast forward.

So read the blog post, I included the link in the slides, it will explain in more details that we have the time for, but fast forward will force the cursor to only be a cursor that can be traversed forward one row at a time instead of doing all this other stuff that we don’t really need the cursor to do. And as you will find from the blog post, that it reduced memory usage, reduces the duration, even reduces pages in tempdb; and all we did was define extra options with cursor. So if you do have to use a cursor, at least now you know that there are options you can include with it to make it go faster. We’re doing good on time.

Error handling – when it comes to error handling, it was the second nature to me as a .NET developer. I learned try-catch blocks pretty much right off the bat, so working with SQL Server, I forgot when the try-catch blocks were introduced but I was glad that they were. So the way I see it, it is a last option for us developers to make the code fail gracefully. A lot of times, we don’t want the code to fail and just commit – some changes to be committed halfway through it. For example, if we’re trying to delete some data or trying to insert it, we want everything to be executed, not just the beginning of it. And then if it fails halfway through it, we want to be able to rollback. One of the easiest way, encapsulate it in a transaction with a try-catch block, we can catch that there and roll it back.

So what we do with that error, well, that’s up to each developer. Sometimes you will just want to print that error message to the screen because you don’t really want anybody else to know that your stored procedure failed, but when you go to execute it, you’ll be able to see that error message.

What about if you want that error message to be an actual error message? Well, you can re-throw it by either catching all the appropriate information with it and then using a RAISERROR or simple in SQL Server 2012 and higher you can just use the THROW command and if you use it from a catch block, it will act as a re-throw. So whatever exception was caught, it will just throw it again.

So let’s look at the execution of it. So we execute a stored procedure that caught an error, nothing really happened, well, because we print, so to see an actual message, we would have to go to the messages tab. So if this is something that’s being caught from a front end and they are not reading back the messages, they won’t even know that your stored procedure failed. They just won’t get the results back. When you execute a stored procedure that actually throws an error, there will be additional information sent to the front end. Or even if you’re running it from SSMS, it will change tabs for you, so you know right off the bat that there is the error and what it is. Plus, you get this nice red text versus just the black text that you get from printing that error message.

So I was talking about difference in THROW versus RAISERROR, and everybody does know that this is RAISERROR…

Brent Ozar: That’s great, I’ve never heard it called that before.

Vladimir Oselsky: You must have never listened to videos from Tobias.

Brent Ozar: No.

Vladimir Oselsky: Yes, he’s like this is RAISERROR, I was like what is he talking about? And he’s like yes, otherwise, it would have been RAISERROR.

Brent Ozar: Nice.

Vladimir Oselsky: So what’s the difference? Well, one with THROW an error, we just have a very simple statement. We just print, throw in error and select from table. Well, we don’t get that select from table. Instead, we get the error message being thrown, so we would expect the same thing with the RAISERROR. Well instead, we get the same error message but we get some additional information. 16 rows affected. We go back to the results and we actually find that the select was still executed, so even though you raised the error, you still got the select statement. Well sometimes, you don’t want your code to continue going if you’re raising an error based on some sort of user condition, you would want it to stop. Instead, in this case, we find that RAISERROR still goes on and you still get that select statement, which in my case it’s not bad but what if it’s a delete statement or something else that you don’t want to do if the error was raised?

So we think, well then RAISERROR is bad and we’re not going to use it. Well, actually not. We’re going to use the same RAISERROR, same select from statement but we’re going to wrap it in a try-catch block. All of a sudden, that select statement is no longer being executed because RAISERROR will act different when it’s inside try-catch block than it will outside of it. So if you have stored procedure that doesn’t have a trap-catch block and you have RAISERROR, the execution will continue. If you have a stored procedure with a begin – with a try-catch block and you have RAISERROR, the execution – it will stop execution and you won’t get that select statement, which is actually very similar scenario to what happens if we just have a generic error message.

So here we’re just doing some simple division by zero, which will give us an error but execution still continues. So I’m not sure why Microsoft decided to do it but with raising the error, you get the same behavior as you would if your code generated an error. But you wrap that same code in the try-catch block and all of a sudden when the error is thrown, it will catch it and the execution will no longer continue. So just something to consider, there is differences when it comes to what SQL Server will do to it.

Alright, so let’s talk about Visual Studio SQL Server data tools. Being a .NET developer, I lived in the Visual Studio. So when I found out that I can actually do my SQL Server development in Visual Studio, I was very excited because I could do all the things in Visual Studio that I learned and loved. So this is not an SSDT presentation, this is just a slight glimpse into what can be done with it. So here, we have a table, stored procedure and one of the biggest difference between writing code in Visual Studio SSDT project versus writing in SSMS, if we already created the stored procedure, we’ll need to write it as an alter statement every subsequent time.

When working on the code in Visual Studio, you always define how it’s going to be. So I never have to write an alter statement because it will actually complain. So it’s always going to be a create stored procedure, never an alter statement. Of course with SQL Server 2016, you can use create if exist I believe…

Brent Ozar: Drop if exist.

Vladimir Oselsky: Yes, drop if exist. So there are some extra possibilities. But right off the bat, using Visual Studio, you write a declarative code so you write the code how you want it to be, not how it is and how we’re going to change it. So we have one simple table, one stored procedure and we have a table call attendees with three columns, ID, first name and surname, and I know if I ever call it surname because maybe we’re running our application that’s going to go worldwide and they don’t always call it last name.

So then we rolled out the application and everybody is happy until the manager comes along and says well, we’re not going to roll out the application worldwide, it’s going to be only for America and we want to call it last name. So as a developer, you would come in and you would change it to last name and it’s all great until you go back to stored procedure, and now your stored procedure doesn’t work because well, it expects a surname but we changed our table to last name. Now we’re back to generating the code, trying to rename all the instances of that stored procedures that we need to do, so there just has to be a better way of doing it.

So with Visual Studio, there are some other capability that you have. One of it is this refactor menu. With the refactor, there’s this little nifty thing called rename, so let’s see what happens if we use the rename. So we’ll give this a new column name, we save it, it says, well, I’m going to change the column name, and we click apply. So looked almost identical to what we did before, instead, when we go to stored procedure now, stored procedure automatically changed because Visual Studio will keep track of dependencies of the objects and when it notices that that object changed, it will actually rename it for you. So just one little feature that – I mean, something that I wished you could do in SSMS, you can do it very easily in Visual Studio. So now instead of trying to find all the stored procedures that you wrote, in every instance when you used that column, you just bring the code into Visual Studio, change it using – you refactor the code and now all of a sudden you get that code changed for you.

So one of the things that you will notice, there’s a file called here refactor log, so what it does, it actually saves whenever columns were changed, and if anybody using Redgate products, this is some of the things it can read and when you’re deploying it, it can generate different code, it will generate a code for you to alter a column name in the table, but it also knows that it needs to generate the code for stored procedures to change it. Well, talking about Redgate, one of the features that we have from Redgate is schema compare. But for those of use that work for companies that don’t want to buy Redgate tools because they don’t feel like their developers are worth spending money on, we’re left to using the tools that are available to us.

One of those is we get a schema compare as part of SSDT and all you have to do is come to tools SQL Server and do a new schema compare. Well, I already set it up and I’m comparing my local project that I’m running in Visual Studio to the local instance database, and you get this nice little graphical interface of what happened. Well, I have more tables than I have in my target, than I have in my project, so we can drop them, we have a change and we know exactly what the change was. So for those of us that don’t have Redgate tools and schema compare, we can use tools that are available to us. So if somebody has Visual Studio as part of a product that they already use, this is some of the things they can do without trying to buy extra tools.

Another thing I saw from Redgate is new – one of their new capabilities with I believe, ready roll, which will probably make SSDT projects almost obsolete, but we’ll see where it goes. We will probably have to spend some time playing with it to see exactly what we can do with this.

Brent Ozar: Dana asks, “Do these features work only in the one database that you’re in or can they look across all the databases on a server to do this?”

Vladimir Oselsky: Well, so with the schema compare, you would have to select specific databases to compare. So you can compare between servers, you can compare databases to databases on the same server, but it’s whatever you have access to. So if you have access to that database, you can see it otherwise you won’t be able to compare it.

Alright, we talked about a tool called LINQPad. And let me start it up… And of course, it goes to the wrong screen. So one of the things that I love about this tiny little tool, it’s very lightweight. I used to use – back when I was running a lot of c# code, so I could write small snippets of code without having to start Visual Studio. I wanted to just write a small code, execute it and just be done with it. Well, I realized that I can do the same thing with SQL statements. So over here, I have my Glenn Berry scripts; that absolute necessity when it comes to troubleshooting performance.

So with all the different scripts you have, for example, where is my sp_who3. Once in a while, somebody calls you and says, hey something is running on the server, we’re not sure what’s going on, so we come in here, and we can just execute it. So it runs really fast and the fact that this – I don’t have to fire up SSMS and sometimes SSMS can crash on me or I have too many windows, or doing something else with it, this is just one of those tools I use to organize my scripts and when they need something to run really quick or to run – it’s like, I didn’t want to run it against this database, I want to run it against different database. So it can be done very easily and again, this tool is free so just by using some free tools, you have more capabilities.

This is not going to replace SSMS and if you come in here you won’t be able to do any of the administrative things you expect SSMS to do like creating schemas for you or doing anything else, this is just a lightweight tool that you can use for querying databases.

Alright, back to Plan Explorer. One of the jobs I had earlier in my career was they gave me a SQL Server and said we want this database to be moved from 2008 to 2012 and make sure to optimize every stored procedure. They had over 200 stored procedure, so now I’m working through every stored procedure trying to performance tune it. So I don’t know how I happened to go find Plan Explorer, but I fell in love with the fact that there’s just – you can click on any individual statement and you’re looking at the plan diagram just for that statement. And if you have multiple screens – give me one second – you can actually use a whole real estate of that screen to see the plan.

So with a small plan, it probably doesn’t really matter but when the plans are bigger – let me find a bigger plan – it becomes much easier to see the bigger picture of it. And the bigger your screen you have, it’s just that much easier than trying to see that scroll bar in SSMS, scrolling back and forth trying to find that one execution that we need.

But I learned about another little thing right off the bat. When it comes to this cost in SSMS, the cost is estimated. So this is just what SQL Server thinks is going to cost you. So how do you find the most offensive query when you execute a stored procedure and it has hundred different statements and you have to go through and look through each one of those statements? Well, in here I can sort by the duration that each statement took and I have this wonderful statement that took 42 seconds, but the estimated cost was only 0.4%.

So if I was going only based off estimated cost, I would never look at this query because you will just won’t expect it to be taking most of your time. So then when it comes to actually figuring out what’s wrong with the plan, well, this one is quite easy. We have index seek with 274 million records coming back.

So very quickly, just visually coming, I was able to identify a problem. Well, if we have 275 million records, so I go look at the base table and the base table only had about 700,000 records. So there was some poorly written code, but when looking at the execution plans in Plan Explorer, let’s say there’s a plan that we don’t really care what it’s doing. We can just come in here and hide it, and then we’re only looking at the part that we care about. We can filter by cost and do many other things that just – you can’t do right off the bat in SSMS.

Another easy way of figuring out where your problem is by doing this cumulative and then we can easily see which lag of the query we actually need to look at and the bigger then queries get, the more we can easily roll out to find the problems that we’re looking for. So let’s change it back to pro note, let’s see line width, or we can change it to data size. So with a single click, just making very, very simple changes and now we’re looking at total data coming back instead of number of rows.

So once again, this tool is free from SentryOne. I’m not a spokesperson from SentryOne, I know a few people that work there and they’ll probably appreciate if you use their product but when it comes to Plan Explorer, you can just go to their website and download it for free. No email address required so they’re not going to spam you with a whole bunch of emails asking you to buy the rest of their products. This is just something that they offer to the community. I believe Aaron Bertrand added a link to your Slack channel, which includes a full webinar on Plan Explorer. There is just so much more that we can talk about when it comes to Plan Explorer that we just won’t have the time. Like this whole index analysis tab, we can probably spend the whole hour just going over what they can show you on index analysis.

But – so this is just one of the tool you use and it’s something that can help you with development. And of course, when working with the big stored procedures, a lot of times we go for this low-hanging fruit where we see most of our time being spent and we can tune it pretty quick. But when the time is spread all against multiple statements, then it becomes a little harder because you have to tune each statement instead of just finding one statement that causing the most offense. That will be it.

Let’s go back to slides that we went over everything, and of course, I didn’t click on the right slide. So tools, so everything – so the Visual Studio, there’s a community edition that you can download completely for free, this is one of the links you can go through. There are some restrictions when it comes to using it in your organization, so you can’t just download it and use for your business but you can use it for personally, and of course SQL Server developer edition.

One of the companies I worked for previously actually had – gave me a MSDN license so it was really easy for me to get all the things that I needed. Without having MSDN license, it becomes a little more complicated when you’re trying to run presentations and you need a SQL Server. So what do you do? Well, you would just download either express edition or evaluation. With SQL Server 2016 develop edition being free, now you can download it – you still have to jump through some hoops to get it and there’s multiple blog posts on multiple ways you can get developer edition downloaded for free.

Time for Q&A, so I believe we actually ran a little quicker than estimated, probably because…

Brent Ozar: You’re doing good, you’re doing good. It’s always – timing online ones is always trickier and a lot of people ran early today too. Samuel says, “Does LINQPad have IntelliSense?”

Vladimir Oselsky: Yes, when it comes to LINQPad, there is option for IntelliSense, but unfortunately it’s a premium feature that you would have to buy. The cost is not that much, but it would just be a little bit extra.

Brent Ozar: Got you. Teddy says, “Did you show that when you right-click on a query plan in Management Studio, it’ll open in SQL Sentry Plan Explorer?” He loves that so much he didn’t…

Vladimir Oselsky: No, I didn’t show it and there’s actually one of the – there is a reason why not to use it, so let me go back to temp tables. I don’t have the plug in on it. If you execute the plan in the SSMS and then you move it to Plan Explorer, some of the data will not be populated. So it’s always better to execute the query out of the Plan Explorer because then you get additional information that they capture while running it. Otherwise you won’t get it. One of the things – I mean, I also like about Plan Explorer, and you were talking about posting to your website, they offer you an option to anonymize your code. So then it’ll take a second – my computer is thinking. It should be one of those buttons that says do not press during…

Brent Ozar: During live presentation.

Vladimir Oselsky: During live presentation. Alright, so now that execution plan no longer has any of your proprietary information. So you will see use database one, variable, column names, so now you can still show the plan XML and you can share that XML through Brent Ozar, Paste the Plan without exposing any of your schema information and also you won’t be exposing the command you’re running. People will still be able to read your command but at least they won’t be able to know what those objects are.

Brent Ozar: If anyone ever sends me a query that looks like that to troubleshoot, I’m going to give you the finger, and it’s not going to be the index finger. Who wants to read that? That’s heinous.

Vladimir Oselsky: Well, at least you can still see the joins and if somebody has a cross join here you can just smack him and say well, fix that cross join before you send me the query. But at least it can be helpful when you’re sharing online and you don’t want to give more information. Plus, they give you an option to post to SQL performance, which one of the places that Aaron Bertrand lives on. At one point I thought Aaron Bertrand was a hive of people because every time I saw somebody posting a question on Stack Overflow, before the question was even posted Aaron Bertrand already had the five-paragraph answer on what was wrong with their SQL statement.

Brent Ozar: Yes, he is really – yes, really quick with that stuff. Well, thank you very much, sir. We really appreciate you taking the time to present today. I want to thank everybody from the audience for handing out with us today, thanks for hanging out on Friday, April the 21st. Great presentation Vladimir and have a great weekend everybody. We really appreciate you hanging out with us this weekend.

Vladimir Oselsky: Alright, thank you so much and if you have any questions, send them to my email address. You can look me us on Twitter, LinkedIn and can check out my blog. It doesn’t have too many things but once in a while I post something just – it’s more about trying to keep my own skills sharp and trying to remember stuff that I solved so that I can find in the future. So thank you so much.

The following two tabs change content below.

Vladimir Oselsky

Senior Software Developer at Civica CMI
Vladimir (Vlady) Oselsky is a Senior Software Developer at Civica CMI. He has been working in IT industry since 2002, with various job roles ranging from Helpdesk to DBA. He has graduated with Bachelor of Science degree in Software Engineering Technology in 2011. Since then he has been working closely with SQL Server.

Latest posts by Vladimir Oselsky (see all)

, ,
Previous Post
Gems to help you troubleshoot query performance
Next Post
We need DataOps – NOW

5 Comments. Leave new

Vladimir presented this to the Columbus PASS chapter recently, and I recommend it. The presentation. The chapter. Both. 🙂

Reply

Hi, and thanks for submitting a session to GroupBy.

Uploading the slide deck really helps because I wasn’t sure about the scope of the session based on the abstract. I’m not clear on whether this is for someone who just started developing, or whether they’re a seasoned SQL developer with several years under their belt.

Check out this post on how to define your attendee:
https://www.brentozar.com/archive/2011/07/how-write-conference-abstract/

I do think your session is a good fit for a LOT of people, but I’d just flesh out the abstract more so people understand if it’s a right fit for them. List some of the specific resources and concepts you’ll cover so that attendees know if they already know these topics, or they’re a surprise.

Reply

I agree with Brent. Your session looks like it will be great. I’m not sure if it is for developers just starting out or for more seasoned developers. I definitely want to attend. I find there is always something I can learn (or re-learn as the case may be!)

Reply
Curtis Gregson
February 11, 2018 8:30 pm

I was looking for the supporting documentation from the link above, but the onedrive link above, but the content is gone. Any chance a Vlad could update the link

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu