TheSQLGuru (5/29/2015)

---

Grant Fritchey (5/28/2015)

---

TheSQLGuru (5/28/2015)

---

Also, I was leery of adding OPTION (RECOMPILE) for something that runs that often.

This is SIGNIFICANTLY misguided, IMHO! I will jump through EXTRAORDINARY hoops these days to trade CPU ticks for query perf (especially IO), and that is exactly what you do with OPTION (RECOMPILE) or other means to avoid getting suboptimal plans when you have either data value skew or widely-varying filtering (think @startdate and @enddate on a report sproc for example). I can count on one hand the number of times I have seen compilation locking issues or CPU issues related to compilations in the wild in 20 years of SQL Server consulting but can't possibly count the times where OPTION (RECOMPILE) et al made performance WAY better AND consistent.

BTW, one of those times was when an admin set Cost Threshhold for Parallelism to 0. Chew on that one for a minute or two. :hehe:

Maybe he was trying to ensure good performance on Columnstore indexes.

Interesting thought, but this was about 7 years ago or so. It was a mixed-mode server (OLTP/OLAP on same databases). I changed CTFP to 15 and CPU made a step-change drop. It was pretty cool. 😎