Início / dba / Perguntas / 303459
Accepted
Thomas Franz
Asked: 2021-12-04 02:13:49 +0800 CST

Columnstore: plano de execução terrível - filtre em vez de buscar

  • 772

Veja o plano de execução em https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise)

  • Eu tenho uma tabela de datawarehouse peak_reporting_data que rastreia a atividade por dia e hora e contém cerca de 4 bilhões de linhas por meses com um columnstoreindex clusterizado particionado por date_key (uma partição por dia)
  • na tabela peak_reporting_monats_peaks eu agreguei esta tabela e a ordenei/classifiquei pelo pico do mês. Existem 3 tipos de atividade (kpi_type), para cada eu tenho até 24h * 31 dias = 744 linhas por mês com [monats_peak] ranqueado de 1 a 744. Tem um índice único sobre month_key, kpi_type, monats_peak.
  • Para a hora mais ativa (por kpi_type) preciso de mais alguns detalhes, então escrevi a seguinte consulta/visualização:
    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

Como existem exatamente 3 linhas com monats_peak = 1 por mês em peak_reporting_monats_peaks, seria lógico para o SQL Server fazer 3 loops e consultas aninhados / agregar a tabela grande com base em date_key, hour e kpi_type (o que seria feito em talvez 2 segundos , como testei com um cursor).

Mas infelizmente ele lê sempre a tabela inteira (36 bilhões de linhas no momento) sem nenhum predicado pedicate/busca no operador ColumnstoreIndexScan, independente do que eu tente. Em vez de 2 segundos, a consulta precisa de 2 a 3 minutos para ser concluída por esse motivo.

Se eu usar um INNER LOOP JOIN em vez de LEFT LOOP JOIN ele remove o operador de filtro, mas adiciona um spool de tabela preguiçoso antes do JOIN, um INNER JOIN usual (sem LOOP) levaria a um HASH JOIN (novamente em toda a tabela).

Alguma idéia de como (exceto um procedimento ou função de valor de tabela de várias linhas com um cursor) eu poderia "forçar" o servidor SQL a fazer as 3 pesquisas simples (+ agregações) em vez de ler toneladas de dados irrelevantes?

As estatísticas etc. estão corretas, ele sabe exatamente que haverá 3 linhas em peak_reporting_monats_peaks e adicionei uma estatística explícita em date_key + hour ao grande peak_reporting_data

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

Eu adicionei o DDL e com um INNER JOIN normal sem nenhuma dica demora 2 min pois adiciona o filtro logo após o scan, mas desta vez fez um NESTED LOOP.

Se eu forçar um HASH JOIN recebo um novo plano agora: https://www.brentozar.com/pastetheplan/?id=HyLDWiPFt de 43k lidos). Ele diz ainda que a tabela é particionada, mas usou 0 partições (errado, caso contrário eu não teria resultado). A questão em aberto ainda é: por que usa um filtro para NESTED LOOPS.

Eu sei que você não pode obter um Columnstore Index Seek, mas se a tabela for particionada, ela poderia pelo menos adicionar um SEEK Predicate (geralmente a partição) ao operador Columnstore Index Scan.

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Você praticamente nunca desejará uma verificação de armazenamento de colunas no lado interno de uma junção de loops aninhados.

    O mecanismo não oferece suporte ao modo de lote nesse cenário (as verificações de armazenamento de coluna do modo de lote não podem ser rebobinadas). Observe que o plano que você carregou mostra a verificação do armazenamento de colunas executada no modo de linha .

    O filtro separado não é particularmente interessante. Predicados não sargáveis ​​nem sempre podem ser empurrados para uma varredura ou busca filho. Nesse caso, o mecanismo não combinará a eliminação de partição dinâmica com predicados residuais. É uma ineficiência, mas não o principal problema aqui.

    Perca as dicas de junção e deixe o otimizador escolher o plano que deseja. Você provavelmente obterá algo semelhante ao plano com sugestão de hash que você carregou, que foi executado em 1600ms. Sim, todo o armazenamento de colunas é verificado, mas os bitmaps criados na junção de hash são muito eficazes - reduzindo as linhas de 35B para 37M. Todo o processo é concluído em 1,5s, o que não é tão ruim assim. Observe que os bitmaps do modo de lote permitem a eliminação no nível do grupo de linhas (incluindo para leitura antecipada) e outros truques, para que você não acabe lendo 35B linhas.

    Como um aparte, seu plano original de loops aninhados incluiu a eliminação de partição:

    Propriedades de digitalização

    Se você realmente deseja seguir a estratégia de estilo de loops de eliminação de partição - e pode valer a pena fazê-lo - você precisará fazer um pouco de trabalho extra para obter uma varredura de armazenamento de coluna em modo de lote eficiente no lado interno de um arquivo aninhado laços se unem.

    Como eu disse, não é possível obter esse arranjo naturalmente. Você precisa 'ocultar' a operação do lado interno em um escopo de execução separado para obter a execução em modo de lote (potencialmente paralela) para as varreduras repetidas de armazenamento de coluna.

    Isso pode ser alcançado por:

    1. Reescrevendo a junção esquerda como uma aplicação
    2. Colocando o lado de aplicação em uma função com valor de tabela (não em linha!)

    A junção esquerda pode ser convertida em uma aplicação com bastante facilidade. Os parâmetros correlacionados serão date_keye [hour]. Você usaria então APPLYpara chamar a função para cada linha de peak_reporting_monats_peaks .

    Se você fizer isso corretamente, obterá eliminação de partição, paralelismo e a verificação de armazenamento de coluna no modo de lote.

    Exemplo rápido do código fornecido:

    TVF:

    CREATE FUNCTION dbo.F
(
    @date_key integer,
    @hour tinyint
)
RETURNS @T table
(
    kpi_type char(1) NOT NULL,
    is_dr_brand bit NULL,
    type_id_usage bigint NOT NULL,
    product_identifier bigint NOT NULL,
    gb decimal(19,6) NOT NULL,
    sek bigint NOT NULL,
    anzahl integer NOT NULL,
    gbits decimal (19,6) NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
    INSERT @T
    SELECT 
        prd.kpi_type,
        prd.is_dr_brand,
        prd.type_id_usage,
        prd.product_identifier,
        gb = SUM(prd.kb) / 1024.0 / 1024.0,
        sek = SUM(CAST(prd.sek AS BIGINT)),
        anzahl = SUM(prd.anzahl),
        gbits = SUM(prd.kb) / 439453125.0
    FROM mba.peak_reporting_data AS prd
    WHERE
        prd.date_key = @date_key
        AND prd.[hour] = @hour
    GROUP BY
        prd.kpi_type,
        prd.is_dr_brand,
        prd.type_id_usage,
        prd.product_identifier;

    RETURN;
END;

    Consulta:

    SELECT
    PRMP.month_key,
    PRMP.date_key,
    [PRMP].[hour],
    F.kpi_type,
    F.is_dr_brand,
    F.type_id_usage,
    F.product_identifier,
    F.gb,
    F.sek,
    F.anzahl,
    F.gbits
FROM mba.peak_reporting_monats_peaks AS PRMP
OUTER APPLY dbo.F(PRMP.date_key, PRMP.[hour]) AS F
WHERE
    PRMP.monats_peak = 1
    AND PRMP.month_key = 202107;

    Plano:

    Plano

    Propriedades de varredura TVF (modo em lote, eliminação de partição)

    Propriedades

    • 7

  2. Graças à resposta de Paul White (particularmente em relação ao modo de lote vs. modo de linha), fiz mais alguns testes (postei isso como resposta em vez de comentário, por causa da formatação / limite de comprimento). Observação: em relação aos dados não importa se eu uso INNER ou LEFT JOIN, pois a tabela peak_reporting_monats_peaks é um agregado materializado da tabela big peak_reporting_data. Portanto, um JOIN sempre encontrará correspondências.

    Para o servidor SQL / plano de execução / desempenho, por outro lado, parece ser importante

    • LEFT JOIN / LEFT HASH JOIN / INNER HASH JOIN / OUTER APPLY : rápido (3 seg), hash join, modo de lote, paralelo, sem eliminação de partição (lê todos os dias), mas eliminação de segmento (44 segmentos lidos, 43k segmentos ignorados)
    • LEFT LOOP JOIN / INNER JOIN / CROSS APPLY : lento (1 min), loop aninhado, modo de linha, paralelo, operador FILTER, eliminação de partição (lê apenas os dias relevantes), mas sem eliminação de segmento (0 de 410 segmentos ignorados)
    • INNER LOOP JOIN : extremamente lento (cancelado por engano após ~30 min em cerca de 10% seguindo o plano de consulta ao vivo), pesquisa aninhada, modo de linha, paralelo, sem FILTER, mas operador de Spool de tabela, nem partição nem eliminação de segmento -> lê e agrega toda a tabela
    • Função de valor da tabela de Paul : rápida (1 sek), pesquisa aninhada (na instrução de seleção externa), modo de lote (dentro da função), eliminação paralela, de segmento e partição
    • função ou procedimento de valor de tabela próprio (multi-instrução, usando um cursor para obter o dia/hora de pico dos três kpi_types e inseri-lo no resultado): rápido (2 segundos), loop aninhado "manual" (sem junção dentro do cursor ), elimina segmentos e partições (lê cerca de 30 segmentos)

    Conclusão: LEFT JOIN / LEFT HASH JOIN / INNER HASH JOIN / OUTER APPLY são os operadores mais rápidos (no meu SQL 2016 com minhas tabelas - para outra pessoa isso pode ser diferente), embora sejam eliminação das partições apenas indiretas por eliminação de segmento

    Faz sentido que os pares LEFT JOIN / OUTER APPLY e INNER JOIN / CROSS APPLY tenham o mesmo desempenho, porque não há operador APPLY e o servidor usará um LEFT / INNER JOIN em vez disso (às vezes loop, às vezes hash e eu vi um MERGE JOIN também) ao construir o plano de execução.

    É estranho que o CROSS APPLY / INNER JOIN "padrão" seja lento enquanto o OUTER APPLY / LEFT JOIN é rápido.

    Eu ainda me pergunto, por que o servidor SQL não pode produzir um plano perfeito onde ele usa uma junção de hash, paralelismo, modo de lote e eliminação de partição + segmento. Como ele "empurra" o bitmap de hash da date_key + bitmap da hora para fazer a eliminação do segmento, ele também deve ser capaz de eliminar partições baseadas no mesmo bitmap de hash, portanto, seria necessário examinar apenas 3 das 700 partições . Por outro lado, seria apenas um ganho de desempenho muito pequeno (exceto que você tem partições de 14k :-))

    O plano produzido por um INNER LOOP JOIN não faz absolutamente nenhum sentido e não faço ideia, por que o servidor SQL decide usar esse plano (consulte https://www.brentozar.com/pastetheplan/?id=rybwcpDtF )

    Edit: código da minha própria função de valor de tabela (Paul adicionou outra função em sua resposta):

    CREATE OR ALTER FUNCTION mba.f_peak_reporting_monats_peak_details 
         (@month_key INT, @kpi_type CHAR(1))
    RETURNS @tbl TABLE ( [monat] int, [date_key] int, [hour] tinyint, [kpi_type] char(1), [is_dr_brand] bit, [type_id_usage] bigint, [product_identifier] bigint, [gb] decimal(38,6), [sek] bigint, [anzahl] int, [gbits] decimal(38,6) )
    AS
    BEGIN
        DECLARE @date_key INT
              , @hour   TINYINT
    
        DECLARE curKPIType CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
                SELECT prmp.date_key, prmp.hour, prmp.kpi_type
                  FROM mba.peak_reporting_monats_peaks AS prmp
                 WHERE prmp.month_key   = @month_key
                   AND prmp.monats_peak = 1
                   AND (prmp.kpi_type   = @kpi_type OR @kpi_type IS NULL)
                ;
        OPEN curKPIType;
        
        WHILE 1 = 1
        BEGIN
            FETCH NEXT FROM curKPIType INTO @date_key, @hour, @kpi_type;
            IF @@fetch_status <> 0 BREAK;
        
            INSERT INTO @tbl (monat, date_key, hour, kpi_type, is_dr_brand, type_id_usage, product_identifier, gb, sek, anzahl, gbits)
            SELECT 1-- @month_key
                 , 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 mba.peak_reporting_data AS prd 
             WHERE prd.date_key = @date_key
               AND prd.hour     = @hour
               AND prd.kpi_type = @kpi_type
             GROUP BY prd.date_key
                    , prd.kpi_type
                    , prd.is_dr_brand
                    , prd.hour
                    , prd.type_id_usage
                    , prd.product_identifier
            ;
        END;
        
        CLOSE curKPIType;
        DEALLOCATE curKPIType;
    
        RETURN;
    END
    ;      
    GO
    • 1

relate perguntas