Category: Execution Plans

Performance Tuning

When Query Plans Lie Part 2

May 9, 2018

Getting Weirder
In Part 1, we looked at how query plans can tell us little lies.

Now we're going to look at how those little lies can turn into bigger lies.
Adding An Index
Right now, this is our query:
[crayon-6a6ea4a14a14a228480896/]
If we add this filtered index, our query will have a grand ol' time using it;
[crayon-6a6ea4a14a154908896987/]
Leaving aside my own advice about filtered indexes, what happens to the query plan now?

Read more about When Query Plans Lie Part 2 Be the first to comment
T-SQL & Development

Concurrency Week: How Entity Framework and NHibernate Can Cause Poison RESOURCE_SEMAPHORE Waits

April 25, 2018

I've already blogged about my dislike for ORMs from a production DBA performance tuning standpoint only. I get that they're useful to developers. I get it. But I'm focused on performance.

A quick recap of what I don't like about ORMs from that other blog post:

Read more about Concurrency Week: How Entity Framework and NHibernate Can Cause Poison RESOURCE_SEMAPHORE Waits 6 comments — Join the discussion
Performance Tuning

Index Key Column Order And Supporting Sorts

April 18, 2018

Whatever Man
When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access other key and included columns to satisfy other parts of the query, whether they're joins, predicates, or selected columns.

Read more about Index Key Column Order And Supporting Sorts 12 comments — Join the discussion
Performance Tuning

Hash Join Memory Grant Factors

March 29, 2018

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 take some extra steps when tuning queries that use them.

Read more about Hash Join Memory Grant Factors 8 comments — Join the discussion
Performance Tuning

Table Valued Parameters: Unexpected Parameter Sniffing

March 23, 2018

Like Table Variables, Kinda
Jeremiah wrote about them a few years ago. I always get asked about them while poking fun at Table Variables, so I thought I'd provide some detail and a post to point people to.

There are some interesting differences between them, namely around how cardinality is estimated in different situations.

Read more about Table Valued Parameters: Unexpected Parameter Sniffing 10 comments — Join the discussion
Performance Tuning

Troubleshooting Parameter Sniffing Issues the Right Way: Part 2

March 7, 2018

In part 1 of this series, I showed you how to get the compiled parameter values for an execution plan that you are investigating. You've identified it as a bad execution plan. You've already fixed the production emergency by removing the bad plan from the plan cache. You're almost ready to start testing for a workaround or a solution to avoid this issue in the future.

Read more about Troubleshooting Parameter Sniffing Issues the Right Way: Part 2 24 comments — Join the discussion