1

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?

<OptimizerStatsUsage>
              <StatisticsInfo Database="[x]" Schema="[dbo]" Table="[y]" Statistics="[IX_3]" ModificationCount="22" SamplingPercent="0.257925" LastUpdate="2023-07-20T09:51:44.88" />
              <StatisticsInfo Database="[x]" Schema="[dbo]" Table="[y]" Statistics="[IX_2]" ModificationCount="0" SamplingPercent="0.257925" LastUpdate="2023-07-20T03:28:08.59" />

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!

7
  • Any chance you have or can provide the actual execution plan of the slow running job? Is it just an INSERT that is taking so long?
    – J.D.
    Jul 27, 2023 at 19:36
  • Did you change the database compatibility level? Jul 27, 2023 at 22:58
  • I don't think disabling indexes disables the statistics. You could delete them if you want. Jul 28, 2023 at 0:24
  • @J.D. the job runs a SSIS package, so far I was able to identify a few steps that are slow, but each of these steps have lots of SQL (inserts/updates), unfortunately I can't share the complete plan with the names of tables / columns.
    – Andre
    Jul 28, 2023 at 11:59
  • @ErikDarling I changed the compatibility level of all databases from 110 to 150 and deleted also the statistics so they would be recreated.
    – Andre
    Jul 28, 2023 at 12:02

1 Answer 1

0

First of all try to exclude four disabled indexes from the equation by dropping/recreating it instead of disabling/enabling.

If the response time do not change so those indexes are not in charge. Also enable query store to get most expensive queries and waits associated with those queries to find your problem child.

I don't think disabling AUTO_UPDATE_STATISTICS could help you.

2
  • I checked the query store and the first SQL there is just an insert from select, most SQLs I saw are like this bulk inserts or updates, since its a BI environment they are less selective. It's weird that the indexes rebuild took around 3h20 to finish compared to 1h30 on SQL Server 2012, for this reason I suspect it is related to some parameter at database level, since there were no code or hardware changes. I will get approval to drop the disabled indexes anyway to see if it makes any difference.
    – Andre
    Jul 28, 2023 at 12:20
  • The problem was minimized after dropping these disabled indexes and re-creating a few others removing included columns, I suspect they were created based on the missing index report from SSMS. Some steps in the execution plans of queries were spent doing "Index update".
    – Andre
    Aug 28, 2023 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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