High Compilations per Second
sp_BlitzFirst® Result:
High Compilations per Second
According to sp_BlitzFirst®‘s diagnostics, your SQL Server is having to compile more than 10% of the incoming T-SQL requests.
When SQL Server sees a query for the first time, it has to build a new execution plan for it. This can cause high CPU use for building that plan – building these plans is computationally intensive. Then, SQL Server caches the plan to avoid having to rebuild the wheel again.
Your server, on the other hand, is either unable to cache plans long enough in memory, OR it’s seeing tons of all-new queries every time. Generally, we expect to see >90% of the plans cached on an OLTP server.
To improve performance, ask these questions:
- Are we passing in strings to execute instead of parameterizing our SQL?
- Are we using an outdated ORM?
- Did we recently purge the plan cache?
- Can we use forced parameterization to reuse more plans?
- Are we using table-valued parameters? (Also see part 2 of that post)
- What queries are getting compiled? You can use sp_BlitzCache @SortOrder = ‘recent compilations’ to find out.
For personalized help with getting the right answers for your server, we’ve got SQL Critical Care®.