SomewhereSomehow's SQL Server notes - A main
At the last event SQLSaturday # 261 - Moscow in 2013, I talked about how the optimizer estimates the expected number of rows, and builds on the basis of this query plan. In other words, I was talking about the cardinality estimation, and of course, could not ignore the new version of the assessment mechanism cardinality in SQL Server 2014.
Information on this topic has been relatively little in the end of this article I will provide a list of all known to me today articles on this topic. First-hand information unavailable at all, hopefully with the official release of SQL Server 2014 the situation will change for the better. In the meantime, I want to share with readers the results of their own experiments. General description of the evaluation process cardinality
The process begins at a very early stage of processing the request, before the actual search begins plan options. When a request comes in to the server it is parsed into a tree of logical operators. Nodes of the tree - the objects of specific bergeon 6767f classes that reflect the query logic.
Consider a simple example that, I still use a surrogate simple DB Optimizer opt out notes (Part 1): Introduction, Optimization: Simplification. Look at the plan for the next query. use opt; go select * from t1 where b = 100 option (recompile, querytraceon 8606, querytraceon 8612, querytraceon 3604, querytraceon 9130);
Simplified Tree - tree operators after simplification. Operator LogOp_Select matters relational operations select, ie "Choose from" and corresponds to the operator in terms of the filter. Operator LogOp_Get - is an operation to get the table corresponds Clustered Index Scan operator in the plan. Please note the property Card (cardinality), for each logical operator - it is zero (0).
This is not the only place where the cardinality is estimated, in the future, when searching for a plan, the optimizer bergeon 6767f generates alternative bergeon 6767f to current operators and the tree gets new logical groups, they should also be evaluated cardinality.
Tree logical operators bergeon 6767f - TF 8606 Tree physical bergeon 6767f operators - TF 8607 Wood operators plan (what is in the cache, and what we used to call the compiled plan) - indirectly through ShowPlan Tree executable statements (also called "iterators", bergeon 6767f ie those operators who commit direct iteration by rows) - this tree can not see any way, but to use the debugger. What we see as the actual execution plan, is actually a combination of collected using a special iterator CQScanProfileNew rollforming information so plan that was compiled. This tree is generated for each user, if the same query with the same compiled plan serve several users simultaneously. You can see the fact of its existence and a few parameters such as the number of uses, size, etc. select cpdo. *, mo. * from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_cached_plan_dependent_objects (cp.plan_handle) cpdo join sys.dm_os_memory_objects mo on mo.memory_object_address = cpdo.memory_object_address
Cardinality estimate is performed at the earliest bergeon 6767f stages of optimization and participates in all the formulas bergeon 6767f calculate the cost, and therefore has a huge influence on the choice of the plan. It is therefore important to know how it runs. Cardinality estimate for versions until 2014
Cardinality computed starts bergeon 6767f with the lowest tree nodes gradually based on the calculated cardinality lower nodes, calculated following cardinality etc. process until it reaches the top.
Inside the site, taking into account the context of the logical operator, which represents bergeon 6767f a node (for example, it will be one of the filter calculation algorithm, Internal Connection - another external bergeon 6767f connection bergeon 6767f - the third and so on). Using the selected algorithm and fed to the input statistics of child nodes (in this case a child node - is the base table, this table is taken basic statistics), the optimizer calculates the selectivity of the operator. The resulting selectivity it multiplies environmental cardinality (also obtained from the previous calculation for the child node, i.e. in this case the base of the table).
The process goes a little differently, if the optimizer knows in advance how many rows it needs to get, ie has a certain target number of rows (row goal). This happens, for example, in a case where the operator TOP. In this case, the cardinality estimate produced below target limited number of rows from the top. select top (5) * from t1 where b = 100
General calculation process has not changed, still uses a bottom-up approach, a probabilistic model to calculate selectivity, different algorithms for different operators. However, changed and added the algorithms that
At the last event SQLSaturday # 261 - Moscow in 2013, I talked about how the optimizer estimates the expected number of rows, and builds on the basis of this query plan. In other words, I was talking about the cardinality estimation, and of course, could not ignore the new version of the assessment mechanism cardinality in SQL Server 2014.
Information on this topic has been relatively little in the end of this article I will provide a list of all known to me today articles on this topic. First-hand information unavailable at all, hopefully with the official release of SQL Server 2014 the situation will change for the better. In the meantime, I want to share with readers the results of their own experiments. General description of the evaluation process cardinality
The process begins at a very early stage of processing the request, before the actual search begins plan options. When a request comes in to the server it is parsed into a tree of logical operators. Nodes of the tree - the objects of specific bergeon 6767f classes that reflect the query logic.
Consider a simple example that, I still use a surrogate simple DB Optimizer opt out notes (Part 1): Introduction, Optimization: Simplification. Look at the plan for the next query. use opt; go select * from t1 where b = 100 option (recompile, querytraceon 8606, querytraceon 8612, querytraceon 3604, querytraceon 9130);
Simplified Tree - tree operators after simplification. Operator LogOp_Select matters relational operations select, ie "Choose from" and corresponds to the operator in terms of the filter. Operator LogOp_Get - is an operation to get the table corresponds Clustered Index Scan operator in the plan. Please note the property Card (cardinality), for each logical operator - it is zero (0).
This is not the only place where the cardinality is estimated, in the future, when searching for a plan, the optimizer bergeon 6767f generates alternative bergeon 6767f to current operators and the tree gets new logical groups, they should also be evaluated cardinality.
Tree logical operators bergeon 6767f - TF 8606 Tree physical bergeon 6767f operators - TF 8607 Wood operators plan (what is in the cache, and what we used to call the compiled plan) - indirectly through ShowPlan Tree executable statements (also called "iterators", bergeon 6767f ie those operators who commit direct iteration by rows) - this tree can not see any way, but to use the debugger. What we see as the actual execution plan, is actually a combination of collected using a special iterator CQScanProfileNew rollforming information so plan that was compiled. This tree is generated for each user, if the same query with the same compiled plan serve several users simultaneously. You can see the fact of its existence and a few parameters such as the number of uses, size, etc. select cpdo. *, mo. * from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_cached_plan_dependent_objects (cp.plan_handle) cpdo join sys.dm_os_memory_objects mo on mo.memory_object_address = cpdo.memory_object_address
Cardinality estimate is performed at the earliest bergeon 6767f stages of optimization and participates in all the formulas bergeon 6767f calculate the cost, and therefore has a huge influence on the choice of the plan. It is therefore important to know how it runs. Cardinality estimate for versions until 2014
Cardinality computed starts bergeon 6767f with the lowest tree nodes gradually based on the calculated cardinality lower nodes, calculated following cardinality etc. process until it reaches the top.
Inside the site, taking into account the context of the logical operator, which represents bergeon 6767f a node (for example, it will be one of the filter calculation algorithm, Internal Connection - another external bergeon 6767f connection bergeon 6767f - the third and so on). Using the selected algorithm and fed to the input statistics of child nodes (in this case a child node - is the base table, this table is taken basic statistics), the optimizer calculates the selectivity of the operator. The resulting selectivity it multiplies environmental cardinality (also obtained from the previous calculation for the child node, i.e. in this case the base of the table).
The process goes a little differently, if the optimizer knows in advance how many rows it needs to get, ie has a certain target number of rows (row goal). This happens, for example, in a case where the operator TOP. In this case, the cardinality estimate produced below target limited number of rows from the top. select top (5) * from t1 where b = 100
General calculation process has not changed, still uses a bottom-up approach, a probabilistic model to calculate selectivity, different algorithms for different operators. However, changed and added the algorithms that
No comments:
Post a Comment