June 10, 2013 at 8:58 am
I have a Parameterized Stored procedure that has a query with some joins and use of temp table and UNION.
When I run the query for the first time in SSMS it runs for 55 secs , however, any subsequent run completes in 5 secs
even if I change the parameter values... seems optimizer creates the execution plan and re-uses it subsequently.
When I repeat the same action after few hours or in another session (new SPID) then it runs for 55 secs again in first run and subsequently 5 secs, no matter how many times I run it.
Why is SQL Server flushing the Query plan out in new session... how can I make it cache the execution plan until optimizer finds a good reason to regenerate one like a new Index or change of columns etc...
June 10, 2013 at 9:04 am
WangcChiKaBastar (6/10/2013)
I have a Parameterized Stored procedure that has a query with some joins and use of temp table and UNION.When I run the query for the first time in SSMS it runs for 55 secs , however, any subsequent run completes in 5 secs
even if I change the parameter values... seems optimizer creates the execution plan and re-uses it subsequently.
When I repeat the same action after few hours or in another session (new SPID) then it runs for 55 secs again in first run and subsequently 5 secs, no matter how many times I run it.
Why is SQL Server flushing the Query plan out in new session... how can I make it cache the execution plan until optimizer finds a good reason to regenerate one like a new Index or change of columns etc...
I do not think the overhead is caused by the parsing of a new execution plan - most likely, the drop in execution time comes from data buffering. First execution physically reads the data and caches it, subsequent executions are logically reading, not physically doing it.
To confirm, just trace the queries.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 10, 2013 at 9:05 am
Probably has nothing to do with the generation of the execution plan.
The first time a query runs, the data is read off disk into the data cache. Subsequent executions the data is read from cache, which is much faster. If the data is not needed for a while then, it's likely to be discarded from memory to make space for other data that is needed. Hence if the query's run again some time later, again the data has to be read from disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2013 at 9:10 am
Thanks Gila and Paul.
So effectively I should consider more optimization of the query itself with an execution time of 55 secs ?
since 5 secs run time is only temporary run off of the cached data from the disk like you mentioned.
Also, is it the same behavior when the Stored procedure is called from .Net ?
June 10, 2013 at 9:18 am
WangcChiKaBastar (6/10/2013)
So effectively I should consider more optimization of the query itself with an execution time of 55 secs ?since 5 secs run time is only temporary run off of the cached data from the disk like you mentioned.
It depends on the kind of query, if query is executed by the application in a way that the perceived performance is 5 seconds then you can say "it takes 5 seconds in operational conditions".
Having said that, optimizing a query never hurts.
WangcChiKaBastar (6/10/2013)
Also, is it the same behavior when the Stored procedure is called from .Net ?
Yes. Data buffering is a function of the backend a.k.a. database engine therefore, no matter how the query gets executed data buffering will be part of the picture.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 10, 2013 at 9:18 am
Exactly the same behaviour no matter where it's called from and no matter whether it's a procedure or ad-hoc code.
Yes, see if you can tune it, especially if you can get it to read less data. Also consider optimising other queries on the server to read less data. That way, there's a better chance the data it needs will still be in cache and less of a drive hit if it isn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply