I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -
What could be the reason that even with Auto Update Statistics enabled and level 130, the statistics are stale?
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 -
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.)
vw_csis a nested view with 4 levels i.e. the base table lies in the 4th view.
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.
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)