I recently asked on Twitter an exciting question about OR vs IN. While I got lots of good answers, there were an equal amount of wrong answers.
SQL Server 2022 – Cardinality Estimation (CE) Feedback for Performance
SQL Server 2022introduces an enhanced Cardinality Estimation (CE) feedback feature as part of the intelligent query processing family.
Parameter Sensitive Plan optimization in SQL Server 2022 – SQL in Sixty Seconds #200
SQL Server 2022 introduces Parameter Sensitive Plan Optimization (PSPO), a feature that revolutionizes the performance of parameterized queries.
Prepared Statements and Plan Reuse From Application Code
Inspect prepared statements, handle lifetimes, parameter types, cached Prepared plans, and the limits of optimize for ad hoc workloads.
Live Plans for Running Queries With sys.dm_exec_query_statistics_xml
Read sys.dm_exec_query_statistics_xml for live plans, compare profile counts with estimates, and interpret partial progress carefully.
Remove Key Lookup – SQL in Sixty Seconds #194
Today, we are going to see one more trick from the book – Remove Key Lookup and improve the performance of queries.
Splitting OR Conditions Into UNION ALL for Index Seeks
Test splitting OR conditions into disjoint UNION ALL branches, preserve NULL behavior and row identity, and compare actual index access.