8

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?

  • We've decided to create a powershell script, with a 'where used' command and run it every hour for about a week or 2. The results will append to a file on the local file system. This way, we have more information how often the sp_updatestats should run in our environments. – Harmen Jun 29 '15 at 13:23
  • Please see additional suggestions in my answer. – Vipin Kothari Jun 30 '15 at 2:33
  • I've seen it Vipin; running the sp_updatestats is recommended by SDL to run it daily. But still, there's no information given when it should be run based on conditions A, B and/or C. – Harmen Jul 6 '15 at 11:05
  • If you want to know conditions, factors etc. for DB maintenance post your question on dba.stackexchange.com I am reasonably confident that you will get to know all the factors that are needed for DB maintenance. Happy Learning – Vipin Kothari Jul 7 '15 at 0:45
4

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!

1

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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