![]() There are a variety of reasons the database engine may choose to recompile objects. SQL Server automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous. ![]() Proactive execution of this stored procedure is usually unnecessary. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries. 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. Sp_recompile looks for an object in the current database only. Return Code ValuesÄ (success) or a nonzero number (failure) Remarks 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. 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. object is nvarchar(776), with no default. The qualified or unqualified name of a stored procedure, trigger, table, view, or user-defined function in the current database. Transact-SQL syntax conventions Syntax sp_recompile 'object' In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile. 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 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. |