Cached Query Plan ...

Question

Cached Query Plan ...

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Rofty SSCrazy Points: 2585 More actions · Answer 1

Thanks for the help guys.

Will be having celebration cup of coffee soon! :w00t:

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

roelofsleroux (10/7/2010)
Nils Gustav Stråbø (10/7/2010)
I'm probably blind, but I don't see any SessionKey column in that table.
Nils thats becuase its not in there yet. Thats part of my version2 design for this table. We have been designing a basic model based on the clients requirements, we are now looking at beefing up the model with better performance based on a trail run we had for almost a year.
But i do now understand the impact the model's design have on cache query plans and its importance to optimize it.

I was also thinking that where is the SessionKey column and you are mentioning that it is in version2. 🙂

In your case, Dynamic query is the best option.

Create the syntax as per your need and execute the query using

EXEC sp_ExecuteSQL @query, parameters (You can check the syntax in BOL.)

This will store the plan in cache and SQL will re-use the plan by just replacing your value.

Thanks

Rofty SSCrazy Points: 2585 More actions · Answer 3

Just another question guys ... does rebuilding indexes have any effect on the cache query plan of a procedure.

I was just wondering cuase we had a few incidents where certain procedures worked fine, but after we rebuild indexes and the procedures took a while to executed.

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 4

Yes, rebuilding an index will automatically update the statistics, and updating statistics will cause queries to recompile.

http://msdn.microsoft.com/en-us/library/ms187348.aspx

"Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile."