UPDATE STATISTICS GETTING SLOWER

20 March,2018 by Jack Vamvas

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.

Update_statistics

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on UPDATE STATISTICS GETTING SLOWER


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer