Press "Enter" to skip to content

Category: Query Tuning

Query Plans from Incomplete Executions

Published 2022-10-10 by Kevin Feasel

Erik Darling needs information:

One thing that comes up pretty often when you ask for an actual execution plan, is that the query “never finishes” and one can’t be captured.

Good news! There are ways for you to get execution plans in progress with some of the actual query plan elements inside.

You don’t have to let the query run to completion, but generally if you give it a few minutes you can capture where things are going wrong.

Read on to see how, as well as the limitations of each technique.

Comments closed

Eager Index Spooling

Published 2022-09-20 by Kevin Feasel

Chad Callihan has a great analogy for eager spools:

Think of a time when you went to someone for help on a problem. Rather than walk you through how to solve the problem, that someone just groans, speeds through solving it for you, and sends you on your way without any explanation.

Did you learn anything? No.

Will you need help again next time that same problem comes up? You bet.

I also like Erik Darling’s explanation that an eager spool is SQL Server’s passive-aggressive way of telling you that you need an index but no, you’re just too busy to create one so I’ll just keep working here all day and do you even call your poor mother anymore?

Comments closed

Using EvaluateAndLog to Diagnose DAX Performance Problems

Published 2022-09-19 by Kevin Feasel

Chris Webb does a bit of sleuthing:

The Switch function is often a cause of performance problems in DAX. Last year a number of optimisations were made that fixed the most common issues (and as a result you should ignore older blog posts that you may find on this subject) but some scenarios still remain where performance can be bad; Marco and Alberto wrote a great post recently outlining some of them that I encourage you to read. How do you even know whether your measure is slow because of your use of the Switch function though? Trying to read query plans, as Marco and Alberto do in their post, isn’t usually practical and commenting out branches in a Switch to see if there is a performance change can be very time-consuming. Luckily, the new EvaluateAndLog DAX function can help you diagnose Switch-related performance issues.

Click through to see how.

Comments closed

Date Calculation (and Calendar Tables) in SQL Server

Published 2022-09-15 by Kevin Feasel

Aaron Bertrand makes the case for calendar tables:

In a previous tip, Simplify Date Period Calculations in SQL Server, I described how to easily calculate certain dates, like the first day of a given month, quarter, or year. Another common scenario is trying to find the last weekday of the month, or the nth Monday or Wednesday. In this tip, I will show ways to simplify these calculations, both with and without a calendar table.

Click through for Aaron’s approach to the problem. I have a blunter approach in creating an expansive calendar table and using it. You do the heavy lifting one time and are good for life on that server.

Comments closed

SQL Server 2022 Query Store Hints

Published 2022-09-09 by Kevin Feasel

David Pless takes us through some new query hints:

Query Store hints provide a direct method for developers and DBAs to shape query plans without changing application code.  

Query Store hints are a new feature that extends the power of Query Store—but this means that Query Store hints does require the Query Store feature to be enabled and that your query and query plan are captured in the Query Store.

Just like plan guides, Query Store hints are persisted and will survive restarts, but Query Store hints are much easier to use than plan guides.

Read on to see which options are available and how they work.

Comments closed

Power BI: Merge Joins and Nullable Columns

Published 2022-09-06 by Kevin Feasel

Chris Bailiss dives into some join problems in Power BI against Snowflake and SQL Server:

Power BI generates SQL that performs very poorly against Snowflake (and possibly other database platforms) when joining nullable columns, e.g. in the “Merge Queries” action in the Power BI query editor.

Specifically, the SQL generated prevents the use of the “hash join” physical join algorithm that has been one of the main algorithms used for decades by database engines when executing joins between large tables. This forces database engines to fall-back to using very inefficient physical joins (e.g. loop/cartesian join) that can perform orders of magnitude slower.

The reason why Power BI generates different SQL for non-nullable for nullable columns in a join is explained in the “Problem Part 2” section below.

Read the whole thing—and there’s a lot of good information in there.

Comments closed

When Trivial Plans Hide Relevant Information

Published 2022-08-24 by Kevin Feasel

Chad Callihan wants the optimizer to sweat a bit:

When you execute a query, the SQL Server query optimizer determines potential query plans and decides which plan is the cheapest. It’s possible for a query to be so simple that there’s no need to bother weighing out different plans before returning results. In this case, SQL Server may use a trivial plan. Let’s look at an example of this plan and one reason why simple may not always be the best.

Click through for an example.

Comments closed

Variance in Parallel Query Performance

Published 2022-08-24 by Kevin Feasel

Joe Obbish takes on parallelism:

You may have noticed large variations in elapsed time for parallel queries while performing query tuning or observing a production workload. This post reviews some of the possible explanations for those variations. I’m testing on SQL Server 2022 RC0 but this behavior can be observed on all currently supported versions of SQL Server.

Joe has an interesting example (and my guess of how bad the performance would be was not too far off, though I did underestimate the difference), as well as several possible causes and mitigation strategies for slow parallel queries.

Comments closed

sp_prepare and Parameter Sensitive Plan Optimization

Published 2022-08-23 by Kevin Feasel

Erik Darling is a bit surprised:

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 it, though, so here goes.

When you use sp_prepare, parameterized queries behave differently from normal: the parameters don’t get histogram cardinality estimates, they get density vector cardinality estimates.

That part’s not the surprise. You’ll have to click through for that.

Comments closed