SQLServerFast

Plansplaining, part 15. Foreign keys, part 4

Hugo Kornelis
4 Comments
Welcome to part fifteen of the plansplaining series. In the three previous parts I looked at the operators and properties in an execution plan that check a modification doesn’t violate foreign key constraints. That part is done. But I’m not done with foreign keys yet. We normally expect foreign keys to throw an error on violations. But that’s actually only the default option: they can also be set to be self-correcting. This is done using the ON UPDATE and ON DELETE clauses, which provide the user with several choices on how to handle child data that would become orphaned, and…
Read More

Plansplaining, part 14. Foreign keys, part 3

Hugo Kornelis
3 Comments
Welcome to part fourteen of the plansplaining series, where I wrap up the mini-series on how simple foreign keys have huge effects on execution plans for data modifications. We already looked at inserting data in the referencing (child) table, and at deleting data from the referenced (parent) table as well as updates in the child table. We did not and will not look at deleting from the child table or inserting in the parent table: those operations can by default never violate the foreign key constraint, so no additional logic is needed. So that means there is only one thing…
Read More

Plansplaining, part 13. Foreign keys, part 2

Hugo Kornelis
2 Comments
Welcome to part thirteen of the plansplaining series. In this part I continue the exploration of how execution plans check foreign keys on data modification. In the previous part, we looked at foreign key checking when data is inserted. Inserts in the referencing (child) table can, by definition, not cause foreign key violations, so no checking is done in those cases. But for inserts in the referenced (parent) table, SQL Server needs to check that the inserted value does indeed exist. We saw how both the Nested Loops and the Merge Join operators have some specific optimizations to do that…
Read More

Plansplaining, part 12. Foreign keys, part 1

Hugo Kornelis
5 Comments
Welcome to part twelve of the plansplaining series, where I’ll discuss foreign keys in the context of execution plans. And just to be clear, this is not about how foreign keys and other constraints can be used to simplify execution plans and speed up query executions. That is documented in so many places that I trust you already know how important those constraints are. No, today we’ll look at the extra work that SQL Server needs to do to ensure that your changes don’t violate any of your foreign key constraints. Sample tables I will use a very simple scenario…
Read More

PASS virtual summit: thoughts, pre-con, and sessions

Hugo Kornelis
No Comments
As you probably already know by now, the 2020 edition of the PASS Summit will be held as a virtual event. Drawbacks That has drawbacks, of course. I’ve been to many PASS Summits in the past, and it’s always a great experience to be among so many fellow data professionals. To be surrounded by thousands of people that I know to have the same passion for data as I do, to face the same struggles in their daily work that I do, and to share my eagerness to absorb more information. The interactions in the hallway, the casual conversations during…
Read More

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

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