Blog - Page 14 of 26 - SQLServerFast

PASS Virtual Summit 2020

August 4, 2020

You probably have heard this already: PASS has decided that PASS Summit will go virtual this year. Instead of assembling in Dallas, we’ll all stay home. Presenters will present from behind their computers, at home. Attendees will watch on their devices, from their homes. It will sort of be the same, but not the same at all! I know a lot of you are debating whether to attend or not. I understand that. The live, face to face experience is one of the cornerstones of PASS Summit, of any community event. A virtual event will never capture that the same…

Stop the “seeks are better than scans” myth, now!

July 27, 2020

Hugo Kornelis

Can we please all stop the nonsense? Now? While I admit that I haven’t seen the literal statement “seeks are better than scans” in a while, I do keep encountering that generic idea, just in other words. Just as recently as this week, on the very popular blog of my good friend Pinal Dave (who knows I’m writing this post), I read this: “The query was indeed running extremely slow […] even though the main operator on the table was index seek.” So? What gives you the impression that an Index Seek should give better performance? Is this an artefact…

T-SQL Tuesday #128 – My mistake

July 14, 2020

Hugo Kornelis

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…

When the actual IO statistics seem wrong

June 26, 2020

Hugo Kornelis

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…

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

June 9, 2020

Hugo Kornelis

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…

Actual I/O Statistics in the execution plan

June 8, 2020

Hugo Kornelis

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…

Row counts and arrow width, more missing nodes

May 28, 2020

Hugo Kornelis

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,…

Row counts and arrow width, Compute Scalar

May 25, 2020

Hugo Kornelis

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…

Row counts and arrow width, missing nodes

May 21, 2020

Hugo Kornelis

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…

Row counts and arrow width, read or returned

May 18, 2020

Hugo Kornelis

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…

PASS Virtual Summit 2020

Stop the “seeks are better than scans” myth, now!

T-SQL Tuesday #128 – My mistake

When the actual IO statistics seem wrong

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

Actual I/O Statistics in the execution plan

Row counts and arrow width, more missing nodes

Row counts and arrow width, Compute Scalar

Row counts and arrow width, missing nodes

Row counts and arrow width, read or returned

PASS Virtual Summit 2020

Stop the “seeks are better than scans” myth, now!

T-SQL Tuesday #128 – My mistake

When the actual IO statistics seem wrong

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

Actual I/O Statistics in the execution plan

Row counts and arrow width, more missing nodes

Row counts and arrow width, Compute Scalar

Row counts and arrow width, missing nodes

Row counts and arrow width, read or returned

Hugo Kornelis

Subscribe via Email

Meta

Archives