- OPTION RECOMPILE SQL SERVER SERIAL
- OPTION RECOMPILE SQL SERVER MANUAL
- OPTION RECOMPILE SQL SERVER CODE
OPTION RECOMPILE SQL SERVER CODE
The code in Listing 7-1 is extremely simple, but it
Recompilation happens because a SET option changes, the executable plan will be Recompilation affects only a small portion of the procedure. Other words, the whole procedure is recompiled even if the cause of the Or batch is large, because SQL Server 2000 does module-level recompilation. Things that cause recompilation to occur are as follows:ħ-2: Trace Properties Dialog for SQL ProfilerĪ recompilation can incur a huge cost especially if the procedure, function, Problem with executing stored procedures, and that is recompilation. Has very much improved the execution of not only stored procedures butįunctions, batches, dynamic queries, and so on. This architecture, paired with the fact that the execution context is reused,
This data structure, which is called the executable plan orĮxecution context, holds the data specific to each user's execution, such
OPTION RECOMPILE SQL SERVER SERIAL
Query plan in memoryone copy for all serial executions and another for allĮxecutable plan≺ data structure for each user that concurrentlyĮxecutes the query. There are never more than one or two copies of the The plan and no user context information (such as data variable values) is The plan is reentrant, which implies that all users share query plan)≺ read-only data structure used byĪny number of users. Is divided into two structures: a compiled plan and an executable plan.Ĭompiled plan (a.k.a. Server 2000, the query plan architecture differs from previous versions, and it Of the infrastructural improvements in T-SQL: statement recompilation. The next thing we'll look at is a performance enhancement that is part Learn More Buy Statement-Level Recompilation The following example causes stored procedures, triggers, and user-defined functions that act on the Sales.Customer table to be recompiled the next time that they are run.First Look at SQL Server 2005 for Developers, A Requires ALTER permission on the specified object.
OPTION RECOMPILE SQL SERVER MANUAL
Most commonly, automatic recompilation follows changes to the underlying cardinality estimate because of automatic or manual statistics updates. 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. Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run.
0 kommentar(er)
