Columnstore: terrible execution plan - filter instead of seek

Question

See the execution plan on https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise)

• I have a datawarehouse table peak_reporting_data which tracks the activity per day and hour and contains about 4 billion rows per months with an clustered columnstoreindex partitioned by date_key (one partition per day)
• in the table peak_reporting_monats_peaks I have aggregated this table and ordered / ranked it by the month peak. There are 3 types of activity (kpi_type), for each I have up to 24h * 31 days = 744 rows per month with [monats_peak] ranked from 1 to 744. It has an unique index over month_key, kpi_type, monats_peak.
• For the most active hour (per kpi_type) I need some more details, so I wrote the following query / view:

    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

Since there are exactly 3 rows with monats_peak = 1 per month in peak_reporting_monats_peaks, it would be logical for SQL server to do 3 nested loops and queries / aggregate the big table based on date_key, hour and kpi_type (which would be done in maybe 2 seconds, as I tested with a cursor).

But sadly it reads always the whole table (36 billion rows at the moment) without any pedicate / seek predicate in the ColumnstoreIndexScan operator, regardless, what I try. Instead of 2 seconds the query needs 2-3 minutes to finish for this reason.

If I use an INNER LOOP JOIN instead of LEFT LOOP JOIN it removes the filter operator but adds a lazy table spool before the JOIN, a usual INNER JOIN (without LOOP) would lead to a HASH JOIN (again over the whole table).

Any ideas, how (except a procedure or multiline table value function with a cursor) I could "force" the SQL server to do the 3 simple lookups (+ aggregates) instead of reading tons of irrelevant data?

Statistics etc. are correct, it knows exactly, that there will be 3 rows in peak_reporting_monats_peaks and I added an explicit statistic on date_key + hour to the big 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

I added the DDL and with a usual INNER JOIN without any hint it takes 2 min because it adds the filter just after the scan, but it did a NESTED LOOP this time.

If I force an HASH JOIN I get a new plan now: https://www.brentozar.com/pastetheplan/?id=HyLDWiPFt This plan has no longer a filter operator but runs only 11 seconds because it does segment elimination (just 44 of 43k read). It says furthermore that the table is partitioned but it used 0 partitions (wrong, otherwise I would have no result). The open question is still: why uses it a filter for NESTED LOOPS.

I know that you can't get a Columnstore Index Seek, but if the table is partitioned, it could at least add a SEEK Predicate (usually the partition) to the Columnstore Index Scan operator.

Answer 1

You will pretty much never want a column store scan on the inner side of a nested loops join.

The engine doesn't support batch mode in that scenario (batch mode column store scans can't be rewound). Notice the plan you uploaded shows the column store scan ran in row mode.

The separate Filter isn't particularly interesting. Non-sargable predicates can't always be pushed down to a child scan or seek. In this case, the engine won't combine dynamic partition elimination with residual predicates. It's an inefficiency, but not the main issue here.

Lose the join hints and let the optimizer pick the plan it wants. You will likely get something similar to the hash-hinted plan you uploaded, which ran in 1600ms. Yes, the whole column store is scanned but the bitmaps created at the hash join are very effective - reducing the 35B rows to 37M. The entire process completes in 1.5s, which isn't too bad at all. Note the batch mode bitmaps allow rowgroup-level elimination (including for read-ahead) and other trickery, so you don't end up reading 35B rows.

As an aside, your original nested loops plan did include partition elimination:

---

If you really want to pursue the partition-elimination loops-style strategy - and it may well be worth doing so - you will need to do a bit of extra work to get an efficient batch mode column store scan on the inner side of a nested loops join.

As I said, it is not possible to get this arrangement naturally. You need to 'hide' the inner-side operation in a separate execution scope to get (potentially parallel) batch mode execution for the repeated column store scans.

This can be achieved by:

1. Rewriting the left join as an apply
2. Putting the apply side in a table-valued (not inline!) function

The left join can be converted to an apply quite easily. The correlated parameters will be date_key and [hour]. You would then use APPLY to call the function for each row from peak_reporting_monats_peaks.

If you do this right, you will get partition elimination, parallelism, and the batch mode column store scan.

---

Quick example from the supplied code:

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;

Query:

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;

Plan:

TVF scan properties (batch mode, partition elimination)

Answer 2

Thanks to Paul White's answer (particularly regarding the batch mode vs. row mode stuff) I did some more tests (posted this as answer instead of comment, because of formating / lenght limit). Remark: regarding the data it does not matter, if I use INNER or LEFT JOIN, because the peak_reporting_monats_peaks table is a materialized aggregate of the big peak_reporting_data table. So a JOIN will always find matches.

For the SQL server / execution plan / performance on the other hand it seems to be important

• LEFT JOIN / LEFT HASH JOIN / INNER HASH JOIN / OUTER APPLY: fast (3 sek), hash join, batch mode, parallel, no partition elimination (reads all days) but segment elimination (44 segments read, 43k segments skipped)
• LEFT LOOP JOIN / INNER JOIN / CROSS APPLY: slow (1 min), nested loop, row mode, parallel, FILTER operator, partition elimination (reads only the relevant days) but no segment elimination (0 of 410 segments skipped)
• INNER LOOP JOIN: extrem slow (canceled by mistake after ~30 min at about 10 % following to the live query plan), nested lookup, row mode, parallel, no FILTER but Table Spool operator, neither partition nor segment elimination -> reads and aggregates the whole table
• Paul's table value function: fast (1 sek), nested lookup (in the outer select statement), batch mode (inside the function), parallel, segment and partition elimination
• own table value function or procedure (multi-statement, using a cursor to get the peak day / hour of the three kpi_types and insert it into the result): fast (2 seconds), "manual" nested loop (no join inside the cursor), does segment and partition elimination (reads about 30 segments)

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.

I still wonder, why the SQL server can't produce a perfect plan where it uses a hash join, parallelism, batch mode and segment- + partition elimination. Since it "push down" the hash bitmap of the date_key + bitmap of the hour to do the segment elimination, it should be able to eliminate partitions based on the same hash bitmap too, so it would need to look into just 3 of 700 partitions. On the other hand it would be only a very slightly performance gain (except you have 14k partitions :-))

The plan produces by an INNER LOOP JOIN makes absolute no sense and I have no idea, why the SQL server decides to use this plan (see https://www.brentozar.com/pastetheplan/?id=rybwcpDtF)

Edit: code of my own Table Value Function (Paul added another function into his answer):

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