SomewhereSomehow's SQL Server notes - A main
This phase is also called Quick Plan. As we have said, queries can bypass the step of Transaction Processing (search 0), and go directly to this phase, if the request at least three tables. music clocks This phase also notable for the fact that it searches for a parallel plan.
I will not go into the material of the previous parts, and once again to talk about that at this phase also apply transformation rules and the search for the plan if it was not found in the previous phases. Instead, a better look at some of the differences and peculiarities of this stage.
Let's start with the fact that our data will change music clocks so that the query plan is found in the previous step search0, music clocks ceased to satisfy the internal threshold for this stage of the optimizer and the optimizer continued searching stage search1. music clocks To do this, you need to increase the size of the test tables, however, we use another technique, namely undocumented options command update statistics - with row_count, page_count. These options change the metadata on the index, setting the values that we determine.
Execute: select [allocation_units: data_pages] = au.data_pages, [partitions: rows] = p.rows, [partition_stats: in_row_data_page_count] = ps.in_row_data_page_count, [partition_stats: row_count] = ps.row_count from sys.allocation_units au join sys. partitions p on au.container_id = p.hobt_id join sys.dm_db_partition_stats ps on ps.partition_id = p.partition_id where p. [object_id] in (object_id ('t1'), object_id ('t2'), object_id ('t3' )) update statistics t1 with rowcount = 1000000, pagecount = 1000 update statistics t2 with rowcount = 1000000, pagecount = 1000 update statistics t3 with rowcount = 1000000, music clocks pagecount = 1000 select [allocation_units: data_pages] = au.data_pages, [partitions: rows ] = p.rows, [partition_stats: in_row_data_page_count] = ps.in_row_data_page_count, music clocks [partition_stats: row_count] = ps.row_count music clocks from sys.allocation_units au join sys.partitions p on au.container_id = p.hobt_id music clocks join sys.dm_db_partition_stats ps on ps . partition_id = p.partition_id where p. [object_id] in (object_id ('t1'), object_id music clocks ('t2'), object_id ('t3'))
Let's talk a bit more about this team. Although this command options update statistics, music clocks but in reality she is not updated statistics. Let's music clocks see, for example, what are the statistics for the table t1: select * from sys.stats WHERE object_id = object_id ('t1')
Approx. By the way, how a name automatic music clocks statistics, the last part - object_id is the object that you created statistics goes on id column prefix on Sys, well, WA - met in the literature is the description that this supposedly Washington, a place where the development team is located, but people who are now working in the optimizer said that this story is not remembered. However, maybe someone else remembers, we shall assume it's just a legend.
Let's run the command to view statistics for the primary key (you probably have other identifier of the primary key, so adjust to your): dbcc show_statistics (t1, PK__t1__3BD0198E0F624AF8)
If you try to return all values back using the command: update statistics t1 with fullscan update statistics t2 with fullscan music clocks update statistics t3 with fullscan music clocks select object_name (ps. [object_id]), ps.in_row_data_page_count, ps.row_count from sys.dm_db_partition_stats ps where ps. [object_id] in (object_id ('t1'), object_id ('t2'), object_id ('t3'))
All this suggests that the updating of metadata in this case, the real statistics in no way affected, and only updated metadata music clocks index. So of course it would be logical to put this functionality, for example, alter index, but, however, because this option is undocumented, music clocks and then bring a claim is inappropriate.
But let us not do it, because Our goal is just the reverse, to make the optimizer think that the table is large, because metadata affect music clocks the assessment and plan (the effect can be calculated out of proportion in relation to the real figures). In actual implementation, of course, will be considered only the actual number of rows and pages.
Now, fulfill our request, with information on optimization steps: select * from t1 join t2 on t1.a = t2.b join t3 on t2.b = t3.c where t1.b = 1 option (recompile, querytraceon 3604, querytraceon 8675)
Result: End of simplification, time: 0 net: 0 total: 0 net: 0 end exploration, music clocks tasks: 89 no total cost time: 0.002 net: 0.002 total: 0.003 net: 0.003 end search (0), cost: 9.35972 tasks: 207 time: 0 net: 0 total: 0.004 net: 0.004 end exploration, tasks: 460 Cost = 9.35972 time: 0.001 net: 0.001 total: 0.006 net: 0.006 end search (1), cost: 8.5812 tasks: 895 time: 0.003 net : 0.003 total: music clocks 0.01 net: 0.01 end exploration, tasks: 896 Cost = 8.5812 time: 0 net: 0 total: 0.01 net: 0.01 end search (1), cost: 8.5812 tasks: music clocks 1397
This phase is also called Quick Plan. As we have said, queries can bypass the step of Transaction Processing (search 0), and go directly to this phase, if the request at least three tables. music clocks This phase also notable for the fact that it searches for a parallel plan.
I will not go into the material of the previous parts, and once again to talk about that at this phase also apply transformation rules and the search for the plan if it was not found in the previous phases. Instead, a better look at some of the differences and peculiarities of this stage.
Let's start with the fact that our data will change music clocks so that the query plan is found in the previous step search0, music clocks ceased to satisfy the internal threshold for this stage of the optimizer and the optimizer continued searching stage search1. music clocks To do this, you need to increase the size of the test tables, however, we use another technique, namely undocumented options command update statistics - with row_count, page_count. These options change the metadata on the index, setting the values that we determine.
Execute: select [allocation_units: data_pages] = au.data_pages, [partitions: rows] = p.rows, [partition_stats: in_row_data_page_count] = ps.in_row_data_page_count, [partition_stats: row_count] = ps.row_count from sys.allocation_units au join sys. partitions p on au.container_id = p.hobt_id join sys.dm_db_partition_stats ps on ps.partition_id = p.partition_id where p. [object_id] in (object_id ('t1'), object_id ('t2'), object_id ('t3' )) update statistics t1 with rowcount = 1000000, pagecount = 1000 update statistics t2 with rowcount = 1000000, pagecount = 1000 update statistics t3 with rowcount = 1000000, music clocks pagecount = 1000 select [allocation_units: data_pages] = au.data_pages, [partitions: rows ] = p.rows, [partition_stats: in_row_data_page_count] = ps.in_row_data_page_count, music clocks [partition_stats: row_count] = ps.row_count music clocks from sys.allocation_units au join sys.partitions p on au.container_id = p.hobt_id music clocks join sys.dm_db_partition_stats ps on ps . partition_id = p.partition_id where p. [object_id] in (object_id ('t1'), object_id music clocks ('t2'), object_id ('t3'))
Let's talk a bit more about this team. Although this command options update statistics, music clocks but in reality she is not updated statistics. Let's music clocks see, for example, what are the statistics for the table t1: select * from sys.stats WHERE object_id = object_id ('t1')
Approx. By the way, how a name automatic music clocks statistics, the last part - object_id is the object that you created statistics goes on id column prefix on Sys, well, WA - met in the literature is the description that this supposedly Washington, a place where the development team is located, but people who are now working in the optimizer said that this story is not remembered. However, maybe someone else remembers, we shall assume it's just a legend.
Let's run the command to view statistics for the primary key (you probably have other identifier of the primary key, so adjust to your): dbcc show_statistics (t1, PK__t1__3BD0198E0F624AF8)
If you try to return all values back using the command: update statistics t1 with fullscan update statistics t2 with fullscan music clocks update statistics t3 with fullscan music clocks select object_name (ps. [object_id]), ps.in_row_data_page_count, ps.row_count from sys.dm_db_partition_stats ps where ps. [object_id] in (object_id ('t1'), object_id ('t2'), object_id ('t3'))
All this suggests that the updating of metadata in this case, the real statistics in no way affected, and only updated metadata music clocks index. So of course it would be logical to put this functionality, for example, alter index, but, however, because this option is undocumented, music clocks and then bring a claim is inappropriate.
But let us not do it, because Our goal is just the reverse, to make the optimizer think that the table is large, because metadata affect music clocks the assessment and plan (the effect can be calculated out of proportion in relation to the real figures). In actual implementation, of course, will be considered only the actual number of rows and pages.
Now, fulfill our request, with information on optimization steps: select * from t1 join t2 on t1.a = t2.b join t3 on t2.b = t3.c where t1.b = 1 option (recompile, querytraceon 3604, querytraceon 8675)
Result: End of simplification, time: 0 net: 0 total: 0 net: 0 end exploration, music clocks tasks: 89 no total cost time: 0.002 net: 0.002 total: 0.003 net: 0.003 end search (0), cost: 9.35972 tasks: 207 time: 0 net: 0 total: 0.004 net: 0.004 end exploration, tasks: 460 Cost = 9.35972 time: 0.001 net: 0.001 total: 0.006 net: 0.006 end search (1), cost: 8.5812 tasks: 895 time: 0.003 net : 0.003 total: music clocks 0.01 net: 0.01 end exploration, tasks: 896 Cost = 8.5812 time: 0 net: 0 total: 0.01 net: 0.01 end search (1), cost: 8.5812 tasks: music clocks 1397
No comments:
Post a Comment