Sql with recompile8/2/2023 ![]() ![]() If you only want individual queries inside the stored procedure to be recompiled, instead of the complete stored procedure, specify the RECOMPILE query hint inside each query you want recompiled. In the following code, sp_recompile will cause recompilation of the Sales.Customer procedure for the next time it is run.ĮXEC dbo.ProductByDate(2012) WITH RECOMPILEĬREATE PROCEDURE dbo.uspProductlist varchar(30) It occurs by deleting the existing query plan from the cache, forcing a new plan to be released for the next procedure run. The sp-recompile system stored procedure forces a recompile of a stored procedure for the next run. In the following section, we will discuss the various available methods forcing SQL Server to recompile the stored procedure for next run(s). Therefore, this is your right to recompile a stored procedure for each call in specific condition. Forcing a stored procedure to recompileĪlthough re-compilation is helpful in preventing from performance plummeting, unwise recompilation can cause more time consumption. Consequently, you noticeably realize a longer time to complete the request. Second, it creates new query plan and stores it in memory for next run. First, SQL Server follows the previous cached plan which is certainly saves the execution time and affects the performance. This time, execution would be divided into two parts. The next time you decide to search on another column, SQL Server blindly uses the previous plan which is not valid for the current parameters. As long as you follow the cached plan (searching on the same column), SQL Server finds no changes and uses the cached plan. In both scenarios SQL Server creates a query plan at the first procedure call, and stores it in memory. On testing, you discover that the stored procedure occasionally takes a longer than expected time to execute. ![]() Scenario 2: You plan to design a complex multi-statement stored procedure. Performing multiple searches, you find that the first is fast, and the subsequent searches are much slower. The performance (execution time) of each search must be approximately the same, but it isn't. The parameter is examined using a CASE block, and then executed, depending on the specified column. Scenario 1: In a search page users can look up by any of several columns after passing a parameter indicating which column must be searched. To clarify more about the effects of compilation and recompilation, note the following scenarios: ![]() Then a new plan cache and possibly a different query plan produced. If one of the checks fails, the plan cache would be deleted and the statement corresponding to the query plan or the entire query would be compiled again. This compiled plan is stored in a part of SQL Server's memory named the "plan cache".īefore SQL Server begins executing of a query plan, the server checked for the validity (correctness) and optimality of the plan. Before a query, batch, stored procedure, trigger or dynamic SQL statement begins execution in SQL Server, it is compiled into a plan which is called "Query Plan". ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |