Have You Heard The One…
Whenever I’m tuning a long stored procedure, I use sp_HumanEvents to grab query plans and other details about what’s going on.
The thing is… It should be a lot easier than that. Unfortunately, when …
Category: Execution Plans
How Microsoft Could Make Problems In Execution Plans Easier To Understand
Round Up
Execution plans have come a long way over the years, gradually adding more and more details as computing power becomes less of a hurdle to collecting metrics.
The thing is, it’s not always obvious where to look or …
Some Notes on sp_prepare And SQL Server 2022
OldBad
I admit that sp_prepare is an odd bird, and thankfully one that isn’t used a ton. I still run into applications that are unfortunate enough to have been written by people who hate bloggers and continue to use …
Views vs Indexed Views In SQL Server
Award Winning
Imagine you have a rather complicated query that you want to abstract into a simple query for your less-than-with-it end users.
A view is probably a pretty good way of doing that, since you can shrink your preposterously-constructed …
Why MAX Data Types Are Usually A Bad Choice For SQL Server Columns
Easy Rider
When you’re trying to figure out how to store string data, it often seems easiest to just choose an extra long — even MAX — data type to avoid future truncation errors.
Even if you’re storing strings with …
SQL Server 2022 CTP 2.1 Improvements To Parameter Sensitive Plan Optimization
Hop On Your Good Foot
Several weeks back, I blogged about a missed opportunity with the new parameter sensitive plan feature. At the time, I thought that there was indeed sufficient skewness available to trigger the additional plan variants, …
Even If SQL Server Table Variables Were Always In Memory, It Wouldn’t Make Them Better Than Temp Tables
Past Perfect
You know all those pesky developer myths that never go away?
- CTEs and Views can’t use indexes: WRONG
- Subqueries are slower than joins: WRONG
- Temp tables are bad for performance: WRONG
- Table Variables are always in memory: WRONG
Why You Can’t Always Rely On Estimated Query Plans In SQL Server
Simple Enough
Most of the time, if you get the estimated plan for a query, the actual execution plan will look about the same.
You’ll get the additional information of course, but the shape and most of the other attributes …
Let’s Stop Calling Queries “Expensive”
Bad Names
When we talk about finding queries to tune, there’s an unfortunate term that gets thrown around: Expensive Queries.
Why is it unfortunate? Well, it reinforces the wrong mindset when it comes to query tuning, and leads people down …