Andy Yun

@dgmelkin's Personal Chef | Pure Storage FSA | SQL Server Dev/DBA | Conference Speaker | Dog Lover | Foodie | Novice Guitarist |

[Andy Yun @SQLBek]

You can find me elsewhere: sqlbek . bsky . social

[Deborah Melkin @dgmelkin]

Today is a great day to support Women In Tech (WIT)! We have #datawit ribbons at #PASSDataSummit. I'll put some in the community zone and will be handing them out as I see you.

[way0utwest 🎙️ (He/Him/His) @way0utwest]

Vendors are doing breakfast at the #PASSDataSummit, lots of options: https://t.co/3twsnetNPs

[Jeff Taylor @ReviewMyDB]

All aboard!! #SQLTrain #SQLFamily @sql_r @SQLBek @SQL_JAR thanks to @Simple_Talk and @drsql for sponsoring!

[Andy Yun @SQLBek]

#SQLTrain 2024!

[Andy Yun @SQLBek]

Continuing the #SQLTrain tradition

[Andy Yun @SQLBek]

#blogged Career thoughts: Never Would I Ever... https://t.co/LAZVkbC3rm

[Andy Yun @SQLBek]

@data_dragoness Tempted, we're just on the other side of the river. Though we've been up since 3AM EDT/12AM PT... But... Tempting!

[Andy Yun @SQLBek]

Back here for the week of #PASSSummit. Looking forward to seeing #sqlfamily!

[Deborah Melkin @dgmelkin]

We hadn't even made to baggage claim before the donut adventure began... #sqltrain @SQLBek

[Andy Yun @SQLBek]

@EdwinMSarmiento @dgmelkin Turkey stock

[Andy Yun @SQLBek]

Who the hell came up with the recommendation that 80-90 write pages/sec is "normal" and beyond that is "high?"

[Andy Yun @SQLBek]

Picked up #SebastianTheDog from boarding and he was SUPER HYPER for the following 30 minutes or so. And now that we're all home, he's crashed HARD.

[Andy Yun @SQLBek]

@drsql No idea. Can only see the total aggregate ratings

[Andy Yun @SQLBek]

Odd feedback:

"I didn't attend"

Then why are you leaving me feedback?!

[Deborah Melkin @dgmelkin]

Home after a great #passdatasummit! As an welcome home gift, the tomatoes we brought in for the winter decided to ripen.

[DataGrillen @DataGrillen]

Hey friends

@PASSDataSummit is over, but the data fun will continue with:

Data Left Unattended

A locally presented, remotely attended, data event.

December 7th in a teams call near you

Details and registration here

https://t.co/YcEeKugoT9

[Andy Yun @SQLBek]

Hey fellow @PASSDataSummit speakers. Be sure to log into the Speaker Portal - they're updating survey feedback reports in "realtime" so you can go check out your feedback now!

[Paul White @SQL_Kiwi]

The latest SQL Server cumulative updates (2022 CU 10, 2019 CU23) contain an interesting bug fix, which @josh_the_coder noticed earlier today:

Before this update, in some rare cases, if IDENTITY_INSERT is set to ON, the identity value for the target table is set to the highest value in the source table when joining tables, even if the highest value doesn't satisfy the join condition or the predicate. This update improves the current design and fixes this issue.

Note: To apply this update, you need to set the QUERY_OPTIMIZER_HOTFIXES database scoped configuration to ON. To turn off this update, you can enable trace flag 13193.

The cases turn out not to be so rare:

SQL Server assigns the new identity value when IDENTITY_INSERT is ON by calling an internal function setidentity() in a Compute Scalar plan operator.

It is the placement of this operator in the plan that is the problem. If it appears before a filtering condition, it might be called more times than necessary, using the wrong source values.

In other words, the destination will behave as if identity values had been assigned when they ultimately weren't, due to the later filtering.

In many cases, SQL Server is saved by deferred evaluation of expressions in a Compute Scalar. This is where SQL Server waits until another operator needs the result of the computation before performing it, despite the position of the Compute Scalar operator in the plan. (This optimisation was added in 2005 and only applies to row mode Compute Scalar operators.)

Now, the only operator that will need the result of the setidentity() call is the Insert operator adding rows to the target table with IDENTITY_INSERT. This means any filtering of rows will occur before the insert, and no unnecessary setting of identity values occurs. Yay!

This is all well and good, unless there is a blocking operator in the plan after the setidentity() Compute Scalar but before the filtering.

A blocking operator has to materialize the data it receives in one way or another - in a hash table or sort memory for example - and this materialization necessarily needs the result of the setidentity() call. Oops.

When this happens, setidentity() is called when it shouldn't be. The target table then behaves as if identity values have been used when they were not.

The fix is to ensure the Compute Scalar appears on the 'write cursor' (left) side of the insert plan, where SQL Server has full control, and any user-coded filtering of rows is bound to have already happened.

Without the fix, the Compute Scalar can appear surprisingly early on the 'read cursor' side of the plan where rows are read and processed to see which ones qualify for the update.

The optimizer tends to push Compute Scalars as far to the right as possible for several reasons, including computed column matching. This will never apply to the special setidentity() Compute Scalar of course, but general rules apply generally.

I wrote a toy demo that materializes the setidentity() call in the build-side hash table of a hash join: https://t.co/fpsG3xxznj

Unfortunately, db<>fiddle is still at 2022 RTM so I can't show the fix with query optimizer hotfixes enabled there, but as you can imagine it ensures the Compute Scalar stays in a safe place on the left side of the plan.

For more about deferred expression evaluation see my 2012 article, Compute Scalars, Expressions and Execution Plan Performance https://t.co/BwdJiGVbtc

[Andy Yun @SQLBek]

Our turn to start head home at stupid-o-clock. Loved spending time with #sqlfamily this week.

Thank you @PASSDataSummit team for all of your hard work putting this together.

Cc @dgmelkin #passdatasummit