![]() There are many factors that can cause SQL Server to not use an existing plan and recompile the query plan. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries. SQL Server can often re-use previously generated query plans for stored procedures as well as for Transact-SQL batches by searching the procedure cache for an existing execution plan. As indexes or other changes that affect statistics are made to the database, compiled stored procedures, triggers, and user-defined functions may lose efficiency. The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. Note that adding the RECOMPILE option to a stored procedure is not something I recommend it means that the entire stored procedure will recompile every time it is executed. Sp_recompile looks for an object in the current database only. Lastly, we’ll create a stored procedure to use for testing that is created with RECOMPILE and then completely free procedure cache. Return Code ValuesĠ (success) or a nonzero number (failure) Remarks Instead, the magic happens the next time queries referencing the recompiled object run. When this is run, related plans are not immediately removed from SQL Server’s execution plan cache. If object is the name of a table or view, all the stored procedures, triggers, or user-defined functions that reference the table or view will be recompiled the next time that they are run. The command looks like this (a table in this example): 1. If object is the name of a stored procedure, trigger, or user-defined function, the stored procedure, trigger, or function will be recompiled the next time that it is run. I'm using SQL Server's built-in encryption to hide data in one of my SQL Server databases, but this is a reporting system and. object is nvarchar(776), with no default. Simple script to refresh or recompile your business-level views. The qualified or unqualified name of a stored procedure, trigger, table, view, or user-defined function in the current database. My original plan was to recompile all the objects in the database using the following code but that requires sprecompile to work. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile. My expectation is that sprecompile would cause the function to be recompiled however, that is either not happening or that is not good enough to update the values return by A. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed InstanceĬauses stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |