on 2012 Jan 28 4:26 AM
Hi
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.
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Glenn, what's the meaning of a DFO operator?
DFO is an acronym for Data Flow Operator. Each physical plan operator in an access plan (sequential scan, hash group by, sort, union, etc) is a DFO. It is the tree of DFO operators that constitute the access plan as viewed in the ISQL plan viewer.
Thank you very much for explanation, it is very useful. But, this behavior is somewhat surprising to me. In other database products I'm working with, generating of detailed query plans, including I/O statistics etc. doesn't have so great overhead. I only wish it would stated more clearly in the documentation.
I cannot answer for other products. The overhead of detailed statistics is considerable; that's why on graphical plans there are three options: estimates only, detailed statistics, and detailed and node statistics, in increasing order of overhead.
User | Count |
---|---|
33 | |
22 | |
17 | |
8 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.