1

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

5
  • Removing the ISNULL from the SELECT columns helps resolve the query slowness <-- does this mean removing the ISNULL only fixes the problem or when you remove those columns completely? Also: as I understand updating statistics without specifiying fullscan doesn't fix the issue?
    – rois
    Mar 16 at 17:03
  • 1)Just removing the ISNULL fixes slowness. 2)Didn't try w/o fullscan (but tried rebuilding indexes and that too serves the purpose)
    – ranit.b
    Mar 16 at 17:13
  • I suspect it's more likely a join condition rather than the select itself. Do you have any unusual joins: convert/cast or other function, or perhaps one of the joins is a very skewed table? Mar 16 at 20:43
  • Please add the query execution plan via Paste the Plan.
    – John K. N.
    Mar 16 at 21:19
  • Thanks @Charlieface. Yes, there were several Nested Loops. I agree with your thought on joining with skewed table(s).
    – ranit.b
    Mar 17 at 9:39
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/

  1. 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

1
  • Thanks for your response @Aleksey
    – ranit.b
    Mar 18 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.