It’s learning time again…with my first time at SQLBits. Sadly it’s just virtual as there is still a corporate travel ban due to Covid, but I had a great first day and learned a ton.

My wife just approached me this morning telling me “Hey if you gonna learn all day take this as a chance for your blog and share what you’ve learnt there…but keep it short and simple and don’t write essays”. With the latter part…I have my difficulties…however I give it a try so let’s go.

The precon session I visited today was by the great Brent Ozar on “Mastering Parameter Sniffing”…a topic which I have no in-depth-knowledge yet.

So without further ado here are my takeaways and quick wins/learns…ah stop
Disclaimer: This is my understanding…could be wrong…don’t blame Brent but me if you are misled

• The course today was all about stored procedures…however you can encounter and also tackle Parameter Sniffing with no procedures involved as well
• Typically parameter sniffing is a situation with a query which can return big amounts of data or small amounts with different executions and different parameters
  • for each of the executions another variant maybe helpful: either doing an index seek and a key lookup or doing a clustered index scan for example
  • depending on the first execution either the query plan for the small amount or the one for the big amount is saved in cache
  • You hit problems if the execution of a different parameter takes too long and uses a highly unsuitable plan. In the best case…your users might not be complaining about query execution time but you would waste resources by doing extra work which might hurt you in the end as well.
  • Brent explains it in more detail (and probably better than me) for example here
• Index Tuning can help to reduce the “stench” of parameter sniffing
  • However in the real world we ofentimes can’t create a perfect covering index
  • need to deal with parameter sniffing anyway then
• Suitable techniques to circumvent parameter sniffing involve
  • change your code
    • try to isolate a part of your query into a CTE (might work..but not guaranteed because CTEs are no optimization “fences” (fancy word I have just learned) in SQL Server but in other databases like PostgreSQL for example.
    • if the CTE doesn’t work isolate a part of your query into a temp table
    • rewrite your procedure to call two different procedures for different plans
    • rewrite your procedure to change the query text and force a different plan (called “comment injection” by Brent)
  • add hints
    • OPTION(OPTIMIZE FOR (@Param='Value'....)) can be used to force query optimization for a specific value
      • downside is that the value could create a different plan over time which is not the one we want (adding technical debt)
    • OPTION(OPTIMIZE FOR UNKOWN) can be used to optimise the query for a mediocre value from the density vector of the statistics –> could help that query execution time does not wildly vary
    • OPTION(RECOMPILE) does get you a fresh plan optimized for the current parameters with each execution…however it is associated with a higher usage of CPU…Brent’s tip: consider it if your query runs once a minute or less
  • use (un)documented traceflags for that query
    • there’s a big list online maintained by ktaranov
    • undocumented traceflag 8671 caught my attention, as it deactivates the time quantum for query optimization…I will have to play with that but also be careful regarding the blast radius 😉
• Further reading for me / Experts mentioned by Brent
  • Guy Glanster seems to have invested a lot of time to write code to fight parameter sniffing…unfortunately I haven’t found much online…apart from this
  • Paul White on Temp Table Caching…okay this is not exactly related to parameter sniffing but why a temp table might not fix it or might just act like OPTION(RECOMPILE)
    • I recognize that Paul White is very knowledgable and a great resource
    • However to be honest I have just skimmed his articles so far as they are often deep dives which seem to be overwhelming
• Querying a partitioned table leads to longer plan compilation time
• SQL Server won’t use filtered indexes when parameters are involved
  • Reason: Execution plan has to be reusable for different parameters
  • Execution Plan contains a WARNING for unmatched indexes if an index couldn’t be used
• The new feature Adaptive Memory Grants (SQL 2019) can make parameter sniffing issues worse
  • would be good if the query would be executed with exactly the same parameters over and over again…but that rarely happens
  • Brent explains it great here

That’s all folks….a lot for me to still revisit and read…maybe you got something useful out of my notes ;-).