Press "Enter" to skip to content

Category: Query Tuning

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

OPENJSON Performance and Schemas

Published 2022-08-22 by Kevin Feasel

Dave 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

Bulk Insert into Azure SQL DB using Python

Published 2022-08-19 by Kevin Feasel

Jose Manuel Jurado Diaz shares some customer notes:

Today, I’ve been working on a service request that our customer wants to improve the performance of a bulk insert process. Following, I would like to share my experience working on that.

Our customer mentioned that inserting data (100.000 rows) is taking 14 seconds in a database in Business Critical. I was able to reproduce this time using a single thread using a table with 20 columns.

A lot of this advice also applies to on-premises SQL Server and relates to using bulk inserts and picking good batch sizes. Similar advice to what we’d be doing with SQL Server Integration Services or any other ETL/ELT process, tailored to Python.

Comments closed

Improvements to Parameter Sensitive Plan Optimization

Published 2022-08-16 by Kevin Feasel

Erik Darling is not good at being on vacation:

Several weeks back, I blogged about a missed opportunity with the new parameter sensitive plan feature. At the time, I thought that there was indeed sufficient skewness available to trigger the additional plan variants, and apparently some nice folks at Microsoft agreed.

If we step back through the old demo, we’ll get different results.

Click through for those results.

Comments closed

When Estimated and Actual Plans Differ

Published 2022-08-12 by Kevin Feasel

Brent Ozar notes that estimated plans are not guarantees:

A reader posted a question for Office Hours:

Hi Brent, What is your take on Hugo Kornelis’s explanation of execution plan naming. As her his explanation, estimated exec plan is simply an execution plan whereas actual execution plan = execution plan+run-time stats. Do you agree that the naming is flawed and confusing? – Yourbiggestfan

Click through to see examples of when estimated plans might look different from actual plans.

Comments closed

Query Splitting in Entity Framework

Published 2022-07-28 by Kevin Feasel

Guy Glantser doesn’t pull punches:

Recently, while working with a customer and tuning some queries, we spotted a query that seemed odd. Something about it wasn’t right. After some more investigation, the developer recognized the query as a one generated by Entity-Framework and using the SplitQuery feature. This was new to me. It’s the first time I encountered this feature, so I went to learn about it.

Now that I know what it is and how it works, I can tell you that it’s a terrible feature in most cases. Developers should avoid using it, unless there is a good reason to use it (which I doubt).

I thought based on the title that this was something totally different. Reading what Guy has to say about it, I fully agree.

Comments closed

The Cure to Scalar Functions

Published 2022-07-25 by Kevin Feasel

Tom Zika has a cure:

In the first two parts, we have seen why the Scalar functions (UDFs) are a problem for the performance. So how do we deal with it now that we know it’s a problem?

There is only one solution:

I say we take off and nuke the entire site from orbit. It’s the only way to be sure.
— Ellen Ripley

Tom’s ideas are intriguing to me and I wish to subscribe to his newsletter.

Comments closed

Another Knock against OFFSET/FETCH

Published 2022-07-12 by Kevin Feasel

Erik Darling won’t play fetch:

The two methods we’re going to look at are OFFSET/FETCH and a more traditional ROW_NUMBER query.

As you may have picked up from the title, one will turn out better, and it’s not the OFFSET/FETCH variety. Especially as you get larger, or go deeper into results, it becomes a real boat anchor.

Click through for the details.

Comments closed

Join Removal Due to Foreign Key Constraint

Published 2022-07-12 by Kevin Feasel

David Alcock shows a performance benefit from having a foreign key constraint in place:

Foreign keys are used in database design to enforce referential integrity but they also have some performance benefits as well that you might not necessarily notice unless you’re looking into your execution plans.

Let’s take the following query using the AdventureWorks2019 sample database where I’m selecting the BusinessEntityID and JobTitle from the HumanResources.Employee table and by using an inner join I’m only returning rows that have matching values (BusinessEntityID) in both tables:

There are specific rules to table elimination but if you meet the criteria, it can save you a bit of CPU time and I/O.

Comments closed