Before executing SQL statement, SQL server checks for the validity and correctness of the query and generates the execution plan if it's not all already available. No need to say compilation is very CPU intensive process. Before executing the already compiled SQL statement, SQL server perform several other checks and if any of these checks fails, SQL server perform again compilation of SQL statement. Such compilations are known as recompilations. Following are some reasons for recompilation:
- Schema change
- Statistics change
- Deferred compile
- SET option change
- Temporary table change
- Stored procedure created with RECOMPILE query hint or OPTION(RECOMPILE)
In SQL Server 2000, if you need to recompile a portion of stored procedure, you need to compile the whole stored procedure, however in SQL Server 2005, you can perform recompilation at statement level. Less recompilation means less CPU consumption and less contention.
For more information, see Identifying Recompilation
No comments:
Post a Comment