The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size.
1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained
an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index
2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)...
BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right).
3. Table size... first - is table size really the optimizer heuristics? meaning for exa
View Complete Post