locked
Update Statistics -Takes very long after Upgrading to 2019 from 2008R2 RRS feed

  • Question

  • Recently upgraded a SQL2008 R2 database to SQL2019 – with compatibility Mode 150

    Largest table has 330 million records and 3.1 TB

    Update Statistics was running in about 10 minutes on the SQL 2008 R2 Server

    On 2019 Server it takes forever. Can someone explain why ?

    I understand cardinality compute has change since 2016 but stats should be still valid after upgrade.

    Unfortunately table is not partitioned - and has a Nvarchar(max) with large String ( BLOB): this column has a Column Statistic

    Saturday, July 25, 2020 4:21 AM

All replies

  • Unless you can show the individual statistics updated along with the sample percentage, comparing statistics update time from one run to another has no correlation, never mind the change from 2008R2 to 2019. In some version of SQL Server, a more sophisticated string statistics capability was introduce, though I think this was from 2000 to 2005 (some one please chime in on this).

    I have had a complaint against SQL Server index statistics for some time. Basically, indexes in which the lead key is not unique should have full scan statistics, because it is not a true random row sample. Instead it is a random page, all rows with the page - sample. If 2019 fixed this, then that would explain longer statistics update, but I was not aware of it.

    see this on why indexes in which the lead key is not unique should have fullscan

    http://qdpma.com/CBO/Statistics.html

    this is my statistics update job to implement this http://qdpma.com/SQL/sp_updatestats2.html


    jchang

    Saturday, July 25, 2020 10:54 PM