SQL Server Cardinality Estimator

[Music] Hi SQL folks, welcome to another free SQL server content focusing on performance tuning. This demonstration, this tutorial, I termed this as funny. By the end of the demo, you would realize why am I calling it funny? In fact, all the store procedures, the demonstration, I've named them as funny. And let's see why it is being called so. Now what I would do is before I uh talk about today's subject, let's go and execute the store procedure. This is probably going to take some time and while it is running we'll talk about a few things. So let's go and execute the store procedure. Take a note that actual execution plan is turned on. So I'm going to execute this and this is probably going to take a minute or two maybe a little more. Now what happens is when your workloads are running slow, you would put on your troubleshooting hat and look at a lot of different things. I'm randomly going to call out a few things like you're going to look into the execution plan. You're going to look at the IO and execution statistics. How much time did it take? What kind of uh scan and seek was it doing? Uh you're going to look at lot of different numbers. the actual execution metrics inside the execution plan. You're going to look at expensive iterators or expensive operators and try to find out tuning opportunities there. You would go and look into the TSQL as as a whole and look at tuning opportunities. Is the TSQL written the right way? Are you following good TSQL practices? Uh are you avoiding those antiatterns? And more specifically inside the execution plan you're going to look at all those indexes you know the scans and the seks the sargability um are there you know are you avoiding sorting or is sort very expensive is it consuming a lot of memory you're going to look at hash operations and is there a way to minimize them or get rid of them so many different things you're probably going to look at all sorts of locking blocking and uh You're going to see if the query is being held up. Are there other processes that are blocking your own thread? So many different things. This query is still running. If you look at the execution time there, it is 1 minute 40 seconds now. There in the status bar and the query is still running.