SomewhereSomehow's SQL Server notes - A main
In the first part we have some general theoretical information (which I will refer, in this, I recommend them to see if you have not had time), as well as presents ciao baby massapequa the trace flag 8780 which sets timeout to a very large number, actually disabling it.
We return to the request from the first part: set showplan_xml on go with cte as (select t1. * From t1 join t2 on t1.a = t2.b where t1.b> 1) select t1.a, t1.b, t1.c , cte.b, cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option ciao baby massapequa (recompile, querytraceon 3604, querytraceon 8675, querytraceon 8780) go set showplan_xml off go
If you wonder whether you can somehow ciao baby massapequa continue to optimize not given threshold at which the plan is considered good enough, the answer is - yes, you can. According to the algorithm described in the first part, in step Optimize Inputs assesses ciao baby massapequa the best plan, in step Optimize Group better preservation plan Memo. Inside the server, these operations occur in functions CTask_OptInputs :: Perform and CTask_OptimizeGroup :: Perform respectively.
Let's look at the work of the flag in action. Perform the previous query with this flag, and without it, and compare the results. set showplan_xml on go with cte as (select t1. * from t1 join t2 on t1.a = t2.b where t1.b> 1) select t1.a, t1.b, t1.c, cte.b, cte2. c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option (recompile, querytraceon 3604, querytraceon 8675, querytraceon 8780) go with cte as (select t1. * from t1 join t2 on t1.a = t2.b where t1.b> 1) select t1.a, t1.b, t1.c, cte.b, cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option (recompile, querytraceon ciao baby massapequa 3604, querytraceon 8675, querytraceon 8780, querytraceon 8671) go set showplan_xml off go
As can be seen from the results in the case of flag enabled on kazhoy stage was conducted ciao baby massapequa more research (more optimization problems), in addition to the search stage 1, the optimizer used all available transformations, and moved on to the next step, search 2. If the flag is off, the optimization stops at the stage of search 1.
Is it good or bad? Again, the concept of Good Enough Plan invented for a reason. If we look at the plans, it will be seen that they are almost indistinguishable. Except that in the second section of the plane disappeared Reason For Early Termination Of Statement Optimization: Good Enough Plan Found , and increased compile-time memory.
If you take a little more complicated query, for example, ciao baby massapequa close to the request from the first part, which exceeded the pseudo-infinite timeout. And to fulfill his flag enabled, and without ciao baby massapequa - the difference in compilation time will be visible to the naked eye.
set showplan_xml on go declare @ v1 int, @ v2 int, @ v3 int, @ v4 int, @ v5 int, @ v6 int, @ v7 int, @ v8 int; with cte as (select t1. * from t1 join t2 on t1.a = t2.b where t1.b> 1) select @ v1 = t1.a, @ v2 = t1.b, @ v3 = t1.c, @ v4 = cte.b, @ v5 = cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b join cte cte3 on t1.c = cte3.b join cte cte4 on t1.c = cte4.b join cte cte5 on t1. c = cte5.b join cte cte6 on t1.c = cte6.b option (recompile, querytraceon 3604, querytraceon 8675, querytraceon 8780) go declare @ v1 int, @ v2 int, @ v3 int, @ v4 int, @ v5 int , @ v6 int, @ v7 int, @ v8 int; with cte as (select t1. * from t1 join t2 on t1.a = t2.b where t1.b> 1) select ciao baby massapequa @ v1 = t1.a, @ v2 = t1.b, @ v3 = t1.c, @ v4 = cte.b, @ v5 = cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b join cte cte3 on t1.c = cte3.b join cte cte4 on t1.c = cte4.b join cte cte5 on t1.c = cte5.b join cte cte6 on t1.c = cte6.b option (recompile, querytraceon 3604, querytraceon 8675, querytraceon 8780, querytraceon 8671) go set showplan_xml off go
A curious fact, to which you can pay attention to is the fact that the first plan is also no section Reason ciao baby massapequa For Early Termination: Good Enough Plan . This means that at an early stage was not identified a good plan and the optimizer honestly worked all transformations until not found a good.
What's the difference, and that flag off 8671? The difference in the amount of branches ciao baby massapequa examined ciao baby massapequa and, accordingly, the number of transformations. In the case of "normal" query processing, searching for the most effective plan, potentially inefficient branches discarded (pruning), being a kind of "Trekking" ciao baby massapequa better plan. If at some point the optimizer determines that he can still do the conversion, but good enough plan has already been found - that occurs before the completion of the wording Good Enough Plan Found . If we are using the flag 8671 to disable the algorithm comparison factor responsible for what is considered good if the plan or reject as ineffective - we significantly increase the time to search and study of alternatives. We begin to explore the inefficient branches, spending resources to it. And we do not get
In the first part we have some general theoretical information (which I will refer, in this, I recommend them to see if you have not had time), as well as presents ciao baby massapequa the trace flag 8780 which sets timeout to a very large number, actually disabling it.
We return to the request from the first part: set showplan_xml on go with cte as (select t1. * From t1 join t2 on t1.a = t2.b where t1.b> 1) select t1.a, t1.b, t1.c , cte.b, cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option ciao baby massapequa (recompile, querytraceon 3604, querytraceon 8675, querytraceon 8780) go set showplan_xml off go
If you wonder whether you can somehow ciao baby massapequa continue to optimize not given threshold at which the plan is considered good enough, the answer is - yes, you can. According to the algorithm described in the first part, in step Optimize Inputs assesses ciao baby massapequa the best plan, in step Optimize Group better preservation plan Memo. Inside the server, these operations occur in functions CTask_OptInputs :: Perform and CTask_OptimizeGroup :: Perform respectively.
Let's look at the work of the flag in action. Perform the previous query with this flag, and without it, and compare the results. set showplan_xml on go with cte as (select t1. * from t1 join t2 on t1.a = t2.b where t1.b> 1) select t1.a, t1.b, t1.c, cte.b, cte2. c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option (recompile, querytraceon 3604, querytraceon 8675, querytraceon 8780) go with cte as (select t1. * from t1 join t2 on t1.a = t2.b where t1.b> 1) select t1.a, t1.b, t1.c, cte.b, cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b option (recompile, querytraceon ciao baby massapequa 3604, querytraceon 8675, querytraceon 8780, querytraceon 8671) go set showplan_xml off go
As can be seen from the results in the case of flag enabled on kazhoy stage was conducted ciao baby massapequa more research (more optimization problems), in addition to the search stage 1, the optimizer used all available transformations, and moved on to the next step, search 2. If the flag is off, the optimization stops at the stage of search 1.
Is it good or bad? Again, the concept of Good Enough Plan invented for a reason. If we look at the plans, it will be seen that they are almost indistinguishable. Except that in the second section of the plane disappeared Reason For Early Termination Of Statement Optimization: Good Enough Plan Found , and increased compile-time memory.
If you take a little more complicated query, for example, ciao baby massapequa close to the request from the first part, which exceeded the pseudo-infinite timeout. And to fulfill his flag enabled, and without ciao baby massapequa - the difference in compilation time will be visible to the naked eye.
set showplan_xml on go declare @ v1 int, @ v2 int, @ v3 int, @ v4 int, @ v5 int, @ v6 int, @ v7 int, @ v8 int; with cte as (select t1. * from t1 join t2 on t1.a = t2.b where t1.b> 1) select @ v1 = t1.a, @ v2 = t1.b, @ v3 = t1.c, @ v4 = cte.b, @ v5 = cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b join cte cte3 on t1.c = cte3.b join cte cte4 on t1.c = cte4.b join cte cte5 on t1. c = cte5.b join cte cte6 on t1.c = cte6.b option (recompile, querytraceon 3604, querytraceon 8675, querytraceon 8780) go declare @ v1 int, @ v2 int, @ v3 int, @ v4 int, @ v5 int , @ v6 int, @ v7 int, @ v8 int; with cte as (select t1. * from t1 join t2 on t1.a = t2.b where t1.b> 1) select ciao baby massapequa @ v1 = t1.a, @ v2 = t1.b, @ v3 = t1.c, @ v4 = cte.b, @ v5 = cte2.c from t1 join cte on t1.a = cte.b join cte cte2 on t1.c = cte2.b join cte cte3 on t1.c = cte3.b join cte cte4 on t1.c = cte4.b join cte cte5 on t1.c = cte5.b join cte cte6 on t1.c = cte6.b option (recompile, querytraceon 3604, querytraceon 8675, querytraceon 8780, querytraceon 8671) go set showplan_xml off go
A curious fact, to which you can pay attention to is the fact that the first plan is also no section Reason ciao baby massapequa For Early Termination: Good Enough Plan . This means that at an early stage was not identified a good plan and the optimizer honestly worked all transformations until not found a good.
What's the difference, and that flag off 8671? The difference in the amount of branches ciao baby massapequa examined ciao baby massapequa and, accordingly, the number of transformations. In the case of "normal" query processing, searching for the most effective plan, potentially inefficient branches discarded (pruning), being a kind of "Trekking" ciao baby massapequa better plan. If at some point the optimizer determines that he can still do the conversion, but good enough plan has already been found - that occurs before the completion of the wording Good Enough Plan Found . If we are using the flag 8671 to disable the algorithm comparison factor responsible for what is considered good if the plan or reject as ineffective - we significantly increase the time to search and study of alternatives. We begin to explore the inefficient branches, spending resources to it. And we do not get
No comments:
Post a Comment