A Fine Slice Of SQL Server
Arun Sirpal is looking for a query plan:
Whatever your database background is you will have heard of an execution / explain plan. Snowflake is no different. Coming from a MS SQL background I was used to reading plans from right to left. Now it is bottom to the top!
Read on to see how you can find the query plan for an operation.
Comments closedChad 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 closedChris 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 closedAaron 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 closedDavid 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 closedChris 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 closedChad 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 closedJoe 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 closedErik 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 closedDave Mason has a new blog theme and a post on OPENJSON performance:
Support for JSON data has been around in SQL Server for a while now, starting with SQL 2016. The OPENJSON rowset function is the built-in function that allows you to natively convert JSON text into a set of rows and columns. There are two options for using OPENJSON: with the default schema or with an explicit schema. There are performance implications for each, which I’ll review with some examples.
Dave has some nice tips for people working with JSON data in SQL Server.
Comments closed