Press "Enter" to skip to content

Category: Internals

Performance Impact of Foreign Keys with Non-Default ON UPDATE or ON DELETE

Published 2021-01-06 by Kevin Feasel

Hugo Kornelis continues a dive into foreign keys:

Welcome to part fifteen of the plansplaining series. In the three previous parts I looked at the operators and properties in an execution plan that check a modification doesn’t violate foreign key constraints. That part is done. But I’m not done with foreign keys yet.

We normally expect foreign keys to throw an error on violations. But that’s actually only the default option: they can also be set to be self-correcting. This is done using the ON UPDATE and ON DELETE clauses, which provide the user with several choices on how to handle child data that would become orphaned, and hence violate the constraint, as a result of a change in the parent table.

Read on to see how these operate in SQL Server.

Comments closed

PFS Contention and Heaps

Published 2021-01-05 by Kevin Feasel

Uwe Ricken continues a series on heaps in SQL Server:

The PFS page “can” become a bottleneck for a heap if many data records are entered in the heap in the shortest possible time. How often the PFS page has to be updated depends mostly on the data record’s size to be saved.

This procedure does not apply to clustered indexes since data records in an index must ALWAYS be “sorted” into the data volume according to the defined index value. Therefore, the search for a “free” space is not carried out via the PFS page but via the value of the key attribute!

Read on for more detail.

Comments closed

The Merge Interval Operator

Published 2020-12-15 by Kevin Feasel

Hugo Kornelis looks at another execution plan operator:

The Merge Interval operator reads dynamic seek range specifications, checks to see if their specified ranges overlap, and if so combines the overlapping ranges into one new range.

One typical use case is for a query that uses multiple BETWEEN specifications, connected with OR. When these ranges overlap, they must be combined into a single range. This saves performance, but more important is that it prevents rows that satisfy both range specifications from being returned multiple times. When the boundaries of the BETWEEN are given as constants, the optimizer analyzes for overlaps and combines ranges if needed when compiling the query. But when the boundaries of the BETWEEN specifications are only known at run-time (variables, column references), the Merge Interval operator is used for this task.

Click through to see how it works.

Comments closed

Execution Plans: Foreign Keys and Deletion

Published 2020-12-08 by Kevin Feasel

Hugo Kornelis continues a series on execution plans:

In the previous part, we looked at foreign key checking when data is inserted. Inserts in the referencing (child) table can, by definition, not cause foreign key violations, so no checking is done in those cases. But for inserts in the referenced (parent) table, SQL Server needs to check that the inserted value does indeed exist. We saw how both the Nested Loops and the Merge Join operators have some specific optimizations to do that check as efficient as possible; we also saw how the Assert operator does the actual checking.

When deleting data, the reverse is true. We can delete rows from the child table without verification, but deletes from the parent table are subject to a check.

And those deletes can get crazy expensive.

Comments closed

Scalar Functions Not Allowed in T-SQL

Published 2020-12-07 by Kevin Feasel

Hugo Kornelis has a list for us:

But the optimizer also has a set of scalar functions available that are not allowed within T-SQL code, but that it can use in properties of some operators. The table below lists all these functions, with a description of their input parameters and their result.

You can’t use these directly, but they are useful nonetheless.

Comments closed

How Foreign Keys Appear in Execution Plans

Published 2020-11-27 by Kevin Feasel

Hugo Kornelis shows us how foreign key contraints modify certain execution plans:

The top left of this execution plan looks very unsurprising. A Clustered Index Scan to read all rows from the #Products temporary table, and those rows are then passed to a Clustered Index Insert operator for insertion into our permanent table dbo.Products. But wait? Why is there no table or index name listed below the operator name? That’s actually because the operator has not one but three items in its Object property. When a Clustered Index Insert targets just a single index, SSMS can work out the name and show it. But when Clustered Index Insert targets multiple objects, SSMS plays it safe and displays none.

Read on to learn more.

Comments closed

The Filter Operator

Published 2020-11-20 by Kevin Feasel

Hugo Cornelis continues a dive into execution plan operators:

The Filter operator is used to remove rows from a result set that do not satisfy a specified condition.

Its most common usage is to implement a WHERE clause, although many elements of a WHERE clause get implemented in other ways (e.g. by pushing them down into an Index Seek or Index Scan operator), and Filter operators can also be used when the optimizer transforms other query elements into any type of filtering.

The Filter operator can only verify conditions based on the data in the current row; if data from different rows needs to be compared for the filter, the optimizer generates an execution plan that first adds the data to be compared to the row, or it uses different operators.

Click through for Hugo’s normal in-depth look.

Comments closed

The Sequence Project Operator

Published 2020-10-26 by Kevin Feasel

Hugo Kornelis continues on a quest:

The Sequence Project operator computes values for the “ranking functions”:  functions where the results depend on other rows in the result set, such as ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

A Sequence Project can be considered as somewhat similar in function as Compute Scalar: both operators add new columns to the data based on expression. But Compute Scalar works on expressions other columns from the same row and constant values as input. Sequence Project computes expressions that are based on preceding rows in the data stream as their input.

Read on to learn more about what this operator does and how it works.

Comments closed

Resolving Call Stack Symbols on SQL Server 2019

Published 2020-10-20 by Kevin Feasel

Paul Randal takes us through a change to SQL Server 2019:

After beating my head against the proverbial wall for an hour, I wondered if I had the wrong symbols somehow. I checked with the excellent SQLCallStackResolver tool from GitHub (authored by Arvind Shyamsundar from the Product Group) and that worked fine with the symbols I had, so it had to be something within SQL Server.

Read on to see the answer.

Comments closed