Sql-server – Stale Statistics and View performance on SQL Server 2016

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