SQLServerFast

T-SQL Tuesday #131 – Join operators simplified

Hugo Kornelis
No Comments
Another month, another T-SQL Tuesday. Our host in October, the amazing Rob Volk (b|t), wants us to explain database concepts like he is five. I like that concept. A lot. In fact, I have in the past already written similar posts, such as my explanation of SQL injection in non-technical terms to a twelve-year old grandmother. But that’s an old post, so I cannot use it for this T-SQL Tuesday. So instead, I’ll try to explain the physical join operators SQL Server supports in execution plans using a real life analogy. I don’t think a five year old would understand…
Read More

Plansplaining, part 11. Merge plans

Hugo Kornelis
2 Comments
Welcome to part eleven of the plansplaining series. You will have noticed that this is no longer a monthly series. But when I was asked recently to provide some insight in how a MERGE statement is reflected in an execution plan, I realized that the plansplaining format works fine for this. But first a word of warning. The MERGE statement, introduced in SQL Server 2008 as an easier alternative for “delete / update / insert” logic, turned out to have issues when it was released. And now, in 2020, many of those issues still exist. So I’ll just point you…
Read More

The SQLServerFast Execution Plan Video Training: an update

Hugo Kornelis
No Comments
Almost half a year has passed since I released the first set of videos of the SQLServerFast Execution Plan Video Training. During this time, I have been working hard to ensure that the next set of videos is even better! As I am now getting ready to finish the last work on that second set of videos, I had to reorganize how and where I had located the videos. This means that links you may have saved will probably not work anymore. New location All existing content is still available, just at a different location. The basic level of block…
Read More

T-SQL Tuesday #130 – Automated restores

Hugo Kornelis
4 Comments
It’s September 2020, and it’s time for T-SQL Tuesday. Our host, this time, is Elizabeth Noble (b|t). And her theme of choice is automation. Not automating the work of others, our day to day job, but automating our own work. To limit the risk of error, to reduce the stress, and simply to avoid boredom from doing the same thing over and over again. I don’t really automate many of my tasks. My work focuses more on writing and optimizing tuning, and less on database administration. That means I do less repetitive tasks than many DBAs do. That said, one…
Read More

Estimated I/O cost, a sign of an expected spill

Hugo Kornelis
2 Comments
Some execution plan properties get a lot of attention. Others tend to be mostly overlooked. But even those properties can, sometimes, relay interesting information. Estimated Operator Cost The Estimated Operator Cost property is one of those that does get a lot of attention. Perhaps even too much. When you look at an execution plan in SQL Server Management Studio, or in Azure Data Studio., it conveniently marks each operator with a line such as “Cost: 34%”. And some third party tools, such as SentryOne’s Plan Explorer, even go a step further and color code this cost in red, yellow, or…
Read More

T-SQL Tuesday #129 – Time capsule

Hugo Kornelis
1 Comment
The theme for the August 2020 edition of T-SQL Tuesday brought back memories. Back when my children were in elementary school, the school asked them to bring things to put in a time capsule. I was clueless what that would be, so my children patiently explained that it’s a container they’d fill with “things” and then bury somewhere, for future generations to dig up, open, and then marvel at how life was in our time. And now, Tamara Clark (b|t) has asked the SQL community to build our own time capsule. She asks us what we want future generations to…
Read More

PASS Virtual Summit 2020

Hugo Kornelis
3 Comments
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…
Read More

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

Hugo Kornelis
5 Comments
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…
Read More

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

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