Execution Plans

The Many Mysteries of Merge Joins

Not A Single Picture Of A Zipper Image humbly borrowed from https://70srichard.wordpress.com/2014/12/17/beverly-hills-cop/ There are some interesting things about Merge Joins, and Merge Join plans that I figured I’d blog about. Merge joins have at least one interesting attribute, and may add some weird stuff to your query plans. It’s not that I think they’re bad,…
Read More

Hash Join Memory Grant Factors

Buskets Much like Sorts, Hash Joins require some amount of memory to operate efficiently  — without spilling, or spilling too much. And to a similar degree, the number of rows and columns passed to the Hashing operator matter where the memory grant is concerned. This doesn’t mean Hashing is bad, but you may need to…
Read More

Why Multiple Plans for One Query Are Bad

I’m going to demo this using the Stack Overflow public database. We’ll use the Users table – which has exactly what you think it has, everyone who’s asked/answered/commented at StackOverflow.com. I need to search for people by their DisplayName, so I’ve created an index on that: Transact-SQL CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName); 1 CREATE INDEX…
Read More

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

sp_prepare For Mediocre You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)! Great posts, Kendra! Following the same theme, we found this issue while looking at queries issued from JDBC. Specifically, the prepared statement class seems to cause queries to hit the density vector…
Read More

Memory Grants: SQL Server’s Other Public Toilet

Sharing Is Caring When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants. The problem becomes itself when queries start to over and under estimate their practical needs. Second Hand Emotion Queries ask for memory to do stuff. Memory is a shared resource. What kind of stuff? Well, usually…
Read More

Functions Can Still Use Indexes, Kinda.

This sentence gets repeated a lot. You know the one: “Functions prevent the use of indexes.” Well, knowing you, it’s probably just your indexes. I’ve seen your indexes. Functions can use indexes, but even so, that doesn’t mean that they’re going to perform as well as queries without functions. Which Indexes? In the SUPERUSER database (yeah, I know, I’m…
Read More
Brent Ozar reading

“But It Worked in Development!” – 3 Hard Performance Problems

Development, Memory Grants
You’ve been performance tuning queries and indexes for a few years, but lately, you’ve been running into problems you can’t explain. Could it be RESOURCE_SEMAPHORE, THREADPOOL, or lock escalation? These problems only pop up under heavy load or concurrency, so they’re very hard to detect in a development environment. In a very fast-paced session, I’ll…
Read More