Red Flags in Your Query (T-SQL Tuesday #200)
When I'm looking at a query, I bet it's bad if I see... a shopping list of red flags I've learned to spot, from AI-generated code to SSMS map mode gut checks.
Performance
9 posts
Async stats update causing blocking
An index rebuild quietly blocked an async stats update, and the whole server started timing out. Here is the lock-partitioning repro and the SQL Server 2022 fix.
Finding and Triaging Scalar UDFs Across Your Environment
You've learned why Scalar UDFs are slow and how to remove them. The last question: where do you even start? Here's how I triage them across a whole environment.
Fixing Scalar UDFs: SCHEMABINDING, Inlining and ITVF Rewrites
Nuke them from orbit, or inline them. The practical ways to neutralize Scalar UDF performance pain, from a free SCHEMABINDING win to a full ITVF rewrite.
Measuring the Scalar UDF Performance Cost
A Scalar function that does literally nothing still runs over 20 times slower. Here is the proof, measured four different ways.
How Scalar Functions Prevent Parallelism
Consultants hate me for this one weird trick. Because I'll be uncovering all the mess Scalar functions can cause in a series of articles.
KEEP PLAN Demystified
Ever wondered what the KEEP PLAN hint actually does? The docs are vague, so I tested it against every recompilation threshold to find out.
Query Store default settings
Query Store comes with default settings - but are they what the documentation says? Let's find out using a simple Extended Events session.
Generate XML documents efficiently
Believe it or not, there is a wrong and correct way when it comes to generating XML documents in SQL.