Stale Statistics and View performance on SQL Server 2016

Question

I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.

So my doubt essentially is -

1. What could be the reason that even with Auto Update Statistics enabled and level 130, the statistics are stale?

2. Is it advisable (or a good idea) to have a utility job every night, post nightly ETL batch completion to update the statistics of all tables?

.

SELECT  dim_isc.<cols>,
        dim_cr.<cols>,
        vw_cs.<cols>,
        --- **problem is here, guess** ---
        isnull(vw_cs.col_1,'  '),
        isnull(vw_cs.col_2,'  '),
        isnull(vw_cs.col_3,'  '),
        isnull(vw_cs.col_4,'  '),
        isnull(vw_cs.col_5,'  ')
FROM    dim_isc
        left join dim_ab on (dim_isc = dim_ab)
        join fact_cc on (fact_cc.cola = dim_isc.cola)
        join dim_cr on (fact_cc.colz = dim_cr.colz)
        join (
            select <cols> from dim_m join fcc
        ) rp on (fcs.coly = rp.coly)
        join vw_cs on (vs_cs.colx = dim_isc.colx)
WHERE 
        rp = yyy -- input 1
        and dim_isc >= xxxx -- input 2
    

Few things to note, might help you advising further -

1. Removing the ISNULL from the SELECT columns helps resolve the query slowness. (I understand this could be stale statistics in the underlying view table(s) or column(s), resulting in a bad plan.)

2. vw_cs is a nested view with 4 levels i.e. the base table lies in the 4th view.

3. Updating statistics (fullscan) of the table from which the columns (vw_cs.col_1, col_2, col_3, col_4 col_5) are sourced, resolved the query slowness.

4. This table gets populated by nightly ETL jobs, as truncate & full load.

Any thoughts please?

(Apologies for not providing the execution plan, reasons - confidentiality with object names and was too huge to be exported. Happy to share information like scans methods chosen, cardinality, etc. Let me know.)

Database config -

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Thanks

Ranit

Answer 1

1. What could be the reason that even with Auto Update Statistics enabled and level 130, the statistics are stale?

Automatic statistic updates are not enough

You should be proactively updating statistics (more often than Auto Update Stats does) with FULLSCAN, to get better cardinality estimations and better query execution plans.

For more info watch this (Brent talks statistics sometime later in the video):

https://www.brentozar.com/training/think-like-sql-server-engine/

2. Is it advisable (or a good idea) to have a utility job every night, post nightly ETL batch completion to update the statistics of all tables?

Yes, update statistics with FULLSCAN for tables affected by the ETL batch processing