Tag Info

Hot answers tagged

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 = ...


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 ...


2

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 ...


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 ...



Only top voted, non community-wiki answers of a minimum length are eligible