UPDATE STATISTICS GETTING SLOWER

Question: The Statistics tasks in the SQL Server Maintenance plan is getting slower? When I first implemented the task it used to take 1 hr – recently it’s built up 4-5 hrs. I’ve checked underlying resource issues – and everything looks OK.

Could you advise on how I can progress with troubleshooting. I’ve included a screeshot of Update Statistics Task.

Answer: It is not unusual to find UPDATE STATISTICS getting slower over time. Looking at the way you have this configured

1. All existing statistics – this includes all index and table statistics
2. Sample size of 25 %

It is very possible you have AUTO_CREATE_STATISTICS set at ON for the databases. The AUTO_CREATE_OPTION  means the Query Optimizer will create statistics for columns used in query predicates , but only if statistics are not already available. You’ll notice naming patterns _WA_Sys_<column_name>_<XXXX> in the statistics section. When the Optimizer creates these statistics , it will use them to design the Execution Plsan.

As more SQL statistics are created and based on the configuration above – i.e All statistics – then the workload may increase over time. What happens is the SQL Server Maintenance Task for update statistics - generates more UPDATE STATISTICS statements to support the new sets of statistics created .

If SQL Server statistics  are not managed properly and as  new queries are introduced – over time,  queries will create more of these SQL statistics . Ultimately these increased sets of statistics require a greater management overhead – leading to longer run times.

There are a number of options you have available:

1. Decrease sample size – This will require some analysis , and may not be the best first course of action
2. Change to sp_updatestats . The sp_updatestats option focuses on statistics requiring updating Versus the default UPDATE STATISTICS in the maintenance plan . One of the avdvantages of using sp_updatestats is it uses rowmodctr in sys.sysindexes to decide on whether to update statistics.Every index or table has a row in sys.sysindexes. The rowmodctr column maintains a running total of : INSERT,DELETE,UPDATE statement since the last statistics update.
3. To implement sp_updatestats within maintenace paln can be a nuisnace. One way to implement is to implement a t-sql related task. Alternatively you could create a custom SQL Agent job to run sp_updatestats – although the problem is it may conflict with other tasks in the in te Maintenance plan , so you need to coordinate

To read more about the differences between UPDATE STATISTICS and sp_updatestats check SQL Server sp_updatestats and UPDATE STATISTICS