Our sample query, we change the NUMERIC_ROUNDABORT as ON and then execute it.Īfter the execution of the sample stored procedure, two events have occurred: However, these options cause the creation of a new execution plan.
SQL Server allows changing some settings of the session connections with help of the SET options.
In the second execution of the stored procedure, we will only see the sp_cache_hit event because the query plan isĪnother point about SQL Server stored procedure execution plan recompilation is related to the connection options. Query plan is inserted into the query plan cache. Also, we can see that after the first execution of the stored procedure, its These two events prove that SQL Server stored procedures areĬompiled in their first execution. The sp_cache_insert event indicates that theĮxecution plan is inserted into the query plan cache. We can find out this plan in the query plan tab. At the same time, the query_post_compilation_showplan captures the estimated execution plan and The query_post_compilation_showplan event indicates that the stored procedure was compiled after Order to enable this filter, we will add a filter expression to object_type with the database name filter.ĮXEC GetProductionList ProductIdNumber = 757Īfter the first execution stored procedure, two events is captured and these are : sp_cache_remove: This event occurs when a stored procedure is deleted from the procedure cacheĪt the same time, we will determine a filter that will create only captures the events of the stored procedures.sp_cache_miss: This event occurs when a stored procedure is not found in the procedure cache.sp_cache_insert: This event occurs when a stored procedure is placed into the procedure cache.sp_cache_hit: This event occurs when a stored procedure plan is fetched from the procedure plan cache by the query optimizer.At the same time, this event returns the estimated execution plan of when the query is compiled query_post_compilation_showplan: This event captures the initially compiled plan.That we will create will include the following events: Therefore, we will create an extended event to monitor what goes on behind the scenes of the query plan compilation process of SQL Server stored procedures. Extended Events uses fewer system resources, has advanced filtering and grouping options, and offers numerous events to monitor. Extended Events is a very advanced monitoring tool that helps to capture and reports the various events that occur in the SQL Server.Dynamic Management Views are the special system views that store various information about the SQL Server performance counters and other metrics.To monitor these steps in SQL Server, we will use two Now, let’s analyze this working mechanism of the stored procedures and learn in which phase their first compilation occurs with an example. It is enough to give the only permission to the stored procedure.Ĭontrary to the notion that stored procedures are compiled during their initial creation, stored procedures are compiled on their first execution. Security: SQL stored procedures help us to get rid of dealing with security settings of differentĭatabase objects. Such as, a stored procedure can have wide usage in different applications but only changing the stored procedure will easily affect all applications. Maintainability: Using stored procedures simplifies code maintenance. This methodology aims to avoid an unnecessary query compilation process.Ĭode reuse: The SQL stored procedure helps to avoid rewriting the same codes again and again. So that, all next executions of the same SQL Server stored procedure will use this cached stored procedure. Performance: After the first execution of the stored procedure, the query optimizer creates an execution plan and this plan is stored in the query plan cache. Stored procedures can be listed as follows: This article intends to give comprehensive details on how we can use the recompilation options of SQL Server stored procedures and how they behave when we use these recompilation options.Ī stored procedure is a ready T-SQL code that can be reused again and again.
0 kommentar(er)
