SQL Server Execution plans
-
Once SQL server has come up with an execution plan are there different ways
of putting it into effect? What I am asking is :
Is there more than one way of executing an execution plan!?!
The reason why I ask is:
a) Sometimes a certain piece of T-SQL results in an execution plan and
completes in 3 or 4 seconds.
b) When exactly the same T-SQL is run at a later date it results in exactly
the same execution plan but completes in 150/200 secs.
I have tried all the normal troubleshooting methodologies looked at stats, defrag, blocks, deadlocks etc etc etc.
All I really want to know is is there more than 1 way of completing an
execution plan? Is there anything intelligent under the optimiser that is
capable of making decisions on the fly about how it is going to execute the
plan? Or once you have an execution plan is that it - SQL will only follow
the execution plan till it completes or errors?
-
Woops, please ignore - I'm using 2000
-
It seems the data for your query/sp was not in memory when you noticed it runs longer. In order to confirm it, try to free the data buffer using DBCC DROPCLEANBUFFERS and rerun the query/sp.
Don't try it in your production system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply