sql简单带索引的语句
Some time ago, SQL Server 2017 was released and issued as CTP. The most exciting release in that CTP was that SQL Server now supports Linux! This is awesome and I consider it to be great news for many people.
不久前，SQL Server 2017被发布并作为CTP发行。 CTP中最令人兴奋的版本是SQL Server现在支持Linux！ 这太棒了，我认为这对许多人来说是个好消息。
I am personally interested in the new features of query processing, and finally I had some time to install the SQL Server 2017 and dig a little bit into it. Currently, it is CTP 1.2 available, and I will use this version for my experiments.
我个人对查询处理的新功能感兴趣，最后我花了一些时间安装SQL Server 2017并对其进行了深入研究。 当前，它是CTP 1.2可用的，并且我将在实验中使用此版本。
While exploring new extended events, I’ve found an interesting event compilation_stage_statistics and one of the columns of this event was trivial_plan_scanning_cs_index_discarded with the following description “Number of trivial plans discarded or could have been discarded which scan Columnstore index”. That pushed me to do some investigations of the topic.
在探索新的扩展事件时，我发现了一个有趣的事件compilation_stage_statistics ，该事件的列之一是trivial_plan_scanning_cs_index_discarded ，其描述为“丢弃或可以丢弃哪个扫描列存储索引的琐碎计划的数量”。 这促使我对这个话题进行了一些调查。
Let’s try to make some experiments.
让我们尝试做一些实验。
I use AdventureworksDW2016CTP3 and make a test table with a single clustered Columnstore index on it.
我使用AdventureworksDW2016CTP3并制作了一个带有单个群集Columnstore索引的测试表。
use AdventureworksDW2016CTP3;
go
-- Create a test table with Clustered columnstore index
drop table if exists dbo.FactResellerSales_CCI;
select * into dbo.FactResellerSales_CCI from dbo.FactResellerSales;
create clustered columnstore index cix on dbo.FactResellerSales_CCI;
goLet’s run a couple of queries that result in a trivial plan under compatibility level 130 (SQL Server 2016) and look at their plans.
让我们运行几个查询，这些查询将在兼容级别130（SQL Server 2016）下得出简单的计划，并查看它们的计划。
-- set compatibility level of SQL Server 2016
alter database AdventureworksDW2016CTP3 set compatibility_level = 130;
go
select count_big(*) from [dbo].[FactResellerSales_CCI] f where f.CurrencyKey > 0;
select sum(SalesAmount) over(order by OrderDateKey) from dbo.FactResellerSales_CCI;The plans are:
计划是：
Both plans qualify trivial plan conditions so both of them are trivial and run in a Row Mode.
这两个计划均符合琐碎的计划条件，因此它们都是琐碎的并以行模式运行。
There is a TF 8757, that I described a few years ago, in my Russian blog, that forces the optimizer to skip trivial plan phase. Let’s turn it on and run our queries again.
我几年前在我的俄语博客中描述了TF 8757，它迫使优化器跳过了琐碎的计划阶段。 让我们打开它，然后再次运行查询。
alter database scoped configuration clear procedure_cache;
dbcc traceon (8757);
go
select count_big(*) from [dbo].[FactResellerSales_CCI] f where f.CurrencyKey > 0
select sum(SalesAmount) over(order by OrderDateKey) from [dbo].[FactResellerSales_CCI];
go
dbcc traceoff (8757);What we see in the query plans is completely different from what we have seen earlier.
我们在查询计划中看到的内容与我们之前看到的完全不同。
Both queries are now fully optimized and that lead to different plans. First of all, both queries run in a Batch Mode, which is much faster than a Row Mode.
这两个查询现在都已完全优化，这导致了不同的计划。 首先，两个查询都以Batch Mode运行，这比Row Mode快得多。
In the first query, we see Hash Match Aggregate instead of Stream Aggregate, more to the point you may see that Actual Number of Rows is 0 because all the rows were aggregated locally at the Storage Engine level, you may see property Actual Number of Locally Aggregated Rows = 60855. This is faster than a regular aggregation and is known as Aggregate Pushdown.
在第一个查询中，我们看到哈希匹配聚合而不是流聚合，更重要的是，您可能会看到实际行数为0，因为所有行都是在存储引擎级别本地聚合的，您可能会看到属性“本地实际数”聚合行=60855。这比常规聚合要快，称为“ 聚合下推” 。
In the second query, you may observe a new Window Aggregate operator which is faster than a Window Spool and runs in Batch Mode also.
在第二个查询中，您可能会看到一个新的Window Aggregate运算符，该运算符比Window Spool更快，并且也以批处理模式运行。
Turning on Compatibility level SQL Server 2017 开启相容性等级SQL Server 2017
Let’s turn on Compatibility level for SQL Server 2017, which is 140 in CTP 1.2, and re-run two queries without any TFs.
让我们打开SQL Server 2017的兼容性级别，在CTP 1.2中为140，然后重新运行两个没有任何TF的查询。
alter database AdventureworksDW2016CTP3 set compatibility_level = 140;
alter database scoped configuration clear procedure_cache;
go
select count_big(*) from [dbo].[FactResellerSales_CCI] f where f.CurrencyKey > 0
select sum(SalesAmount) over(order by OrderDateKey) from [dbo].[FactResellerSales_CCI];
goYou may see almost the same fully optimized plans, without any TFs.
您可能会看到几乎没有任何TF的完全优化的计划。
So, it seems, at least in CTP 1.2, that in SQL Server 2017 some plans with Columnstore indexes may skip trivial plan phase to have more benefits from the full optimization phase.
因此，似乎至少在CTP 1.2中，在SQL Server 2017中，某些具有Columnstore索引的计划可能会跳过琐碎的计划阶段，以从完全优化阶段中获得更多收益。
Undocumented TFs 未记录的TF
Some features in the query processor of SQL Server may have trace flags to control their behavior. This may be helpful in case of testing or if the new feature hurts performance.
SQL Server查询处理器中的某些功能可能具有跟踪标志以控制其行为。 如果进行测试或新功能影响性能，这可能会有所帮助。
Skipping trivial plan if Columnstore index is involved also have TFs. They are not documented and not supported (though, CTP itself is not intended for production and support) and might be removed in RTM, but at the moment of writing this post – they work.
如果涉及到列存储索引，则跳过琐碎的计划也有TF。 它们没有文档记录，也不受支持（尽管CTP本身并不用于生产和支持），并且可以在RTM中删除，但是在撰写本文时，它们已经起作用。
If you want to try this feature (skip trivial plan for Columnstore) under Compatibility level 130, you may use trace flag 11002.
如果要尝试在兼容级别130下使用此功能（跳过针对Columnstore的简单计划），则可以使用跟踪标志11002。
alter database AdventureworksDW2016CTP3 set compatibility_level = 130;
alter database scoped configuration clear procedure_cache;
go
select count_big(*) from [dbo].[FactResellerSales_CCI] f where f.CurrencyKey > 0
select count_big(*) from [dbo].[FactResellerSales_CCI] f where f.CurrencyKey > 0 option(querytraceon 11002);
goThe plans are, accordingly:
因此，这些计划是：
If you want to disable this feature in the new Compatibility level 140, you may use trace flag 11012.
如果要在新的兼容性级别140中禁用此功能，则可以使用跟踪标志11012。
alter database AdventureworksDW2016CTP3 set compatibility_level = 140;
alter database scoped configuration clear procedure_cache;
go
select count_big(*) from [dbo].[FactResellerSales_CCI] f where f.CurrencyKey > 0
select count_big(*) from [dbo].[FactResellerSales_CCI] f where f.CurrencyKey > 0 option(querytraceon 11012);
goThe plans are, accordingly:
因此，这些计划是：
That’s all for today, but there a lot of other intriguing additions in SQL Server 2017.
这就是今天的全部内容，但是SQL Server 2017中还有许多其他有趣的功能。
Stay tuned, thank you for reading.
请继续关注，谢谢您的阅读。
(Table of contents)
SQL Server 2017: Columnstore Indexes and Trivial Plan |
SQL Server 2017: Sort, Spill, Memory and Adaptive Memory Grant Feedback |
SQL Server 2017：栏目索引和简单计划 |
翻译自: https://www.sqlshack.com/sql-server-2017-columnstore-indexes-and-trivial-plan/
sql简单带索引的语句
