Having a clear afternoon, I decided to check out another Summit Session for my TIL Holiday Learning Series. This time, I chose Erin Stellato’s (b|t) session – Five Execution Plan Patterns to Watch For.
DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.
Here’s my list of interesting notes & tidbits:
- In SQL 2012 & higher, if no rows have changed and you UPDATE STATISTICS, cached plans are NOT invalidated!
- Session was a good reminder to think more about residual predicates/probe residual. Use F4 on an operator to see for sure if Residual exists, because residuals may not always appear in graphical plan output. Or just always use Plan Explorer (come on – it’s FREE!).
- TF 9130 – This is useful to use while dev/test/tuning (NOT FOR PRODUCTION). It pushes residual out to a discrete operator – an extra filter.
In this session, Erin discusses five different patterns (or anti-patterns). Of those, residual predicates was of the most interest to me. When query tuning, I don’t always look for these, but this session reinforced their potential impact when evaluating query performance. It was also validating to see the other anti-patterns Erin spoke of, are already part of my mental checklist. This was definitely an awesome session for any intermediate to advanced query tuner!