SQLServer query blocking itself, how to solve?

Question

I have this query

;WITH --first CTE is your data set example
    CTE
    AS
    (
        SELECT *
        FROM (VALUES
                ('2023-04-06', 0029, 'D', 'ABCD', 1, 100),
                ('2023-04-06', 0027, 'D', 'ABCD', 1, 200),
                ('2023-04-06', 0044, 'D', 'ABCD', 1, 300),
                ('2023-04-06', 0042, 'D', 'ABCD', 1, 400),
                ('2023-04-06', 0029, 'C', 'ABCD', 1, 500),
                ('2023-04-06', 0069, 'C', 'ABCD', 1, 600),
                ('2023-04-06', 0067, 'C', 'XXCD', 1, 700),
                ('2023-04-06', 0089, 'C', 'ABCD', 1, 800),
                ('2023-04-06', 0079, 'C', 'XXCD', 1, 900),
                ('2023-04-06', 0084, 'C', 'ABCD', 1, 1000)) AS T([BOOKING_DATE],[TIME_INTERVAL],[DB_CR_CODE],[CHANNEL],[NBR_OF_TXN],[AMOUNT])
    ),
    CTE2 --aggregate data 
    AS
    (
        SELECT
            booking_date, interval, product_group1, product_group2,
            SUM(nbr_of_txn) AS nbr_txn, SUM(amount) AS amount
        FROM
            (SELECT
                BOOKING_DATE,
                CASE
             WHEN TIME_INTERVAL BETWEEN 0000 AND 0030 THEN 1
             WHEN TIME_INTERVAL BETWEEN 0031 AND 0060 THEN 2
             WHEN TIME_INTERVAL BETWEEN 0061 AND 0090 THEN 3
             ELSE 99 
         END AS interval,
                CASE
             WHEN DB_CR_CODE = 'C' THEN 'Credit'
             WHEN DB_CR_CODE = 'D' THEN 'Debit'        
             ELSE '' 
         END AS PRODUCT_GROUP1,
                CASE 
             WHEN DB_CR_CODE = 'C' AND CHANNEL = 'ABCD' THEN 'Credit_ABCD'  
             ELSE '' 
         END AS PRODUCT_GROUP2,
                NBR_OF_TXN, AMOUNT
            FROM
                CTE) a
        GROUP BY booking_date, interval, PRODUCT_GROUP1, PRODUCT_GROUP2
    )

    --UNION from CTE2
    SELECT booking_date, interval, product_group1, nbr_txn, amount
    FROM CTE2
    WHERE product_group1 !=''
    UNION ALL
    SELECT booking_date, interval, product_group2, nbr_txn, amount
    FROM CTE2
    WHERE product_group2 !=''

On a daily basis it contains 10 to 15 million records. When I run it for a day, it immediatelly goes into suspended status. I cancelled it after 3.5 hours

enter image description here

It looks like it is blocking itself because of that SELECT (STATMAN).

enter image description here

What is that SELECT(STATMAN)? Why is it doing that, while I am only reading. How to solve this?

I hope someone can help. Small addition: in the end this query will be embedded in a view.

Regards Ron

Using SSMS v18.2.1

Answer 1

SELECT (STATMAN) is a statistics update. This is normally set to happen automatically when you read from a table, if statistics need updating, but by default will happen synchronously (ie you need to wait for it).

You have a number of options:

• Turn off automatic creation of statistics using SET AUTO_CREATE_STATISTICS OFF. You would be advised to create your own statistics manually.
• Turn off automatic statistics update using SET AUTO_UPDATE_STATISTICS OFF, or using NO_RECOMPUTE on an individual statistics object. This is not advisable unless you know what you are doing, and you would need to have some process to update them manually or on a schedule.
• Set statistics to update asynchronously, so that your query does not have to wait, using SET AUTO_UPDATE_STATISTICS_ASYNC ON. Note that this option is not enabled by default but is normally recommended. This option may be problematic if you are querying a table immediately after loading it.