Run how often sp_updatestats (SQL)

Question

I wonder if there is some 'best practice' how many times a day the sp_updatestats (SQL db Content Manager) should run. Of course it 'depends' on certain things... but like what? Imports by the Content Porter I assume. But any other things?

At the moment we run sp_updatestats twice a day. Regarding the fact we do: - daily imports (let's say about 30 items) - new versions/update versions of components (200-300 a day) - any other behaviour in the database that sp_updatestats will improve?

I was thinking about changing it to run it every hour. Is that common?

Answer 1

AFAIK, there isn't any best practice from Tridion on when to run Updatestats and the reason is that it is related to general DB maintainence practices. I will be pleasantly surprised if Tridion includes a recommendation for this.

We run the UpdateStatseveryday in all of our environments. The Updatestats is very important since it keeps the DB performance optimum. Running it daily is the recommendation from our DBAs and they do it for all DBs not just Tridion DBs.

Along with the same, you should also have DB reindexing daily as that will keep Tridion running optimally. We had setup reindexing daily because we faced issues with Tridion search queries timing out and the root cause per the DBA was DB performance and indexing. Based on DBA recommendation we have both running daily.

I would highly encourage you to connect with the DBAs in your enterprise and seek their inputs on this because it is their expertise area. Also, this is a known practice, so reaching out to them makes more sense rather than re-inventing the wheel, because it may result in an over engineered solution for a known case.

As always, choice is always yours!

Answer 2

I'd hazard a guess that freuqntly wasn't common. There's no official statement anywhere in the documentation for Tridion (or for DBs in general) that I've come across.

You can run it every day - it may do nothing most of the time.

I tend to see it done as part of a wider straightening up that the DBA may have setup already.

Here's more information on what it actually does

Answer 3

As we perform more CRUD operations on database, the data will be scattered. (Also, known as "In database fragmentation"). So retrieval time will increase as time goes by. After some time this slow down can also result in timeout.

To avoid database slowness/timeout for retrieval, periodically database re-indexing should be done which will keep data indexed so that even with scattered data the retrieval will not slow down.

sp_updatestatistics only updates statistics for the rows which are updated and these updates will be used for generating query plan. So it is recommended to use sp_updatestatics with re-indexing.

Since, the database operation will slow down during indexing, it is recommended to run off hours; probably over the weekend. There is not much benefit gained by running indexing on daily basis.