SQLServerFast

T-SQL Tuesday #128 – My mistake

Hugo Kornelis
1 Comment
My second favorite method of learning is to learn from my mistakes. Or as I often put it, learn from your mistakes so that the next time you can make a new one, which isn’t quite as boring as repeating the same mistake over and over again. But even better than learning from my mistakes is learning from yours. No, not you specifically. Everyone else. Because while learning from mistakes is useful, making mistakes isn’t fun at all. If someone else already made a mistake, the why should I have to repeat that? And apparently I am not alone in…
Read More

When the actual IO statistics seem wrong

Hugo Kornelis
1 Comment
I recently wrote about the Actual I/O Statistics property that you can find in an execution plan plus run-time statistics, which allows you to see exactly how much I/O was done by each individual operator. This is a great feature and I’m very happy with it. But, as I already indicated at the end of that post, there are some issues as well. Let’s take a look at the dark side of this property. Error, does not compute So, let’s look at an example, based on this simple query: SET STATISTICS IO ON; SELECT sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail AS sod…
Read More

T-SQL Tuesday #127 – Non SQL tips and tricks

Hugo Kornelis
1 Comment
The June 2020 edition of T-SQL Tuesday is hosted by Kenneth Fisher (b|t). He wants us to share some tips and tricks … for anything that’s not SQL Server. I first thought I’d have to sit this one out. I’ve never been really big on tips and tricks. I sometimes look in awe at all the handy stuff I see other people do. And then I try to memorize it all … and fail miserably. For me, it is apparently more efficient to just use the simple, standard methods, then it is to try to memory all the hacks and…
Read More

Actual I/O Statistics in the execution plan

Hugo Kornelis
3 Comments
One of the things that many people look at when tuning their queries is the amount of I/O required per execution. Of course, when the CEO is waiting for their favorite report to render, we only care about elapsed time. But for most other queries, especially for those that run thousands of times each day, reducing I/O is often our main objective. The old ways The standard method to get insight in the amount of I/O that was done while executing a query is to precede the query with the statement SET STATISTICS IO ON . (The same setting can be…
Read More

Row counts and arrow width, more missing nodes

Hugo Kornelis
1 Comment
Over the past weeks, I have published several posts about Actual Number of Rows, Estimated Number of Rows, how they are reflected in the arrow width in the graphical execution plan, and several scenarios where this can be wrong or misleading. And now, after telling you in my previous post that this was the last in this series, I am back with yet another one. I discovered this only very recently. It was not planned for the series, because I was not aware of it. Until I ran into it while preparing a demo, got fooled by it, investigated it,…
Read More

Row counts and arrow width, Compute Scalar

Hugo Kornelis
1 Comment
This is the fifth and final post in my May 2020 series about the Actual and Estimated Number of Rows properties in execution plans. In the previous posts, I talked about how you can use them, the confusion a Nested Loops operator can cause, how sometimes the graphical representation is based on input rather than output, and how missing nodes can catch one off guard. This last part focuses on the issue that is perhaps the most common of all: the lack of data reported by a Compute Scalar operator, and how this affects the graphical execution plan Compute Scalar…
Read More

Row counts and arrow width, missing nodes

Hugo Kornelis
1 Comment
I have this month already blogged about good ways to use the Actual Number of Rows and Estimated Number of Rows properties, about the confusing representation on operators that execute more than once, and about the confusing choice to render arrow width based on the Number of Rows Read when it is available. Today I’ll show yet another case where execution plans can mislead you. Not as common as the previous two situations, but with the potential to cause heavy panic when you first encounter it. Misleading estimates caused by missing nodes The issue I will talk about today is…
Read More

Row counts and arrow width, read or returned

Hugo Kornelis
1 Comment
In my last two posts, I presented use cases for the Actual Number of Rows and Estimated Number of Rows properties, and showcased one shortcoming. Was that the only problem with these properties? Unfortunately not. Rows read versus rows returned The visual of the arrows in an execution plan strongly suggests that they represent the flow of rows from one operator to another. And hence, the width of that arrow strongly appears to be an indication of how many rows are passed between the connected operators. That used to be always the case. But unfortunately, this changed in December 2017…
Read More

Row counts and arrow width, ignoring execution count

Hugo Kornelis
1 Comment
In my previous post, I talked about the Actual Number of Rows and Estimated Number of Rows properties, their visual representation in execution plans, and the most important ways in which this can be used. But life is not always perfect, and Microsoft likes to remind us of that. Sometimes, these properties report values in weird and confusing ways. Sometimes the arrow width, as the visual representation in the execution plan, misleads us. Let’s look at one such case. Average per execution versus total of all executions When looking at the Actual and Estimated Number of Rows properties of an…
Read More

T-SQL Tuesday #118 – My response to COVID-19

Hugo Kornelis
2 Comments
The May 2020 edition of T-SQL Tuesday is hosted by Glenn Berry (b|t). On the surface, his topic is about “Folding@Home”. But if you read closer, you’ll see that Glenn actually asks: “what are you doing as a response to COVID-19?” And while Glenn does suggest that zooming in on how we optimize our hardware for Folding@Home might be a good idea for this round of T-SQL Tuesday, the topic allows for more freedom. I’ll grab that freedom and use it to talk about something else I do as a response to COVID-19. Execution plans? Yes, execution plans It should…
Read More

Hugo Kornelis

Married, father of 2 children.

Awarded 13x MVP (2006-2016; 2019-now), in the areas SQL Server and Data Platform.

Subscribe via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Archives

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close