Writing a CTE name two times can mean doing the work two times. Two small demos show when a temp table helps and when it only adds cost.
Plan Cache Key Lookups: Finding the Worst Ones With XQuery
Not every key lookup needs fixing. Rank statements by reads, find the lookups with XQuery, then test the smallest index change on one call.
Computed Column UDFs: Why Every Query on the Table Goes Serial
A computed column that calls a scalar function can block parallel plans for queries that never read it. Here is how to spot it and test a fix.
Intra-Query Parallel Deadlock: When One Query Blocks Itself
No error, no deadlock graph, just a report that is slow on some runs. Here is a demo where one query deadlocks with itself, and how to fix it.
A Separate Clustered Index for a Nonclustered GUID Primary Key
The primary key and the clustered index are two different jobs. This demo keeps the GUID as the public ID and clusters on an identity column instead.
Optimized Plan Forcing: Cutting Compile Time in SQL Server 2022
Check optimized plan forcing in SQL Server 2022 through scoped configuration and Query Store replay flags, then compare compile duration.
SQL SERVER – A Quick Look at Performance – A Quick Look at Configuration
Before I answer a performance question, I need to know the environment. Here is how I take a quick look at configuration to get the full picture fast.