SQL Server Performance Office Hours Episode 51

Description:
## Summary
In this video, I delve into a series of technical questions and challenges faced by the SQL community. Erik Darling from Darling Data shares insights on computed columns, index usage, memory grants, and blocking issues—explaining why certain optimizations might not always yield expected results. The discussion covers topics like parameter sniffing, query plan stability with Query Store, and the nuances of CPU utilization after implementing dynamic SQL solutions. I also touch upon my recent endeavors in automating content creation for YouTube videos and WordPress blog posts using local LLMs, highlighting how these tools are transforming content production processes. Additionally, I announce upcoming speaking engagements at various data conferences across North America and Europe, offering attendees free access to advanced T-SQL training materials.

## Topics
`SQL Server`, `computed columns`, `index optimization`, `query performance`, `execution plans`, `memory grant feedback`, `parameter sensitivity`, `query store`, `T-SQL`, `database indexing`, `SQL Server optimizer`, `computed column usage`, `query rewriting`, `data spilling`, `SQL Server maintenance`, `database training`, `consulting services`, `video transcription`, `YouTube content management`, `WordPress integration`

## Chapters
- **00:00:00** - Introduction
- **00:03:45** - Parameter Sniffing Solutions
- **00:10:37** - Blocking Analysis
- **00:18:52** - Episode Wrap-Up and Future Plans

━━━━━━━━━━━━━━━━━━━━━━━━━━
???? TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-ai-ready-with-erik?coupon=AIREADY

SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-server-performance-engineering?coupon=ENGINEERING

Learn T-SQL with Erik
https://training.erikdarling.com/learn-t-sql-with-erik?coupon=ADVANCEDTSQL

Everything Bundle:
https://training.erikdarling.com/?coupon=SPRINGCLEANING

━━━━━━━━━━━━━━━━━━━━━━━━━━
????️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━

Need SQL Server performance help?
https://training.erikdarling.com/sqlconsulting

━━━━━━━━━━━━━━━━━━━━━━━━━━
???? CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━

Ask questions at Office Hours
https://erikdarling.com/officehours/

Become a channel member
https://www.youtube.com/@ErikDarlingData/join

━━━━━━━━━━━━━━━━━━━━━━━━━━
⁉️ QUESTIONS
━━━━━━━━━━━━━━━━━━━━━━━━━━

* I created a computed column with a specific formula to match a common WHERE clause pattern, added an index on it, and the statistics show good histogram data. But the optimizer still expands the formula and does a scan instead of seeking on my computed column index. I'm not using any functions that would prevent matching. What causes the optimizer to ignore a computed column that perfectly matches the expression?

* This query spills about 2GB to tempdb, but the estimated memory grant looks reasonable. Why would SQL Server underestimate memory like this, and when does memory grant feedback fail to fix it?

* Query Store shows a stable plan and low average duration, but users still report random slowness. What does Query Store not tell us that could explain this?

* I fixed parameter sniffing with dynamic SQL, but now CPU is higher overall. Did I actually improve performance or just change the failure mode?

* sp_WhoIsActive shows short blocking chains, but users still experience big delays. How can blocking be minimal but user latency still be high?