For years, I hated troubleshooting servers with high CPU usage caused by queries constantly asking for new execution plans. Hated it. SQL Server just doesn’t make it easy to find queries with recompile hints.
Then Erik Darling’s sp_HumanEvents came along.
And now troubleshooting frequent compilations is as easy as this:
EXEC dbo.sp_HumanEvents @event_type = ‘compilations’, @seconds_sample = 60;
Which produces this:
ARE YOU KIDDING ME? It gives me the parameters, the number of times the query compiled, how much time was spent doing those compilations, and more! It’s a SINGLE LINE OF T-SQL that sets up an Extended Events session,