I'm working on the SQL Server performance area and I want to know that is the Parameter Sniffing problem resolved in SQL Server 2022 completely ?
What trace flags are useful in this area ?
Thanks in advance.
asked Dec 13, 2022 at 10:22
Add a comment
|
1 Answer
No it is not.
See this article Brent Ozar wrote about it.
https://www.brentozar.com/archive/2022/08/pspo-how-sql-server-2022-tries-to-fix-parameter-sniffing/
Small problem: we still have sniffing. Try running it for, say, Reputation = 3 and review the actual query plan ... then free the cache and see the difference.
Medium problem: direct equality searches only. The PSPO implementation is more of a down payment than a full payment. It only works for equality searches, not range searches. One of the most common parameter sniffing problems is the date range issue: queries with start & end date parameters, like this.
Large problem: monitoring software is doomed. If you look closely at the sp_BlitzCache screenshots, there’s a huge, massive, giant, unbelievably big new problem.... SQL Server can no longer tell where the query came from. It’s like every running statement is suddenly dynamic SQL with no parent... If you think that’s bad, there’s no way to join to the underlying statements. That’s not just bad, it’s poor. Really, really poor.
