You can find me elsewhere: sqlbek . bsky . social

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.

Vendors are doing breakfast at the #PASSDataSummit, lots of options: passdatacommunitysummit.com/…

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

#SQLTrain 2024!

Continuing the #SQLTrain tradition

#blogged Career thoughts: Never Would I Ever... sqlbek.wordpress.com/2024/11…

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

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

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.

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

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 meetup.com/datagrillen/event…

Odd feedback: "I didn't attend" Then why are you leaving me feedback?!

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!

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: dbfiddle.uk/v7Ci0_pU 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 sql.kiwi/2012/09/compute-sca…

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

Dinner at Crab Pot with #SQLFamily after #PASSSummit #PASSDataSummit #PASSDataCommunitySummit

🙌 That's a wrap on #PASSDataSummit 2023! Thank you to our #datacommunity, attendees, speakers, sponsors, exhibitors, and crew for bringing Summit 2023 to life. We hope you leave feeling motivated, recharged, and inspired to take on the world.

Getting ready to kick off the day 3 sessions of #PASSDataSummit with the last session of our learning pathway, "Solving Real World SQL Server Problems" by talking about Conquering the Monster Proc: Combatting Legacy Code in Room 611.