SQL Server query developers, listen up! Query execution time is not everything you should be worried about. You need to examine the parse and compilation time for each of your queries too.
When you run a query, the SQL Server engine executes it. You get a runtime in SSMS or whichever tool you’re developing in. Take this “fine” example of a dashboard query from an ERP system.
If you go to the Messages pane to look for more details, it’s pretty sparse by default.
This runtime is not the complete picture of what it is doing while processing your query. Run the command “SET STATISTICS TIME, IO ON”. It provides greater level of information in the Messages pane in SSMS. Run that command, then re-select and execute your query.
Now open the Messages pane, and you are presented with much more information.
Do you see how it now breaks out parse and compilation time from execution time? In this example, this query took 3.4 seconds to parse and compile. That is an eternity.
We can also see that the query went parallel. The total execution time was 7.7 seconds, but we have approximately four times that of CPU time. We can estimate that our actual max degree of parallelism was across four CPUs.
You might even have some queries that spend an eternity compiling and then the execution time is very brief!
What is interesting is that when we look at our execution plan via SSMS, it does not directly display any item related to compilation in the root node of the plan. (It does show a lot of other garbage, but that’s a topic for a different day.)
It does show that our degree of parallelism is four, just as we estimated. (There are some execution plan tools that do show this, but shall remain nameless.)
Edit the plan’s raw XML, however, and we can see some more details missing in the GUI.
At the top of the plan XML under a second called QueryPlan is an entry for CompileTime, measured in milliseconds.
Now, in many cases where queries contain any number of suboptimal items, our compilation time can actually (sometimes greatly) exceed the runtime of the query! This list includes:
- Significant amounts of business logic embedded in the code
- Missing indexes or outdated statistics
- Use of dynamic SQL
- Querying views
- Views joined to other views
- UDFs and TVFs, especially in query predicates
The tough part is that as the query complexity grows, so does parse and compile time. The use of suboptimal constructs amplifies it. With that compilation time comes a spike in CPU and some memory consumed while it compiles. If the query parse and compile time reaches two seconds, an internal engine threshold, it will hit a timeout and stops the process and just goes with what it has at that time. It might be an optimal plan… or it might be a bad plan. (If the engine encounters some of these items listed above or a lot of other scenarios, it can recompile, which can cause the parse and compilation time to grow even higher.)
It gets worse. Many SQL Server monitoring tools usually do not capture the compilation time, but just capture the execution runtimes. This omission means that a significant portion of the actual runtime of the overall command could be completely missing from any monitoring telemetry you might have. With a diagnostics query, you can view the compilation time for all queries pulled from the execution plan cache. Thanks Jonathan for the query!
At a micro-level, review your query parse and compilation time as you develop your queries. If it suddenly spikes or is elevated to begin with, undo the last change and review before deploying the code to production.
At a macro-level, review the top compilation offenders. You might be surprised at what you find! At that point, start reworking the queries that you have under your control and cut that time down! An easy bandaged solution is to repackage the queries as a stored procedure, as you might just find your plans get re-used much more frequently, and that compilation time impact is cut to zero once a plan is developed. (It won’t fix the inefficient commands, but can at least reduce that compilation time.)
If high compilation times are encountered in third-party databases, take this raw detail and go to them to present the findings. They need to address these inefficiencies as soon as they can, or else you will eventually be needing to add more CPU cores to this server just to handle the inefficient code, all of which costs your organization money in the form of new SQL Server licensing.