1

We have a DB of size 1257GB having 100+ objects with hourly transactions (Insert and update).

We have set Auto Update statistics:True and Auto Update statistics asynchronously : False.

When we trigger the queries to fetch the data it's taking long time. But when we manually execute SP_UpdateStats, the same query is taking very less time to fetch the same amount of data.

Please let me know whether we need to update the stats on regular basis? And what are the advantages and disadvantages of using EXEC SP_UpdateStats?

Windows server 2012R2 and SSMS2014.

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
Puneeth Haroor
  • 21
  • 1
  • 1
  • 4

2 Answers2

2

But when we manually exec SP_UpdateStats, the same query is taking very less time to fetch the same amount of data

Even though you have auto update statistics set to true, your statistics won't be updated frequently

SQLServer triggers automatic statistics update,based on certain thresholds and below thresholds holds good for all version less than SQLServer 2016

  • The table size has gone from 0 to >0 rows (test 1).

  • The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then (test 2).

  • The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered (test 3).

so based on how big your table is ,you can determine the threshold using above formula

Starting with SQLServer 2016,this threshold has been changed and statistics will be triggered more frequently.

The same behaviour can be obtained in older versions with the help of traceflag 371

For example, if the trace flag is activated(by default activated in SQLServer 2016), update statistics will be triggered on a table with 1 billion rows when 1 million changes occur.

If the trace flag is not activated, then the same table with 1 billion records would need 200 million changes before an update statistics is triggered.

Could you please let me know whether we have to update the stats on regular basis? And what are the advantages and disadvantages of using EXEC SP_UpdateStats?

If you see suboptimal plans due to inaccurate statistics,go ahead and schedule this new flag

Talking about disadvantages, if you update statistics frequently, you will see query plans getting recompiled,which will in turn cause CPU pressure,when plans are compiled again

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
2

Sp_UpdateStats will update the statistics on all the tables. Brent Ozar believes that this should be done much more regularly then doing reorgs or rebuilds on indexes. By updating your statistics SQL Server is more likely to create a 'better' query plan. The downside of this is that all the statistics on all tables (whether they need to or not) will be updated and it can take substantial resources to do so. Many DBAs run sp_updatestats on a nightly or weekly basis when the machine is not being used heavily. There are scripts that will check for what tables to be updated and only those are updated.

To see different approaches to updating statistics this is a good place to start: https://www.brentozar.com/archive/2014/01/update-statistics-the-secret-io-explosion/

If the query is running slowly it is much more likely that there are other issues with the query. You should post the query and query plan and the community may be able to offer useful hints on improving the query or adding indexes to the underlying tables.

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22