I Know You
You have too many indexes on too many tables already, and the thought of adding more fills you with a dread that has a first, middle, last, and even a confirmation name.
This is another place where …
Tag: Performance
Making The Most Of Temp Tables Part 2: Materializing Expressions
Bad Data
A lot of the time when I see queries that are written with all sorts of gymnastics in the join or where clause and I ask some questions about it, people usually start complaining about the design of …
Making The Most Of Temp Tables Part 1: Parallel Inserts
Sing Along
If you have a workload that uses #temp tables to stage intermediate results, and you probably do because you’re smart, it might be worth taking advantage of being able to insert into the #temp table in parallel.
Remember …
An Edge Case When Working With Date Parameters
Wheeze Man
When people tell you that working with correct data types is important, it’s for a variety of very good reasons.
Not only can you avoid performance issues, but you can avoid strange query plan distractions, too.
Let’s look …
Be Careful Where You Use AT TIME ZONE
Universal
Databases really do make you pay dearly for mistakes, and new linguistic functionality is not implemented with performance in mind.
I’ve written before about how to approach date math in where clauses: Where To Do Date Math In Your …
Troubleshooting RESOURCE_SEMAPHORE_QUERY_COMPILE Helper Queries
Unfortunate
RESOURCE_SEMAPHORE_QUERY_COMPILE happens, in a nutshell, when SQL Server has allocated all the memory it’s willing to give out to compile query plans of a certain size and, throttles itself by making other queries wait to compile. For more details, …
Why You Shouldn’t Ignore Filter Operators In Query Plans Part 2
If You Remember Part 1
We looked at a couple examples of when SQL Server might need to filter out rows later in the plan than we’d like, and why that can cause performance issues.
Now it’s time to look …
Why You Shouldn’t Ignore Filter Operators In Query Plans Part 1
Source Of Frustration
Ahem.
*taps mic*
When we write queries that need to filter data, we tend to want to have that filtering happen as far over to the right in a query plan as possible. Ideally, data is filtered …
One Thing The “New” Cardinality Estimator Does Better
Or “Default”, If That’s Your Kink
Look, I’m not saying there’s only one thing that the “Default” cardinality estimator does better than the “Legacy” cardinality estimator. All I’m saying is that this is one thing that I think it does …
Not All Function Rewrites Are Straightforward
And Some, Not At All
Let’s say at some point, you just didn’t know any better, and you wrote a scalar function to make some common thing you needed to do all “modular” and “portable” and stuff.
Good on you, …