Tag Info

New answers tagged

3

The decision to use a table spool or not depends on the query optimizer's cost assessments. These in turn are influenced by the specific values of parameters when the query plan is compiled (or recompiled). So, this is just a regular case of parameter sensitivity. The chosen (and cached) plan depends on the parameter values sniffed when a compilation or ...


8

when does SQL Server notice the new indexes and determines whether they actually help the query? Immediately. Creating an index causes a recompile because of a schema change. Here's an illustration of this. First to setup the objects in the test database: use TestDatabase; go if exists (select 1 from sys.tables where object_id = ...


1

My experience is that a useful index gets used almost immediately. It does change the information available to the optimizer after all. However, a new index may never get used if the optimizer rejects its value. You should note that a stored procedure may hold onto an existing plan for some time. If so, you can use exec sp_recompile procedurename to ...


3

Am I correct in saying that statistics are only used when creating the execution plan No, out-of-date statistics may cause an optimality-related recompilation of the affected statement. We have an ascending date/time column in one of our largest tables that we query regularly Sub-optimal execution plans caused by predicate values being outside ...


3

Am I correct in saying that statistics are only used when creating the execution plan for a stored procedure, and they are not used in the actual execution context? No, what happens is that the execution plan for a stored procedure is cached. Assuming there is enough available memory to continue holding the plan, it won't change unless one of the ...



Top 50 recent answers are included