Recently, I saw that the same query may take much more time to execute when it is run via Plan Viewer ("Detailed Node and Statistics" mode) as compared to Interactive SQL. Note that some of the queries in question return only a few rows, so client fetching is not an issue here.
Also, some queries doesn't return at all when run via Plan Viewer, or at least I didn't enough patience to wait. I see that the query surely does something using OS utilities, but it just doesn't return. The same query returns in a couple of minutes when run via Interactive SQL. Particularly, I saw this behavior from queries with a number of queries concatenated with UNION ALL in the FROM clause.
The question: is it something expected or I'm hitting a bug? Is it a limitation of the Plan Viewer? I'm working with SQLA 12.0.1.
asked 28 Jan '12, 04:26
Executing a query while collecting detailed statistics about the query's execution is an excellent way to determine not only the characteristics of the execution strategy chosen by the optimizer, but to get actual (versus estimated) query cost parameters in the process.
However, the CPU and elapsed time cost of this data collection is far from free. Saving detailed statistics for an individual DFO operator roughly doubles (I do not have an actual number at my fingertips, unfortunately) the per-row execution cost, in terms of CPU time. So if a DFO operator processes 1 million rows and takes 7 CPU seconds, the plan with statistics will roughly double that to 14 seconds. The more complex the query (and the more filtering that takes place), the greater the amount of metadata collected and, consequently, the higher the overhead.
answered 30 Jan '12, 12:04