Estimate vs Actual Number of Rows

[Music] Hi SQL folks, welcome to another tutorial from SQL Mestros. Today's content is a quick tip about query tuning. Now when your query is running slow, one of the first things you do is look at the execution plan and then you are trying to find out tuning opportunities. This is a common thing that we do and in the execution plan you're looking for so many different things. Amongst them, one of the things that you're looking for is the cardality estimation, which is the estimated number of rows versus actual number of rows. Okay, let me run the stored procedure and show this to you. So, here is a stored proc called DBO funny. There are reasons why I call this as funny, but anyway, we'll go and execute this. And the actual execution plan is turned on. Let's go here. And now if you see this execution plan, it is not a very big plan, but it's still a sizable plan. You're going to look at a lot of scans and seeks and these hash matches and whatnot. And if this query is running slow, you are going to find all those tuning opportunities. You will see where you could make improvements. But one of the things that you're doing here is you're taking the cursor over each arrow here or maybe on the operator and you're looking at the estimated number of rows versus actual number of rows. Now let's say I take my cursor over hashmatch here and I zoom in and I will show you the actual number of rows for all executions 290. And then you will see here estimated number of rows for all executions 505. I'm not saying that this is a bad estimation but I'm just this is just an example a dummy example where you're comparing the estimation versus actual. Now for people who are unvered we are talking about this estimation versus actual meaning when you submit your query to the optimizer the optimizer is applying a lot of transformation rules etc. your query goes under different optimization phases and then it estimates that I am going to return X number of rows that X value is 505 here but when the query is actually running the number could be different and if this is a huge gap that is where you may have some tuning opportunities or what you you may want to investigate as to why my estimation went bad and there potential reasons for that that's a different content. What is the challenge in all of this? The challenge is taking the cursor over each operator here and trying to figure out this estimation versus actual. Now you can of course always take the cursor over the arrow here which is just going to give this a little more quickly than searching through those data items.