Sample Header Ad - 728x90

Performance issue after migration to SQL Server 2019

1 vote
1 answer
875 views
I am troubleshooting a performance issue on a job that is taking 5h longer to complete on SQL Server 2019 compared to when it ran on 2012. One of the problematic steps of the job is during a batch load into a Operational Data Store type of database (ODS) which gets data from different sources and inserts into a table that is truncated every night and reloaded, the indexes are disabled before the load and they are rebuilt in the end one by one and by name, not with rebuild all. Even though there are four disabled indexes in the table, I saw that their associated statistics are still getting updated daily: Statistic | Statistic Type | is_disabled | Stats Last Updated | Rows | Rows Sampled | Unfiltered Rows | Row Modifications | Histogram Steps --------- | -------------- | ----------- | ------------------ | ---- | ------------ | ----------------- | ----------------- | --------------- IX_1|Index Statistic|Yes|2023-07-27 03:07:00.4566667|238634988|617299|238634988|4963|3 IX_2|Index Statistic|Yes|2023-07-27 03:07:24.8866667|238634988|617299|238634988|4963|200 IX_3|Index Statistic|Yes|2023-07-27 08:29:01.5266667|238634988|617290|238634988|4963|199 IX_4|Index Statistic|Yes|2023-07-27 03:07:34.0233333|238634988|617299|238634988|4963|192 Furthermore I also found out the execution plan of some queries consider these statistics from the disabled indexes, is this normal?
Due to nature of this database which truncates and loads tables every day should I turn off the AUTO_UPDATE_STATISTICS either for the whole database or for some of the tables ? When I query the sys.dm_db_stats_properties there are many auto-created statistics with more than 200 million row modifications. Any inputs will be appreciated, thanks!
Asked by racoon (13 rep)
Jul 27, 2023, 06:56 PM
Last activity: Aug 28, 2023, 08:18 PM