In this article, we will explain what compilations and recompilations are, and give recommendations for creating reusable queries to keep the pressure off your processor.
What is a compilation?
A compilation is the process when a stored procedure’s query execution plan is optimized, based on the current database and database objects state. This query execution plan is then stored in cache and can be quickly accessed.
When a query is executed, it’s sent to the parser first. The parser checks the query syntax and stops the execution if the syntax is incorrect.
|
1 2 3 4 5 6 |
SELECT * FROM person.address; INERT INTO Person.Address1 (AddressID, AddressLine1, AddressLine2) VALUES(1, N'1970 Napa St.', NULL) |
If multiple syntax errors exist in the submitted query (in this example both in the first and the third line), the parser will stop when it reaches the first one.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘*’.
When the syntax is correct, the query goes further to the algebrizer. The algebrizer finds all objects used in the query, verifies their names, finds the data types used, checks whether aggregate functions are used, and based on the information collected creates a syntax-based optimization.
A query compilation consists of the processes executed in the parser and algebrizer. A complied plan is saved in cache.
What is recompilation?
A recompilation is the same process as a compilation, just executed again. If the database structure or data change significantly, a recompilation is required to create a new query execution plan that will be optimal for the new database state and ensure better procedure performance.
A recompilation degrades SQL Server performance, as SQL Server is performing the same action multiple times, instead of using its resources for other important actions. If you could use the cached execution plan, instead of recompiling the stored procedure again, that would make query execution faster for the time needed for a recompilation.
Therefore, it’s recommended to have reusable query execution plans.
Recompilation cannot be completely eliminated, but you should watch for a large number of recompilations which indicates that queries are reused, but their query execution plan is not. Also, a large number of compilations should be investigated, as that means that new queries are excessively compiled, and not reused. In some cases, it’s recommended to check the compilation/recompilation ratio.
If the recompilations occur more than expected, find the stored procedures that are frequently recompiled. You can use SQL Profiler, or a SQL Server monitoring tool. Then determine why the stored procedure is frequently recompiled instead of reused from cache, and finally fix the problem.
What is parameterization?
One of the mechanisms that SQL Server uses to provide compiled queries to be reused is using parameterization. For example, we will create a query execution plan for the following query that contains the specific value for the AddressID.
|
1 2 3 4 |
DELETE FROM Person.Address1 WHERE AddressID = 100000 |
If no parameterization was used, it would mean that each time the same query is executed, but with different values for the AddressID, it would have to be recompiled. As shown in the query execution plan, this is not the case, as the @1 parameter is used instead of the exact value, so the query execution plan can be reused for every AddressID value.
When is a query recompiled?
As described above, a compiled query execution plan is stored in cache.
The queries are automatically recompiled when:
A query is executed using a RECOMPILE query hint.
There are three query hints that define query execution plan use. Query hints are executed on the statement level, so they don’t affect the whole stored procedure or query.
RECOMPILE – specifies that after the query is executed, its query execution plan stored in cache is removed from cache. When the same query is executed again, there will be no existing plan in cache, so the query will have to be recompiled. This is an alternative to using the WITH RECOMPILE clause in stored procedures; it is useful if you want to recompile only some of the statements in the stored procedure, not all of them.
|
1 2 3 4 5 6 |
CREATE PROCEDURE <name> Query1 Query2 OPTION (RECOMPILE) Query3 |
For example:
|
1 2 3 4 |
SELECT * FROM Sales.SalesOrderDetail OPTION (RECOMPILE) |
KEEP PLAN – specifies that a query execution plan is not recompiled when it normally is. When the table index column changes due to an INSERT, DELETE, UPDATE, or MERGE statement, the query execution plan is recompiled. The number of changed rows that triggers a recompilation is different for temporary and permanent tables and depends on the number of rows in the table. The threshold value is higher for permanent tables. For example, if a temporary table has less than 6 rows, the plan will be recompiled if 6 new rows are added. For the permanent table with 6 rows, 500 new rows must be added to trigger a recompilation. The KEEP PLAN raises this threshold, so the recompilations occur less frequently.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE #t (Id INT, Address1 [nvarchar](60), Address2 [nvarchar](60), Town [nvarchar](30)) INSERT #t SELECT [AddressID], [AddressLine1], [AddressLine2], [City] FROM Person.Address SELECT count(*) FROM #t WHERE ID = 37 OPTION (KEEP PLAN) |
KEEPFIXED PLAN – specifies that a query execution plan is never recompiled due to index column changes and changes in statistics. When this query hint is used, the plan is recompiled only if the schema of tables used in the query is changed or the sp_recompile stored procedure is executed on these tables. The example above applies here as well; the only difference is OPTION (KEEPFIXED PLAN) instead of OPTION (KEEP PLAN).
A WITH RECOMPILE option us used in a CREATE PROCEDURE statement or in an EXECUTE statement when the procedure is called
|
1 2 3 4 5 6 7 8 9 |
CREATE PROCEDURE Person.AddressLines @AdrID INT = '32' WITH RECOMPILE AS SELECT * FROM Person.Address WHERE AddressID = @AdrID; GO |
The query execution plan is not stored in cache after the stored procedure is executed, so it will be recompiled each time it’s executed
When executing a stored procedure with a WITH RECOMPILE option in the EXECUTE statement, a new query execution plan is created and used for this specific execution, but it’s not stored in cache. If there is already a plan in cache for this specific stored procedure, it’s intact.
|
1 2 3 |
EXECUTE dbo.uspGetBillOfMaterials WITH RECOMPILE |
The sp_recompile system stored procedure is used
The stored procedure removes an existing query execution plan for a specific stored procedure or query from cache, so they are recompiled the next time they are called.
|
1 2 3 |
EXEC sp_recompile N'dbo.uspGetBillOfMaterials' |
When the stored procedure is executed, the following message is shown
Object ‘dbo.uspGetBillOfMaterials’ was successfully marked for recompilation.
In this article, we explained what compilations, recompilations, and parameterization are. We showed how to recompile a query using T-SQL query hints, options, and stored procedures. In the next part of this article, we will show how to detect frequently recompiled queries.
She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.
Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.
View all posts by Milena "Millie" Petrovic
- Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports - September 12, 2014
- Using custom reports to improve performance reporting in SQL Server 2014 – the basics - September 8, 2014
- Performance Dashboard Reports in SQL Server 2014 - July 29, 2014