December 19, 2003 at 11:32 am
I have a procedure that runs very often through out the day. A look at syscacheobjects always shows a copy of the procedure in there. If I run the proc in QA with the "set statistics time on" option, I see that it sometimes accumulates "parse and compile" time. The amount of time is significant (2 to 4 seconds).
If this procedure is in cache, why does it require any time for parse and compile?
Thanks in advance for your thoughts!
apf
December 19, 2003 at 11:45 am
December 19, 2003 at 11:45 am
Even though it may be in the cache, there may be reasons it incurs a recompile. If the execution plan looks to differ drastically from what's necessary for the new execution, it'll recompile. If temporary tables are used but not cited properly, it may recompile. If the stored procedure is named wrong (sp_*), it may start down that path. If DML and DDL are interleaved, that could be a cause. More here:
http://qa.sqlservercentral.com/columnists/bkelley/procedurecache.asp
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply