SQL Server Performance Forum – Threads Archive
parse and compile time
In Query Analizer I’ve trying turning setting "set statistics time on" in order to see the "parse and compile time" as is mentioned in various forums. Unfortunately, this stat is never output. I do get some execution times, but nothing labelled "parse and compile time". Any thoughts ?Oh, and I’m using SQL Server 2000 and my client is XP with SQL Server 2000. Scott W Parcel
I think the statistics in query analyser are only meant for quite granular performance profiling.
For that sort of low level profiling, you need to use sql profiler. Even then, there is no such event that will give you the parse and compile time as such, but i think maybe you could approximate it as the difference between the Batch Start event and the Stmt Start Event (not 100% sure). Either way, time taken to parse and compile is rarely a problem in itself, what you perhaps should be more concerned about, is that once parsed, the execution plan is getting reused. In most cases the parse and compile time is negligible compared to the execution time. The exception is if you have a very quick query running extremely frequently, that is gettign recompiled each time, or for some other reason is not being cached. See the cache hit ratios performance counters.
The converse scenario of course, is where you have an execution plan that IS getting reused, but such is the diversity of parameters or dynamic sql inside, that it might be more optimal to force a recompilation each time. In such situations youd want to evaluate the parse and compile time before settling a WITH RECOMPILATION, but as far as I know you can only do this by trial and experimentation. Im happy for someone to prove me wrong though!
My expectations about "parse and compile time" statistics came from articles such ashttp://www.sql-server-performance.com/statistics_io_time.asp. I assume you are right about the relative importance of compile time, but I wanted to see it anyway. –scott
quote:Originally posted by Chappy
I think the statistics in query analyser are only meant for quite granular performance profiling.
For that sort of low level profiling, you need to use sql profiler. Even then, there is no such event that will give you the parse and compile time as such, but i think maybe you could approximate it as the difference between the Batch Start event and the Stmt Start Event (not 100% sure). Either way, time taken to parse and compile is rarely a problem in itself, what you perhaps should be more concerned about, is that once parsed, the execution plan is getting reused. In most cases the parse and compile time is negligible compared to the execution time. The exception is if you have a very quick query running extremely frequently, that is gettign recompiled each time, or for some other reason is not being cached. See the cache hit ratios performance counters.
For that sort of low level profiling, you need to use sql profiler. Even then, there is no such event that will give you the parse and compile time as such, but i think maybe you could approximate it as the difference between the Batch Start event and the Stmt Start Event (not 100% sure). Either way, time taken to parse and compile is rarely a problem in itself, what you perhaps should be more concerned about, is that once parsed, the execution plan is getting reused. In most cases the parse and compile time is negligible compared to the execution time. The exception is if you have a very quick query running extremely frequently, that is gettign recompiled each time, or for some other reason is not being cached. See the cache hit ratios performance counters.
]]>