![]() If the batch references tables in which data volume has changed significantly since the last execution of this batch then it will have to be re-compiled. If the batch changes the value of a particular SET option, then the query plan will be recompiled at each execution. Furthermore, some SET options can affect the results of a query. If the schema of any object referenced by the batch has changed (for example - due to adding a column, dropping a constraint, or creating an index) the batch will have to be re-compiled in order to return correct results. Each Transact-SQL batch references one or multiple database objects (tables, views, user-defined functions, etc.). In rare cases, recompiling stored procedure execution plan might be beneficial, but as a rule recompiling stored procedures at every execution is a poor idea. If you use either of these constructs, you advise SQL Server to recompile the execution plan therefore previously used execution plan will not be reused. ![]() CREATE PROCEDURE…WITH RECOMPILE or EXEC PROCEDURE…WITH RECOMPILE.Query plans can typically be reused if the server name, database name and owner name for each object referenced by the query are the same from one execution to next. If Transact-SQL code specifies database name, owner name and object name for each referenced object, then SQL Server is more likely to re-use the query execution plan. If the object name isn’t qualified, then SQL Server will have to resolve the name to determine the correct database where the object resides and its owner. If any query contains a literal longer than 8KB, the execution plan is not cached and it will be recompiled at each execution. The execution plan of bulk insert statements is never cached so their execution plan is compiled at every execution. It is recommended to change the settings at connection time and ensure they do not change for the duration of the connection: Changing the value of the following set options is likely to cause SQL Server to not reuse execution plans. If the same query is executed with a different literal value, the plan can be reused. In this case,SQL Server can replace the literal value with a parameter and cache the query execution plan. Since SQL Server 2005, the ad-hoc query plan can be cached using auto-parameterization feature. Even if there is an extra space or line-break, the query plan will not be reused. In order to reuse the execution plan for a given ad-hoc query, the entire statement must be identical to the one executed before. Ad-hoc queries are SQL statements sent directly to the server without being enclosed in stored procedures. Some of the main causes of frequent statement recompiles include the following: Storage Performance and Utilization Management.Information Archiving & Storage Management.Hybrid Active Directory Security and Governance.Starling Identity Analytics & Risk Intelligence.One Identity Safeguard for Privileged Passwords.
0 Comments
Leave a Reply. |