![]() SELECTSCHEMA_NAME(SCHEMA_ID) SchemaName, nameProcedureName,last_execution_time LastExecuted,last_elapsed_time LastElapsedTime,execution_count ExecutionCount,cached_time CachedTimeFROMsys.dm_exec_procedure_stats ps JOINsys.objects o ONps.object_id = o.object_idWHEREps.database_id = DB_ID() Īre you looking for an answer to another query? Contact our technical support team. Here is a short script you can use to find out which stored procedure your database has cached along with its various associated properties. When you run a stored procedure, you use the first method, and when you run it for the first time, it uses a different method to prepare it for recompilation. When a stored procedure is run for the first time using this technique, the cache is cleared from the procedure cache. This is where you can create your stored procedure for recompilation. Method 2: sp_recompile for Recompiling Stored Procedures EXECStoredProcedureName WITHRECOMPILEĪs soon as it completes the task, this will recompile the stored procedure. While your stored procedure is running, you can simply recompile it. You don’t have to create the stored procedure from scratch to recompile. Two Simple Methods for Recompiling Stored Procedures: However, changing a single stored procedure will easily affect all applications. It is easier to maintain code when stored procedures are used, and many different applications use them. During the execution process, this byte code is interpreted, a process which requires time and resources. This makes it possible to avoid continually writing the same codes. By default, PL/SQL code is compiled and stored in the form of byte code ready for execution. With this technique, unwanted query compilation processes are avoided. Therefore, this cached stored procedure will be used for all subsequent tasks of the same SQL Server stored procedure. The following are the key advantages of using test scripts, which are ready T-SQL code that can be reused repeatedly:Īfter the initial execution of a stored procedure, the query optimizer generates an execution plan that is cached in the query plan cache. Overview: Recompile a Stored Procedure SQL Server Let’s take a look at how our Server Management Services at Skynats can help you recompile a stored procedure SQL server method.
0 Comments
Leave a Reply. |