SQL Server Development Mistakes

Welcome to the MS SQL Tips webcast common SQL Server development mistakes and how to avoid them. Sponsored by IDERA. I'm Greg, cofounder MS SQL Tips and today's webcast host. Joining me today is John Minor. John is the MS SQL Tips author and is a data architect at bluemetal. He's over 25 years of data processing experience, and his architecture experience encompasses all phases of the software project Lifecycle, and today he will share what he has learned over the years. Now on this slide here, you can see different ways to contact John, so jot those down. After the Webcast, feel free to get in touch with John, check out the articles and MS SQL Tips that he's written, as well as his blog and his Twitter feed as well. Before we get started, if you have any questions, you can submit at any time during the Webcast by answering questions in the question area and your presentation controls, we will try to answer as many questions as possible during today's webcast.

If you're not already a member of the MS SQL Tips community, we invite you to join. You can go to www.msSQLTips.com, sign up for our newsletter, read any of our tips, download white papers, and watch on demand videos. This webcast will be recorded and archived for future playback, and you'll receive a follow up email with links after today's event. John, can you go to Slide too, please?

Rapid msSQLTips

As mentioned, today's webcast is sponsored by Edera, the makers of Rapid SQL. Rapid SQL allows you to build the best SQL code for several database platforms, allows you to collaborate with the entire development team, the built in SQL debugger Simplifies binding, coding errors. You can also quickly construct code with tools that allow you to find and fix code issues in real time syntax validation, code search features, and more. After today's webcast, please visit their website at the URL listed on the screen. Or you can just simply go to www.idarer.com. At this time, I'm going to turn things over to John and he can get started.

Thanks again, Greg. My name is John Minor, and I am a data architect at Blue Metal. Again, I'd like to thank first Greg and Jeremy for allowing me to present today. Also thank you to Idear for sponsoring this event. Last but not least, my passions. I've been doing SQL for about ten years plus very active in it. I love PowerShell, Power, Bi and starting to do a lot of data science. That's why I like doing at the very beginning you saw a little icon, it's a crafty DBA, so I like doing woodworking, crafting beers and playing chess. Enough about me. I like to talk about the presentation purpose today. I want to do a highlevel introduction of transaction SQL statements that might have performance or maintenance issues.

SQL Server Performance Tuning

SQL Server database administrators should be subject matter experts. If you see your developers falling into any of these traps. You want to help them to avoid the trap, if not rewrite the code so that's better and faster. At every presentation I do, I like to give a syllabus of what I want to talk about. Today I'm going to talk about commoning. Common is very important. I've seen many pieces of code out there that are in the wild, should we say wild west? And they don't have comments. You really don't know what's going on. Curses. A lot of people curses. They say this, well, we're going to talk about what they're good for and examples of how they might be having performance issues. Air handling. There's two schools of error handling. There's old school and new school. We'll talk about them and where you might see them.

Functions. Functions are typically going to be slow and we'll talk about why a scalar function might be okay, but a multiline function or table value function might be slow. Isolation levels. That is the acid part of the database. Atomic, consistent, independent, and durable. Okay, so we're going to talk about the different isolation levels, the default levels for each database, including on premise in the cloud transactions. How do you make sure that insert into the customer table is followed? Right, by insert into the count table. We'll talk about how you can do that. This is an indexes very important topic. We can spend a whole hour just talking about indexing. I'm just going to gloss over a couple of ideas that you should know. And last but not least, temporary objects. Not everything is free in life. I just want to tell you that even though it's temporary, it's going to exist somewhere and it may cause you problems.

Commenting

Let's talk about commenting. A single line comment is represented by two hyphens, right? A text after that comment is indicated is ignored. A multiline comment starts with a slash star and ends with a star slash. The main thing at the bottom is all data that's sent to SQL Server engine is sent over the tabular data stream to the algebraic processor. White space is used to increase readability, but in most cases not required. So let's do a demo. I'm starting up the newest version of SQL Server Management studio, and I'm connecting to my on premise SQL Server 2016 database.

Create Database

First example, we're going to use the master database. We're going to create a database called Banking, and we're just going to specify some sizes. So we're going to execute this. This works fine, but you can see that the readability is not there. You really can't know why the purpose is. You don't see it formatted correctly. Let's take a look at the same example, but adding formatting. In this example we can see that formatting adds readability.

For instance, at the very top we have a comment header. We can do a name, say an author, a date of purpose. Okay, we're going to do the same thing. We're going to create the banking database in case I'm going to hit refresh, going to find the database, hit properties, and we're going to stop this from scratch again. Oops, wrong click. Right and task.

There we go. Sorry. Okay, so it's gone. We're going to recreate it from scratch. This one, if we execute it and we hit refresh okay, you'll see that we recreated a banking database. One thing you might not know is that there's something called extended property, which is part of the database. Extended properties can allow you to add comments to any of the database objects. Not only can you comment in line code, but you can comment the objects themselves. If you use the spade extended property, we can add a comment to the actual banking database. Let's do that. At this point, if we right click and hit properties and we go down to extended properties, we can see that we put a caption called the Simple Banking Database. One of the things I see up there is people not using schemes. That much scheme is a great way to segregate security.

SQL Server Create Schema

We're going to create a schema called Active and we're going to give it a caption, okay? And we can find schemas. If we go into banking on the security under Schemas, we can see Active. If we do properties again, we can see that we created an extended property called Caption. Last but not least, you can do this all the way down to the column level. We want to create a customer table. We're going to add some customers to the banking database. This one, we're going to have to specify the level, schema and table. The same thing, we're going to add another property. Last but not least, if we hit refresh and we maximize the table, we hit properties. There you go. Extended properties. We can see we added a caption to the table. Not only can you put comments inside the code, but you can actually comment the object so you can see them in the actual Object Explorer.

Let's go back to the presentation and talk about pros and comments. Cons for commenting usage. Pros. It allows the next developer to understand what you're trying to do. If your company has a consistent commenting and formatting, you'll be able to pick up a piece of code and understand it right away.

Cursors in SQL

Cons objects like tables do not support comments, but we saw there's a way around it and the placement of the header is important. If you want to actually have the header show up in the SP help text of say, the function or the startup seizure, you have to put that after the create statement as let's talk about cursors. That's the next thing. A cursor is a programming contract that processes rows row by row. I think Jeff Moden came up this concept called Arbi, which is row by agonizing row. Curses have been around for a long time. I've seen them and used them, say, back in 2000 with Cobalt going against Cybase. SQL Server and Cybase are both its way back then and they split. It comes to code base, if you look at the declare statement, that basically will allow you to declare the SQL statement that you're going to run the open, actually executes the SQL statement that you want to pop, and it populates a record set.

The fetch can fetch a row from the record set. Usually you put this in a while loop with a condition and you go row by row until you finish all the rows and then the closed statement close the cursor and the deallocate statement releases any internal constructs. Cursors theoretically are really easy to do. They can get quite complicated. I'm going to allow you to look on books online for details. You can do anything such as a fast forward cursor. You can do a read only cursor. There's many different variants. You can do one which is positional, so you can go forward and back when cursor. Usually I define a fast forward, which you're just reading information. Let's talk about curses, the Pros and cons, and then we'll do another demonstration. The pros. It's a very simple contract for processing a small set of data. I've used them.

Some MVPs say, hey, don't use curses. Some people out there say, hey, I never use a cursor. I've used curses. They're great for maintenance scripts that are not time sensitive. If you don't care, hey, it took an extra half a minute to run something, that's fine. If you're working for a financial institution that is dealing in stocks, yeah, half a minute is a long time. If you're going ahead and say you're updating indexes, it's not a big deal. Cons, if you want to go faster, usually use a set operation, okay? And also looping. The looping can be actually replaced with a tally table and a common table expression. Jeff Moden, if you go out there, talks about tally tables and has plenty of examples of common table expressions. Let's take a look at a cursor. We're going to first start with a sequel statement that's going to go against my Adventure Works database, which is right here.

One of the things a lot of people do is they turn I owe on as well as statistics on. That tells you, hey, how long it takes to actually execute a piece of code that works for simple statements, one line statements, we're going to look into the cursor and it's not going to work too well. A workaround for that with a little overheadness. Again, assuming that this is a long running query, is to show the timestamp before starting show the timestamp after starting and actually do some math. If we look at the actual SQL statement here first, I'm getting the last sale by customer in 2011. That's our inner derive table right here. What I'm going to do is join that to the sales header and the customer in person, return some data, and we're going to run it and let's take a look at how long it runs.

If we look at this wrong database, should have executed used Venture works up here but missed it. We can see that we have messages and we can see that the derived table actually created a work table in Temptv or maybe in memory. It actually joined everything and it took 86 milliseconds. Now if we rerun this with the end timestamp and the begin timestamp and do the math, we can see that it takes a little more. It takes over 100 milliseconds. Okay, so that's one way to do this particular join. Now let's take one more look at it before rewriting it as a cursor. We're getting a customer ID and the max order date for 2011. For each customer ID and order date, we're going to pull the first name, last name, ID total, and a date. Let's rewrite this as a cursor and see what the performance changes.

On this particular one, for me to actually duplicate the actual order by, we're going to have to stick the results into a temporary variable. We're going to use a temporary variable and what we're going to do is we're going to create a cursor and we're going to use the same select statement we had before. We're going to have customer ID max order date by 2011. We're going to open up the cursor. The first thing we do is we grab the first customer ID and the order date.

What we do is for each row, while there's data that's the fetch staff equal zero, we're going to insert first name, last name, so on from that join we saw before and then skip to the next record, close the cursor and deallocate. Last but not least to turn the results. Now we have it in the table very we have to go ahead and select star from it. Let's go ahead and turn on I O statistics and let me show you why using IO statistics is a problem with this. This particular loop is actually going to go ahead and loop around 14, six times, I think the number of records. What you would have to do if you're manually trying to do this is you would have to go through each one of these and actually say, okay, well, what? The work table is nine milliseconds.

I want to add another some of these zero, which means less than a milliseconds. The problem is we really can't get an accurate reading here. Okay, another way to do it is to actually go ahead and to take the time stamps again, subtract them. It's close enough, I mean, plus and minus, say 50 milliseconds. If we look at the example two A, I came up with 100 milliseconds execution time. If you do the same example with a cursor, and again, this is a toy example with 1408 rows, it's seven times longer to run. So there's definitely a performance change there. That's why a lot of people do not want you to use curses. Curses again, are great for use in small data sets like maintenance scripts. Do not use them when you're processing large amounts of data. So that's another tip. Error handling. Well, how many times have you seen an error occur and you don't know about it?

SQL Error Handling

Well, I've seen it a lot as a coder, I'd rather catch the error and handle it gracefully in my code versus allowing the developer to catch it, say, at the front end. There is a bunch of legacy code out there that has its end statements that test for the error and it basically tells you what the last error code is. If there is an error, you more than likely to exist in sys message table, you can look up the details. That's old school error handling. New school error handling. I don't know exactly when this was put in, maybe 2005 or 2012. Again, I'm guessing you'll have to look it up. Uses the try and catch blocks. You got a begin try and then try a begin catch and end catch. What they also added is six additional error functions and these return detailed information, a lot more than system dot message.

Let's talk about the usage of error handling, the pros and cons. The pros is that it allows you to develop a gracefully handled error and return at the database level. You can do something if you have a multi statement process of code, you can go ahead and you can roll back the code to a consistent state and that might be according to your business rule. Maybe you'd want maybe one insert to go in, but the second insert, well, that fails. Who cares? Just keep on going. Okay, cons there's a lot of code out there for the old way syntax that you have to do a bunch of if then statements. If error code then go somewhere, right? It usually has a go to label, otherwise you have to put that catch statement. You would have to add logic everywhere in that code for some type of catch.

The goes to a block of code that looks like the catch. Okay, so my first question would be to a person is where would you see this? Well, let's take a look. Where is it? On the SQL Server Agent under Jobs, if we look at the CIS policy purge history job and we script it to a crate too. Lo and behold, guess what? There's still legacy code out there. We can see that when executing this job, we have begin end transactions. Not only that, we have our error handler right here. If error is not equal to zero, then what's going to do is it's going to go to a quick roll back. Like I said, a quick rollback is their version of a catch statement. It's going to roll back the transaction. Again, even SSMS when it comes to jobs is actually using the same logic.

Let's look at the old school construct with an example. We're going to create a database called Math. The reason why I like math is I did an undergraduate in computer science and math, and we're going to talk about Fibonacci numbers.

Create SQL Server Database

You probably heard about Fibonacci numbers in A DA Vinci Code. Anyways, so we're going to create a database called Math. Going to hit refresh. We can see the Math database shows up here. We want to create a table called Fibonacci. It's nothing to write home about, so it has a my value, a number and a time. What we're going to do is we're going to show you how to do old school error handling. Two settings. There is settings that actually affect how error handling works. The first one we're going to use is we're going to use this one, which is allow arithmetic warnings actually happen. We're going to truncate the table and then we're going to look at this block of code that creates Fibonacci numbers. Basically the first Fibonacci number is zero. Next one is one. The third one would be zero. Plus one is one.

The fourth one would be one plus one is two. The fifth one would be two plus three, and then five, then eight, and so on. This is what's called the exponential equation, because what's going to do is grow exponentially. We did declare, so the big entry, but bigger even has bounds itself, okay? We're going to have a rhythmatic overflow. What we're going to do is we're going to increment it and then we're going to do error handling. See, hey, if it's too big, it's going to hit this error handle right here and say, hey, it's an arithmetic error. Also when we turn off the arithmetic warnings here and what's going to happen is it's just going to fail on an addition, but it's going to come back with a null value, okay? When we insert into the table, we're going to actually get another error because if you look back here, it says not null, and we'll catch that error too.

Let's actually run this and let's see if we can generate the first error. We can see that it ran a bunch of times and then eventually stopped on Fibonacci number 94, couldn't figure out 94. So how big did it get? Let's just take a quick look. If we do select 1000 rows, we can see these Fibonacci numbers and we can see it got 93 rows successfully. We do have a rather large number. We're trying to add these two large numbers at the bottom and it's actually generating arithmetic overflow. Let's go back and now say, hey, guess what, I don't really care about arithmetic wanting, just ignore them, don't truncate the table again. We're going to select thousand rows and there's nothing there to select. We're going to run the same piece of code over again, subtitle end and let's execute. And now we get an error.

Guess what, we get a different error. It says can't insert a no value.

Because it did do the arithmetic but when it went to insert into table error there. We have two different type of errors that we get here. Still not too explanative. It doesn't tell us too many details. I mean there's some details here, doesn't tell us what line number it happened on, so on. Let's see if we can do this better. The new statement is the user try and catch. What we're going to do is we're just going to drop this and start from scratch. The easiest way to do it and hopefully it's gone. Okay, I'm just going to run this all at once but I'm going to show you really quickly what it does. Let's create a database, let's create a table. We're not going to worry about this. We're going to do regular arithmetic error overflow first and this time we're going to do begin, try same code and try.

Then we're going to do a catch. I remember I said there was six or so new functions are added. These are the functions. There's error number severity, the state, the procedure that actually happened, which there's no procedure. Yes, it's going to come back null line number and error message. Okay, so let's execute this. Sorry about that. I think I got disconnected when I deleted the database. So we're going to try this again. And there you go. This time we did the same thing but now we're actually getting an error number 8115, which means an overflow of type BigAnt. It's more exploitative of what it is, tells you error line number that's actually happened, the severity in the state. So we're getting more details here. It's much better handling than the old school way to do it. Microsoft is definitely suggesting you hopefully to switch over.

As you can see they are still behind the times on their product. If we run the second set of code but the second condition, which is the actual null condition, we can see we get a different line number. Because it was farther down the code, it's 27 and it tells you it's a null value issue.

SQL Functions

Functions can be classified as two types. There's scala functions which return a single value and table functions which return multiple rows of data. I've been using SQL Server for a long time since version wow, six. Then it was seven in 2000. Back then there was no such thing as table value functions. There is nowadays that so additionally, table functions can be defined in line, which means that you just do a return clause and it returns a single T SQL statement. We'll see that in line. Table value functions perform really well.

Multiline table value functions are enclosed between the beginning and block, just like scala functions are, and they don't perform as well. And we'll talk why? Just like most objects you can use to create, alter and drop statement with functions, a lot of people have gone down the path of common language runtime functions for on premise. They're out of scope for this discussion. I also want to say they do give you performance boost. However, they are very fragile. They depend upon the right version of the common language runtime and sometimes they break. If you just want to keep it with an SQL, stay away from clr. Again, ansysql would be if you wanted to put the code to your schema, to any database back end, regardless if it's SQL Server or say a competitor. Pros functions are great because they can pack up logic and scala functions in line.

Table value functions perform rather well so you can use them. Multiline table value functions might perform badly. We're going to see how that optimizer looks at an ITVF and can actually merge it into the final plan. While a regular function, it's just a black box and it doesn't actually optimize it. We're going to go back to scala functions, we're going to go to Adventure Works and we're going to create something called get stock. A lot of times people do this. I just want to show you a coding technique. Sometimes what they do is they go ahead and they create a stub and then what they can do is they'll do an altar and any future ones they do an altar because they already got the permission set on the stub, right? It's one way you can do it. This one basically will go ahead and it's going to say take a product ID and it's going to go through the product inventory table looking for that product ID and it's going to return the sum, okay?

Because we want it to be an injury, if it's a null value, return is null, zero. And then last but not least, functions. Again, always a return type an as statement between the beginning and end block. You can have some code. There's always a return statement with a value. Okay, so let's take a look at this. If we go ahead and let me go over this piece of code right here. Checkpoint basically flushes anything to disk this, cleans out any pages memory and free the procedure cache. This basically says hey, what will happen if there's not a plan sitting in memory?

Again we're not looking at the DBCC or the set on IO statistics on we're just going to go ahead and execute the statement and get a timestamp before and after.

If we take a look I think it was about 84 milliseconds and again this one ran a little more than that for some reason but it depends. We'll go with the old numbers I record and I'll show you why. That way I don't have to do some math on this webex. Let's open up the file for B and what we're going to do is we're going to rewrite this many different ways. We're going to first try to rewrite this as a sub query then a drive table and an inline table value function and I just want to show you how to do that as well as see if there's a time difference.

We said that the original one was 84 milliseconds. One way to rewrite this is to go ahead and do a correlated sub query.

At the place where I want the stock level so for each product ID I want to figure out and this is your sub query, right? The quantity and I'm joining so it's correlated as the sock level. Now if we run this and execute we can see 60. Again for some reason it's interesting because I got better times last time. This one, this one is 140 milliseconds. Maybe something's running on my desk right now chewing up something. We can also rewrite this as a derived table. Okay? Derived table instead of actually doing a sub query here instead of for this one actually does the sub query and it's joining on a particular ID. We can go ahead and join on all IDs and then return the product name and stock level. Again this is a rewrite of the same code but as a derived table. Execute it and again with 434 rows and whatever this was, it was 66 before we had 67 66.

It looked pretty much the same, a little better than the original one which was a function.

Create Function in SQL

Last but not least we can also write it as a table value function. Funny thing is that will not return as perform as well as other ones. I think the reason is actually has to create some type of memory construct and return the whole table even though it's a single value. We're going to drop the function and we're going to create a new function. We create this here and if we run this here again we'll get some timing. Now the interesting thing again I wanted to go over is that we. Can see that create function. Instead of returning an end, we're returning a table as and this is in line because there's not multiple lines so it's going to run pretty good. We do select, we do an Is Null to prevent the Nulls from coming back, which we could handle a Null but we don't want them.

We're returning the results to actually use this. What we do is we use a cross apply function. We want to take table production product and cross apply it against our inline table value function and return the results. If we look at the actual numbers, let's see if they're still higher than last time it ran and they do look higher. Again, in production, why I did last time was 117 milliseconds. Unfortunately cable value functions did not win out. The traditional functions which we talked about, which are regular set operations actually perform better, but they're not as packaged up nicely. You can't call them over and over again. For a little hit scaler and in line table value functions perform very well. One thing that did not get a chance to show you is how a function itself is not understood by the compiler. So let's do that.

We're going to drop this function again, recreate it and I wanted to show you that's an important fact. We're going to drop this, going to recreate it and now we're going to actually run it, execute, but we're going to include the actual plan. We got to run it again and look at the execution plan. Now we can see that this takes the function, makes it a scala. If we go ahead and we show the execution plan, we can see that it has something called Ufnget stock, right. It hasn't been merged into the final plan. So it's a black box basically. Now if we go ahead and go to B and we're going to rewrite it. This is B, the third one, right? I'm going to drop it and rewrite it as a table value function and now we're going to run it again. We're going to include actual execution plan, going to look at it again, we're going to right click and we're going to look at the XML and now we look and do look for the USN.

We see it's in the actual definition of the query. Guess what, it's not there because no why it's been actually expanded out into the plan. The only thing that is showing up as a scale is the Is Null function. That's why inline table value functions work rather well because they get expanded and placed into the actual execution plan. Jeremy, please tell me when we're about ten of them.

Isolation Levels there are five main isolation levels. The following lists decrease in concurrency while improving isolation. Therefore more locks are used on objects to ensure isolation so the default is Read Uncommitted for SQL Server. On premise, uncommitted snapshot isolation for Azure SQL Database. The highest level is Serializable, which uses a lot of locks. Re Committed pretty much doesn't use any locks.

So again, we talked about this. The default for Azure is read Committed Snapshot Installation. You should be using this in your production environment. If you're not using it, please enable it. It will give you a little more consistency. What it does is when you go ahead, when you do recommitted writer's block readers, this will prevent writers from block readers because it will take a snapshot or version row versioning and put it in Tempdv. It's consistent for that transaction one, but your readers will still read. Again, there's minimal overhead for ASCII and Tempt evidence. This is an interesting thing that a lot of developers don't understand. I just want to show you this and I think it's really important. There are side effects for each one of these all the way down to snapshot. And Serialize will have no side effects. Obviously you're using either a lot of Tempt EV for Snapshot or you're using a lot of locks for serializable.

I want to go over dirty Reads, non repeatable reads, and Phantom reads. Okay, let's get out of here and take a look at the examples. Okay, so this example, okay, when we use the banking, we created a table called Customers, right? So I like the vendors. We're going to put some vendors in here.

We're going to truncate the table and we're going to add some adventures. Now we have Iron Man and Black Widow and so on. What we can do is we're going to open up two query windows and we're going to see how this works. We're going to be doing the Can Check transaction query window one. It's a new query and actually I can leave it here. We'll do it in this one too, and then we'll show you what the site effect is. There's two ways to do Redone Committee. You can actually use something called Transaction Isolation. Level does it for the whole session, or you can do it with a hint. Okay, so I think I copied over the hint, right? What we're going to do is we can open up a transaction. We're going to say, hey, Chris Evans, whose store likes feet at my favorite Vietnamese restaurant, and there's the address of it.

We're going to do an update and we're not going to do anything else. We're going to stop the transaction, execute one of the commands, out the DBC open transaction. So shoot that. These transactions are currently open and someone didn't close it. Now if we go ahead and do Read Uncommitted, guess what we're actually seeing. That Reservoir Avenue. That's where he lives at my Chinese restaurant. For some reason, say in our code we hit a condition we want to roll it back, execute. Guess what? It's no longer there. Right? But we did a dirty read. That's what an example of a dirty read is.

Let'S look at recommitted. We're going to use the same thing. We're going to clear it out and restock from scratch to execute. We have our same data set again. This time what we're going to do is we're going to run it through a delay. The second window, we're going to try to select them. One of the side effects, okay, of snapshot on that recommitted isolation not is that I just block readers, okay? What we're going to do is just going to wait for 15 seconds. We're going to execute this and we execute this. Guess what, it's going to stop waiting. This one has to wait 15 seconds. It'll probably take 16. Maybe this one's still waiting and so on. Again, RCSI allows you to read while the writer is doing something. If there was some processing that stopped the writer from completing right away, boom, it would work.

Create Database

Now there's something I definitely wanted to show you, which this is interesting. Okay, first of all, this had a pass event, basically the summit back, I think, in 2011. And Tony Rogers actually registered did it. And here's a link to him. We're going to create a database called Sequel Bog. What will happen is this is that regardless of what type of read you do in read isolation, more than likely what's going to happen is it has to do a scan of the data pages when you do account. The problem is if you have multiple data pages, it will not be able to put a lock on every single data page. Therefore you can get inconsistent counts for large tables. So this example will show you that. What we're going to do is we're going to basically create a database called SQL Blog.

SQL Create Table

We're going to create a table called BigTable. We're going to go ahead and add 100,000 rows. And Tony like this name in it. We're going to double the rows and we're going to add a primary key because we're going to do primary table scan against the clustered index. Execute a little seconds because it's thinking it's probably allocating a database. Let's hit refresh database. Is there table there yet? Tables there? Still executing. Okay. What we're going to do is we're going to do two infinite loops and we'll talk about the two infinite loops. First infinite loop is we're going to say clean the buffer. I don't want to keep anything memory. And then just select account. Second one is we're going to do two reverse actions and we'll see why they make a difference. First reverse action is we're going to take number one and make it the last entry in the table.

What's going to happen is it's going to double count on this. Because right now we have 200,000 rows in this table. Let's take a look. Select it once, execute 200,000. Because now we're moving the first row to the end, you get 2001. The reverse action is going to take the last row and move to the beginning. And guess what, we're going to undercount. If we run this infinite loop and we run the other one infinite loop, we're going to get numbers that vary between 2000, 200,001 and 199,999. We're going to run this update and then we're going to go back to the original and run this and execute. And eventually it starts showing something. I hope I have to stop this and I will say, but it showed regardless. There we go. Now we can see that the first count is coming up 199, one short, one plus, one short.

Sometimes it's I caught it. So that was exactly right. So one short, one plus. It all depends on timing. That's why I got the pile loop going on. Pretty interesting stuff. The interesting thing is that when counting, if you're doing an account, not like the sum or even sums, sometimes if something gets moved to the end, you could be off with the read isolation. If you really needed the exact number, then you want to use something that has more consistency, say serializable or make sure this user is not on the system and you get the exact number. Repeatable read. This is the last one I want to show you and it goes over the phantom racket.

We're going to do is we're going to truncate the table again. I'm going to add my adventures back. What we're going to do is do this, execute there. What we're going to do is we're going to select, pause and select. That should be as a repeatable read. What we're going to do in this one is if it's locked, it's going to block it. But we're going to do an insert.

Now the interesting thing is because it's repeatable lead, we're able to insert at the end during this 15 2nd delay and it's going to give us a phantom record. Because we didn't have it during the fresh read, we're still in the same transaction but a phantom record will show up. So there's execute and execute. Now we do it and eventually this will come back.

We can see that there's one record extra here that was inserting. That's a phantom record.

In short isolation levels, they can determine how the database is and they will go ahead and there's side effects and you should be aware of them. Okay. Transactions, transactions, okay. That would begin transaction. We showed that you can do save points. So it can be really complex. You can have nested transactions, you can do a rollback. Roll back the whole transaction to a safe point and commit. Basically commits all the data to the database and releases anything that was being used. Okay, so pros transaction, great way to make sure two or more statements related that either they commit all at once or they roll back Nested transactions and say points are supported. Two things that you need to worry about transactions and this is where developers usually go wrong, blocking and deadlocking. Okay, and let's talk about Deadlocking. Deadlocking. This is a picture taken from MSDN.

This shows that usually Deadlock is when two transactions take part and they open up the transaction in opposite order. This one shows the supplier being grabbed first get a lock on it. We try to look for the part and this one shows a lock on the part. We try to get the supplier and the bulk block and someone has to die. SQL Server is good enough to know that there's a Deadlock that kills one of the transactions. Your code should be smart enough to understand that and then do the appropriate action. Deadlocking.

We're going to go back to the original one and we're going to update the database for banking. So I'm just going to drop it. This one uses sequences. It's going to create a customer, an account, and AI transcription table. Okay, just like you're in. Hit refresh, maybe not do it again and let's execute it for broke. You can look at these scripts afterwards if you want to get an idea of what's going on. Interesting thing is we can see a user table customer, we can see a user table account, and we can see a user table transaction. That's the main points to get from this. Now we're going to create a transaction, okay, this is typically how you would go ahead and we're going to create a transaction called New add a new account. We add a new account, we're going to pass to it, say, checking or savings.

What we're going to do is we want to return back an account number. What we're going to do is we're going to use a sequence. I'm going to create a sequence called it's usually a nine digit number. So 100, 200, 300. So this is a sequence execute. Second thing we're going to do is we're going to create this. Now the interesting thing is this is all one transaction. Guess what, we're using some of the concepts that we talked about. We're using a begin try begin transaction. We're using a commit, we're using the end try. We're using a begin catch. We're doing a rollback. If there's more than one transaction, there was an error and we're going to show some errors numbers.

The main thing this is it does some really interesting stuff. For instance, first we're going to select from the sequence. Second thing, it uses an actual table variable because we're going to get the output. We don't know what the account number is until we insert it and get back from this new sequence number. It could be we can't do it like that because it might be multiple checking accounts for that particular person. Last but not least, we're going to insert into accounts, insert into a transaction a zero, because when we open up a new account, we're going to start with a zero balance. This basically creates a transaction and account record. So let's execute this.

We're going to delete anything there was, and now we're going to execute this. Iron Mantor, Black Widow, Captain America and Hulk are all going to get them.

Now we can see there's different transaction numbers being returned. Let's take a look at the results. If we execute this, we can see AI transcription work correctly. Now we've actually created a checking account and saving accounts rotating, and they all have zero balances. That's the correct way to actually do something. When it comes to transaction. Let's talk about blocking and then Deadlocks. Usually blocking that's bad, but it's not that big a deal. Deadlock is really bad because basically someone is going to be denied service and killed. On this one, we're going to look at these two IDs, 17 to 21, and we're just going to update the actual email address and so on. We're going to create two transactions. First transaction is going to update the actual person table and then we're going to wait for a delay. The second transaction is going to select from it and we're just going to show that it's just blocking.

So this is your recommitted blocking, right? If we go down here and run, this is going to create two store cedars. Now, interesting thing is we can actually do this in the text. We could run these two just like this and it would actually go ahead and show the two threads. In social blocking, we can also run it from a command prompt. We're going to do it from command prompts. If we go to webcast, we can see there's a run block test and it's going to call these two. If we hit Notepad, we can see we're SQL command and we're just going to run the test. So let's run the test. So now we should have two windows. One hit somewhere and we can see if we do SB, who. Two, we can see that sometimes it goes so fast that the order gets.

Blocking in SQL Server

Which it shouldn't because they're being launched at the same time and then the example doesn't work. So I'm going to manually do it. This is the one that runs a long time. This will run that takes a little longer. Now we can see that both running and we can see there's two things we can look at. First thing is we can look at Sys DM exact request and we can see it's blocking, tapping. We can also use SP two, and we can go down to SB, who two, and we can see that 58 is being blocked. 59 is blocking 58. So that's blocking, eventually these will stop. And guess what? Blocking will go away. So if we execute and guess what? Now blocking went away because it was time to blend it. Let's do an example on Deadlocks.

Deadlocks in SQL Server

Cool. Thank you, Greg. Okay, so barely have enough time. So deadlock. We're going to basically have the same thing. What we're going to do is now is we're first going to update the email address and then the person, and we're going to do it in reverse order.

Let's do it. We execute these, we get these two strip cedars, and let's look at the box. Now there's a couple of things you should know about this. You should turn these trace flags on. These trace flags will help you determine what actually happened.

What we're going to do is we're going to execute one and we're going to execute two. This should give me my blocking, and then we should be able to do this. If it didn't get killed already, it might have got killed already. Executing, executing some executing thread two. Executing thread one. Did not see it. Okay, so now it actually sees the blocking now. And guess what? This one actually tells us the actual engine was smart enough to find out that actually blocking actually happened and a victim was killed. If you want more information, you can actually go into the information logs and with that trace lock, which would be under SQL logs right here, and we can actually see the process ID, and we can see all the information about the actual blocks that were held. Let's talk about indexes. I think I only have time for one more to wrap up.

SQL Indexes

We'll go over indexes. I wish I had time for tempt tables. I did do a bunch of examples. I'll do one more example because it's just not enough time. Indexes. A lot of types of indexes, clustered and non clustered are the most common. There's a ton. We could spend a whole hour just talking about indexes tools. Query tuning again is its own self, a whole presentation itself. Dynamic Management View is a great way to look at production statistics. Unfortunately, a rebooted system resets a counter. In 2016, a query star was introduced. And this is really important.

I'm going to go over this versus temp table because I think temp tables are cool, but not that cool. The query. If you're not using a query store yet, you should use a querystar before 2016. You can grab the DMV statistics from like Glen Barry, but again reboot kills them. Many products out in the market from like SQL, Century Idea and so on, that will actually run a query against engine and periodically safety statistics.

Temporary Objects

Pros index can speed up the execution against topics for you to research like parameters sniffing, skewed statistics, cons make sure that the number of rights to index do not outweigh the number of reads because otherwise you're writing to it but not using it. And so consider dropping the index. A large number of indexes creases the time to insert update records. Temporary objects, going to talk about them really quickly and I will include the examples in the bundle so you can play around with them. Tables can be created in Temp DB themselves. Bad practice, but I've seen it done. Interesting thing is you can do modifications like drop indexes, add indexes could be really cold, right? Drive tables are queries that are treated as a table and you can actually update the drive table local temporary tables that are defined with a pound and have the scope of the current session global or double pound and have a scope of the last connection that's using table variables rates of this that way amps and have the scope of the transaction.

Common table expression falls in the same category as drive tables, which are really cool. Again, complex processing can be used, broken into steps, right? Each step can actually use a temporary object and that's the power of using these temporary objects. Nice thing about using Tempdb, guess what, I o might be fast because you at least have four files if your database administrator is doing this job. However, at the same time all the work is being done in Tempdb and you might have some negative effects on your database engine. Again, nothing is ever free in life. Objects either take space and Tempdb are in memory and make sure that you're not creating large objects because it can actually cause growth of your Tempdb files.

Query Store

Cool. I just want to show him diagnostic queries versus I want to show him the query store and again I'll include all this information in the bundle that we send out. So diagnostic queries. Glenberry has a great thing, it's been using forever. If we look at missing indexes and hit enter, we can see there's a query for missing indexes. It's query number 32, so we can run it, execute and boom, it doesn't get anything. What we're going to do is we're going to try running this actual piece of code right here and then we run this again and we execute. For some reason it's not getting I don't know, but try one more time, okay? Because I am not in the right database and I'm rushing excuse. Here we go. Let's see. Okay, so while I it works. Just want to show you that they get the same result regardless of whether or not use the Query Store or use that DMV.

The DMV is saying that VentureWorks Sales customer is missing index on person store ID. Excellent. Again, in 2016, this is a feature in 2016 only and above you can use a small part of the database, say Adventure Works to store query statistics. This is awesome because guess what? Reboot, you still have the query statistics. Let's take a look at this. I'm going to go ahead and ask by year three times, do some grouping and then I'm also going to do a summarize group. We're going to execute it. Now if we go to Adventure Works and before we now see a Query Store and we hit top consuming queries and we can see we have two of them. We can see that this is the count that was actually doing all of them and actually execution count twice and execution count one and nothing too big to write home about.

The interesting thing I want to show you and most important is the one we did here. This is a table valley function.

What we're going to do is we're going to run this again. I'm going to clear the query store. Execute. If we go here, this could reduce and now we're going to run this job execute and we're going to view the top queries we can see, okay, this is actually taking a really long time to take run. We can see that this is milliseconds is almost what, 1000 milliseconds a second is a long time. If we look, we can see there's a missing index. Before we couldn't tell this because it's a multi line table value function. Now, because it's actually being broken into its constituent parts and executed, we're capturing that and we get a plan. ID and we know that now we need to add index example. We can do query fix the query problem by creating a cluster index. Now what we're going to do is going to run the actual clear one more time and then last but not least, we're going to run the same statement, we're going to execute it and then we're going to close out of here and look at it again.

Optimize SQL

And now guess what? We increased the performance by tenfold, okay? From going almost a second down to two tenths of a second.

Rapid SQL

I guess the question no, actually, we're out of time. As John mentioned, we will make all of the scripts available. We'll get those all packaged up for you, as well as the slides and the webcast was recorded. We'll get the archive up and running probably by the end of today, so hopefully that information will be available for you to download. We'll also send out an email with links. With that, I want to thank John for presenting Take all the great information today. I also want to thank Idea for sponsoring today's event, so please visit Idear's website at www.idear.com to learn more about Rapid SQL and to download your free trial. Also, please visit msSQLTips.com to access all of our free SQL Server resources. Once again. I'm Greg Roberto from MS SQL Tips. Thank you for attending and have a great day. Bye.