请参阅https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise)上的执行计划
- 我有一个数据仓库表 peak_reporting_data，它跟踪每天和每小时的活动，每月包含大约 40 亿行，其中包含由 date_key 分区的聚集 columnstoreindex（每天一个分区）
- 在 peak_reporting_monats_peaks 表中，我汇总了该表并按月峰值对其进行排序/排名。有 3 种类型的活动 (kpi_type)，对于每种类型，我每月最多有 24 小时 * 31 天 = 744 行，[monats_peak] 排名从 1 到 744。它具有超过 month_key、kpi_type、monats_peak 的唯一索引。
- 对于最活跃的时间（每个 kpi_type），我需要更多详细信息，因此我编写了以下查询/视图：
SELECT prmp.month_key
, prd.*
FROM mba.peak_reporting_monats_peaks AS prmp
LEFT LOOP JOIN (SELECT prd.date_key
, prd.hour
, prd.kpi_type
, prd.is_dr_brand
, prd.type_id_usage
, prd.product_identifier
, SUM(prd.kb) / 1024.0 / 1024.0 AS gb
, SUM(CAST(prd.sek AS BIGINT)) AS sek
, SUM(prd.anzahl) AS anzahl
, SUM(prd.kb) / 439453125.0 AS gbits
FROM db1.mba.peak_reporting_data AS prd
GROUP BY prd.date_key
, prd.kpi_type
, prd.is_dr_brand
, prd.hour
, prd.type_id_usage
, prd.product_identifier
) AS prd
ON prd.date_key = prmp.date_key
AND prd.hour = prmp.hour
WHERE prmp.monats_peak = 1
AND prmp.month_key = 202107
由于在 peak_reporting_monats_peaks 中每月恰好有 3 行 monats_peak = 1，因此 SQL 服务器执行 3 个嵌套循环和查询/基于 date_key、hour 和 kpi_type 聚合大表是合乎逻辑的（这可能会在 2 秒内完成，正如我用游标测试的那样）。
但遗憾的是，它总是读取整个表（目前为 360 亿行），而 ColumnstoreIndexScan 运算符中没有任何谓词/查找谓词，无论我尝试什么。由于这个原因，查询需要 2-3 分钟才能完成，而不是 2 秒。
如果我使用 INNER LOOP JOIN 而不是 LEFT LOOP JOIN 它会删除过滤器运算符但在 JOIN 之前添加一个惰性表假脱机，通常的 INNER JOIN（没有 LOOP）将导致 HASH JOIN（再次在整个表上）。
任何想法，如何（除了带有游标的过程或多行表值函数）我可以“强制”SQL服务器进行3个简单的查找（+聚合）而不是读取大量不相关的数据？
统计数据等是正确的，它确切地知道 peak_reporting_monats_peaks 中将有 3 行，我在大 peak_reporting_data 中添加了 date_key + hour 的显式统计
DDL：
USE tempdb
GO
CREATE SCHEMA [mba] AUTHORIZATION dbo
GO
CREATE PARTITION FUNCTION pf_mba_cdr (INT)
AS RANGE RIGHT FOR VALUES (20201101 , 20201102 , 20201103 , 20201104 , 20201105 , 20201106 , 20201107 , 20201108 , 20201109 , 20201110 , 20201111 , 20201112 , 20201113 , 20201114 , 20201115 , 20201116 , 20201117 , 20201118 , 20201119 , 20201120 , 20201121 , 20201122 , 20201123 , 20201124 , 20201125 , 20201126 , 20201127 , 20201128 , 20201129 , 20201130 , 20201201 , 20201202 , 20201203 , 20201204 , 20201205 , 20201206 , 20201207 , 20201208 , 20201209 , 20201210 , 20201211 , 20201212 , 20201213 , 20201214 , 20201215 , 20201216 , 20201217 , 20201218 , 20201219 , 20201220 , 20201221 , 20201222 , 20201223 , 20201224 , 20201225 , 20201226 , 20201227 , 20201228 , 20201229 , 20201230 , 20201231 , 20210101 , 20210102 , 20210103 , 20210104 , 20210105 , 20210106 , 20210107 , 20210108 , 20210109 , 20210110 , 20210111 , 20210112 , 20210113 , 20210114 , 20210115 , 20210116 , 20210117 , 20210118 , 20210119 , 20210120 , 20210121 , 20210122 , 20210123 , 20210124 , 20210125 , 20210126 , 20210127 , 20210128 , 20210129 , 20210130 , 20210131 , 20210201 , 20210202 , 20210203 , 20210204 , 20210205 , 20210206 , 20210207 , 20210208 , 20210209 , 20210210 , 20210211 , 20210212 , 20210213 , 20210214 , 20210215 , 20210216 , 20210217 , 20210218 , 20210219 , 20210220 , 20210221 , 20210222 , 20210223 , 20210224 , 20210225 , 20210226 , 20210227 , 20210228 , 20210301 , 20210302 , 20210303 , 20210304 , 20210305 , 20210306 , 20210307 , 20210308 , 20210309 , 20210310 , 20210311 , 20210312 , 20210313 , 20210314 , 20210315 , 20210316 , 20210317 , 20210318 , 20210319 , 20210320 , 20210321 , 20210322 , 20210323 , 20210324 , 20210325 , 20210326 , 20210327 , 20210328 , 20210329 , 20210330 , 20210331 , 20210401 , 20210402 , 20210403 , 20210404 , 20210405 , 20210406 , 20210407 , 20210408 , 20210409 , 20210410 , 20210411 , 20210412 , 20210413 , 20210414 , 20210415 , 20210416 , 20210417 , 20210418 , 20210419 , 20210420 , 20210421 , 20210422 , 20210423 , 20210424 , 20210425 , 20210426 , 20210427 , 20210428 , 20210429 , 20210430 , 20210501 , 20210502 , 20210503 , 20210504 , 20210505 , 20210506 , 20210507 , 20210508 , 20210509 , 20210510 , 20210511 , 20210512 , 20210513 , 20210514 , 20210515 , 20210516 , 20210517 , 20210518 , 20210519 , 20210520 , 20210521 , 20210522 , 20210523 , 20210524 , 20210525 , 20210526 , 20210527 , 20210528 , 20210529 , 20210530 , 20210531 , 20210601 , 20210602 , 20210603 , 20210604 , 20210605 , 20210606 , 20210607 , 20210608 , 20210609 , 20210610 , 20210611 , 20210612 , 20210613 , 20210614 , 20210615 , 20210616 , 20210617 , 20210618 , 20210619 , 20210620 , 20210621 , 20210622 , 20210623 , 20210624 , 20210625 , 20210626 , 20210627 , 20210628 , 20210629 , 20210630 , 20210701 , 20210702 , 20210703 , 20210704 , 20210705 , 20210706 , 20210707 , 20210708 , 20210709 , 20210710 , 20210711 , 20210712 , 20210713 , 20210714 , 20210715 , 20210716 , 20210717 , 20210718 , 20210719 , 20210720 , 20210721 , 20210722 , 20210723 , 20210724 , 20210725 , 20210726 , 20210727 , 20210728 , 20210729 , 20210730 , 20210731 , 20210801 , 20210802 , 20210803 , 20210804 , 20210805 , 20210806 , 20210807 , 20210808 , 20210809 , 20210810 , 20210811 , 20210812 , 20210813 , 20210814 , 20210815 , 20210816 , 20210817 , 20210818 , 20210819 , 20210820 , 20210821 , 20210822 , 20210823 , 20210824 , 20210825 , 20210826 , 20210827 , 20210828 , 20210829 , 20210830 , 20210831 , 20210901 , 20210902 , 20210903 , 20210904 , 20210905 , 20210906 , 20210907 , 20210908 , 20210909 , 20210910 , 20210911 , 20210912 , 20210913 , 20210914 , 20210915 , 20210916 , 20210917 , 20210918 , 20210919 , 20210920 , 20210921 , 20210922 , 20210923 , 20210924 , 20210925 , 20210926 , 20210927 , 20210928 , 20210929 , 20210930 , 20211001 , 20211002 , 20211003 , 20211004 , 20211005 , 20211006 , 20211007 , 20211008 , 20211009 , 20211010 , 20211011 , 20211012 , 20211013 , 20211014 , 20211015 , 20211016 , 20211017 , 20211018 , 20211019 , 20211020 , 20211021 , 20211022 , 20211023 , 20211024 , 20211025 , 20211026 , 20211027 , 20211028 , 20211029 , 20211030 , 20211031 , 20211101 , 20211102 , 20211103 , 20211104 , 20211105 , 20211106 , 20211107 , 20211108 , 20211109 , 20211110 , 20211111 , 20211112 , 20211113 , 20211114 , 20211115 , 20211116 , 20211117 , 20211118 , 20211119 , 20211120 , 20211121 , 20211122 , 20211123 , 20211124 , 20211125 , 20211126 , 20211127 , 20211128 , 20211129 , 20211130 , 20211201 , 20211202 , 20211203 , 20211204 , 20211205 , 20211206 , 20211207 , 20211208 , 20211209 , 20211210 , 20211211 , 20211212 , 20211213 , 20211214 , 20211215 , 20211216 , 20211217 , 20211218 , 20211219 , 20211220 , 20211221 , 20211222 , 20211223 , 20211224 , 20211225 , 20211226 , 20211227 , 20211228 , 20211229 , 20211230 , 20211231 , 20220101 , 20220102 , 20220103 , 20220104 , 20220105 , 20220106 , 20220107 , 20220108 , 20220109 , 20220110 , 20220111 , 20220112 , 20220113 , 20220114 , 20220115 , 20220116 , 20220117 , 20220118 , 20220119 , 20220120 , 20220121 , 20220122 , 20220123 , 20220124 , 20220125 , 20220126 , 20220127 , 20220128 , 20220129 , 20220130 , 20220131 , 20220201 , 20220202 , 20220203 , 20220204 , 20220205 , 20220206 , 20220207 , 20220208 , 20220209 , 20220210 , 20220211 , 20220212 , 20220213 , 20220214 , 20220215 , 20220216 , 20220217 , 20220218 , 20220219 , 20220220 , 20220221 , 20220222 , 20220223 , 20220224 , 20220225 , 20220226 , 20220227 , 20220228 , 20220301 , 20220302 , 20220303 , 20220304 , 20220305 , 20220306 , 20220307 , 20220308 , 20220309 , 20220310 , 20220311 , 20220312 , 20220313 , 20220314 , 20220315 , 20220316 , 20220317 , 20220318 , 20220319 , 20220320 , 20220321 , 20220322 , 20220323 , 20220324 , 20220325 , 20220326 , 20220327 , 20220328 , 20220329 , 20220330 , 20220331 , 20220401 , 20220402 , 20220403 , 20220404 , 20220405 , 20220406 , 20220407 , 20220408 , 20220409 , 20220410 , 20220411 , 20220412 , 20220413 , 20220414 , 20220415 , 20220416 , 20220417 , 20220418 , 20220419 , 20220420 , 20220421 , 20220422 , 20220423 , 20220424 , 20220425 , 20220426 , 20220427 , 20220428 , 20220429 , 20220430 , 20220501 , 20220502 , 20220503 , 20220504 , 20220505 , 20220506 , 20220507 , 20220508 , 20220509 , 20220510 , 20220511 , 20220512 , 20220513 , 20220514 , 20220515 , 20220516 , 20220517 , 20220518 , 20220519 , 20220520 , 20220521 , 20220522 , 20220523 , 20220524 , 20220525 , 20220526 , 20220527 , 20220528 , 20220529 , 20220530 , 20220531 , 20220601 , 20220602 , 20220603 , 20220604 , 20220605 , 20220606 , 20220607 , 20220608 , 20220609 , 20220610 , 20220611 , 20220612 , 20220613 , 20220614 , 20220615 , 20220616 , 20220617 , 20220618 , 20220619 , 20220620 , 20220621 , 20220622 , 20220623 , 20220624 , 20220625 , 20220626 , 20220627 , 20220628 , 20220629 , 20220630 , 20220701 , 20220702 , 20220703 , 20220704 , 20220705 , 20220706 , 20220707 , 20220708 , 20220709 , 20220710 , 20220711 , 20220712 , 20220713 , 20220714 , 20220715 , 20220716 , 20220717 , 20220718 , 20220719 , 20220720 , 20220721 , 20220722 , 20220723 , 20220724 , 20220725 , 20220726 , 20220727 , 20220728 , 20220729 , 20220730 , 20220731 , 20220801 , 20220802 , 20220803 , 20220804 , 20220805 , 20220806 , 20220807 , 20220808 , 20220809 , 20220810 , 20220811 , 20220812 , 20220813 , 20220814 , 20220815 , 20220816 , 20220817 , 20220818 , 20220819 , 20220820 , 20220821 , 20220822 , 20220823 , 20220824 , 20220825 , 20220826 , 20220827 , 20220828 , 20220829 , 20220830 , 20220831 , 20220901 , 20220902 , 20220903 , 20220904 , 20220905 , 20220906 , 20220907 , 20220908 , 20220909 , 20220910 , 20220911 , 20220912 , 20220913 , 20220914 , 20220915 , 20220916 , 20220917 , 20220918 , 20220919 , 20220920 , 20220921 , 20220922 , 20220923 , 20220924 , 20220925 , 20220926 , 20220927 , 20220928 , 20220929 , 20220930 , 20221001 , 20221002 , 20221003 , 20221004 , 20221005 , 20221006 , 20221007 , 20221008 , 20221009 , 20221010 , 20221011 , 20221012 , 20221013 , 20221014 , 20221015 , 20221016 , 20221017 , 20221018 , 20221019 , 20221020 , 20221021 , 20221022 , 20221023 , 20221024 , 20221025 , 20221026 , 20221027 , 20221028 , 20221029 , 20221030 , 20221031 , 20221101 , 20221102 , 20221103 , 20221104 , 20221105 , 20221106 , 20221107 , 20221108 , 20221109 , 20221110 , 20221111 , 20221112 , 20221113 , 20221114 , 20221115 , 20221116 , 20221117 , 20221118 , 20221119 , 20221120 , 20221121 , 20221122 , 20221123 , 20221124 , 20221125 , 20221126 , 20221127 , 20221128 , 20221129 , 20221130 , 20221201 , 20221202 , 20221203 , 20221204 , 20221205 , 20221206 , 20221207 , 20221208 , 20221209 , 20221210 , 20221211 , 20221212 , 20221213 , 20221214 , 20221215 , 20221216 , 20221217 , 20221218 , 20221219 , 20221220 , 20221221 , 20221222 , 20221223 , 20221224 , 20221225 , 20221226 , 20221227 , 20221228 , 20221229 , 20221230 , 20221231);
GO
CREATE PARTITION SCHEME ps_mba_cdr AS PARTITION pf_mba_cdr ALL TO ([PRIMARY]);
GO
CREATE TABLE mba.peak_reporting_data
(date_key INT NOT NULL
, hour TINYINT NOT NULL
, kb DECIMAL(19, 6) NULL
, msisdn_key INT NOT NULL
, sp_account SMALLINT NOT NULL
, is_dr_brand BIT NULL
, type_id_usage BIGINT NOT NULL
, product_identifier BIGINT NOT NULL
, kpi_service VARCHAR(15) NULL
, kpi_group VARCHAR(15) NULL
, sek INT NULL
, anzahl INT NULL
, kpi_type CHAR(1) NOT NULL
, anzahl_begonnen INT NULL
, anzahl_geendet INT NULL
, anzahl_durchgaengig INT NULL
, anzahl_nur_in_hour INT NULL)
ON ps_mba_cdr(date_key);
GO
ALTER TABLE mba.peak_reporting_data SET (LOCK_ESCALATION = AUTO);
GO
CREATE CLUSTERED COLUMNSTORE INDEX icc_peak_reporting_data ON mba.peak_reporting_data ON ps_mba_cdr(date_key);
GO
CREATE TABLE mba.peak_reporting_monats_peaks
(month_key INT NOT NULL
, date_key INT NOT NULL
, week_day VARCHAR(30) NOT NULL
, hour TINYINT NOT NULL
, kpi_type CHAR(1) NOT NULL
, gb DECIMAL(38, 6) NULL
, sek BIGINT NULL
, anzahl INT NOT NULL
, gigabit_pro_sekunde DECIMAL(38, 6) NULL
, prozent_dr_brand DECIMAL(9, 6) NOT NULL
, tages_peak TINYINT NOT NULL
, monats_peak SMALLINT NOT NULL
, refresh_date DATETIME2(0) NOT NULL) ON [PRIMARY];
GO
CREATE UNIQUE CLUSTERED INDEX iuc_peak_reporting_monats_peaks__month_key__kpi_type__monats_peak
ON mba.peak_reporting_monats_peaks (month_key, kpi_type, monats_peak)
WITH (DROP_EXISTING = OFF, FILLFACTOR = 98, DATA_COMPRESSION = ROW, SORT_IN_TEMPDB = ON
, STATISTICS_INCREMENTAL = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX inc_peak_reporting_monats_peaks__date_key
ON mba.peak_reporting_monats_peaks (date_key, kpi_type, tages_peak)
WITH (DROP_EXISTING = OFF, FILLFACTOR = 99, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON
, STATISTICS_INCREMENTAL = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];
GO
我添加了 DDL 并使用通常的 INNER JOIN 没有任何提示，这需要 2 分钟，因为它在扫描后添加了过滤器，但这次它做了一个嵌套循环。
如果我强制使用 HASH JOIN，我现在会得到一个新计划： https ://www.brentozar.com/pastetheplan/?id=HyLDWiPFt该计划不再有过滤器运算符，但只运行 11 秒，因为它会消除段（只有 44 43k 读取）。它还说该表已分区，但它使用了 0 个分区（错误，否则我将没有结果）。悬而未决的问题仍然是：为什么将它用作嵌套循环的过滤器。
我知道您无法获得 Columnstore Index Seek，但如果表是分区的，它至少可以将 SEEK Predicate（通常是分区）添加到 Columnstore Index Scan 运算符。
您几乎永远不需要在嵌套循环连接的内侧进行列存储扫描。
在这种情况下，引擎不支持批处理模式（批处理模式列存储扫描无法倒带）。请注意，您上传的计划显示列存储扫描以行模式运行。
单独的过滤器并不是特别有趣。不可分割的谓词不能总是下推到子扫描或搜索。在这种情况下，引擎不会将动态分区消除与剩余谓词结合起来。这是一个低效率，但不是这里的主要问题。
丢失连接提示，让优化器选择它想要的计划。您可能会得到类似于您上传的哈希提示计划的内容，该计划在 1600 毫秒内运行。是的，整个列存储都被扫描了，但是在散列连接处创建的位图非常有效——将 35B 行减少到 37M。整个过程在1.5s内完成，一点也不差。请注意，批处理模式位图允许行组级别的消除（包括预读）和其他技巧，因此您最终不会读取 35B 行。
顺便说一句，您原来的嵌套循环计划确实包括分区消除：
如果您真的想采用分区消除循环式策略 - 而且这样做可能很值得 - 您需要做一些额外的工作才能在嵌套的内侧获得有效的批处理模式列存储扫描循环加入。
正如我所说，这种安排不可能自然而然地得到。您需要在单独的执行范围中“隐藏”内部操作，以便为重复的列存储扫描获取（可能是并行的）批处理模式执行。
这可以通过以下方式实现：
左连接可以很容易地转换为应用。相关参数将是
date_key和[hour]。然后，您将使用APPLY从peak_reporting_monats_peaks调用每一行的函数。如果操作正确，您将获得分区消除、并行性和批处理模式列存储扫描。
提供的代码中的快速示例：
电视节目：
询问：
计划：
TVF 扫描属性（批处理模式、分区消除）
感谢 Paul White 的回答（特别是关于批处理模式与行模式的东西），我做了更多的测试（由于格式/长度限制，将此作为答案而不是评论发布）。备注：关于数据没关系，如果我使用 INNER 或 LEFT JOIN，因为 peak_reporting_monats_peaks 表是大 peak_reporting_data 表的物化聚合。所以一个 JOIN 总是会找到匹配的。
另一方面，对于 SQL 服务器/执行计划/性能来说，这似乎很重要
Conclution: LEFT JOIN / LEFT HASH JOIN / INNER HASH JOIN / OUTER APPLY are the fastest operators (on my SQL 2016 with my tables - for someone else this could differ), although they are elimination the partitions only indirect by segment elimination
It makes sense, that the pairs LEFT JOIN / OUTER APPLY and INNER JOIN / CROSS APPLY performs equal, because there is no APPLY operator and the server will use a LEFT / INNER JOIN instead (sometimes loop, somethimes hash and I'v seen a MERGE JOIN too) when building the execution plan.
It is strange, that "default" CROSS APPLY / INNER JOIN is slow while OUTER APPLY / LEFT JOIN is fast.
我仍然想知道，为什么 SQL 服务器不能产生一个完美的计划，它使用散列连接、并行、批处理模式和段-+分区消除。由于它“下推” date_key + 小时位图的哈希位图来进行段消除，它应该也能够消除基于相同哈希位图的分区，因此它只需要查看 700 个分区中的 3 个. 另一方面，这只会带来非常轻微的性能提升（除非您有 14k 个分区 :-)）
由 INNER LOOP JOIN 生成的计划绝对没有意义，我不知道为什么 SQL 服务器决定使用这个计划（请参阅https://www.brentozar.com/pastetheplan/?id=rybwcpDtF）
编辑：我自己的表值函数的代码（保罗在他的答案中添加了另一个函数）：