7/11/2023 0 Comments Sql server recompile viewFor example, if you create a temporary table, populate and read from this table in the same batch you’ll experience recompiles at every execution. Mixing Data Definition Language (DDL) and Data Modification Language (DML) statements within the same batch or stored procedure.Query optimizer will compile a new execution plan and use a non-clustered index seek because doing so will be considerably more efficient (as opposed to scanning the entire table). Next time you execute the same batch it only return 10% of the table’s data. ![]() Next suppose that you added thousands of rows to the table referenced by the query (perhaps through a BULK INSERT statement). ![]() As an example, suppose that a query returns 90% of all data in a given table the query optimizer is likely to use a table scan or clustered index scan for such a query. 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.
0 Comments
Leave a Reply. |