Execution Plans

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

Cheeky If you thought that title sounded familiar, you sure were right. That’s called a classical reference. So here we are, three whole years later, and yet another improvement to the engine promises to fix performance issues forever and ever. While this isn’t exactly an entirely new cardinality estimator, it’s an entirely new branch in…
Read More

Replacing ISNULL In A WHERE Clause

Execution Plans, T-SQL
I like blogging Really, I do. But sometimes I like a change of pace. Instead of blogging about this question, I posted it over on dba.stackexchange.com. Also, I thought it was an interesting question, and apparently many other people agreed. Not just because I wanted some more points, but because blog comments are notoriously sucky…
Read More

Decrypting Insert Query Plans

We’ve all been there Staring at a simple query that has a three mile long execution plan. After the first time, you learn to always look to make sure there’s not a secret view involved. After looking at query plans for modifications involving indexed views, I decided to look at some other fairly common table…
Read More

Some Plans Are Wider Than Others

Execution Plans, SQL Server
Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here’s an execution plan for an update statement: The Narrow Plan (click to see PasteThePlan details) It’s a pretty simple query – it just scans the Posts table looking for any post with PostTypeId = 8, and then updates a single field for the first 250…
Read More

What’s the Difference Between Estimated and Actual Execution Plans?

I’m going to use the dbo.Users table in the StackOverflow demo database and run a pretty simple query: Transact-SQL SELECT * FROM dbo.Users WHERE DisplayName LIKE 'Brent%'; 123 SELECT *FROM dbo.UsersWHERE DisplayName LIKE 'Brent%'; First, hit Control-L in SSMS and get the estimated execution plan. Here it is: Estimated execution plan: https://www.brentozar.com/pastetheplan/?id=H1TeRlZke Click on the…
Read More