In the storage whitepaper that I recently released, I talked a bit about the SPDatabaseStatisticsJobDefinition timer job and the fact that it can, in most cases keep SharePoint running smoothly. However, I recently had to research a bit further into what exactly this timer job does. This is what I discovered:
This timer job is associated with each web application in the farm. It is automatically created and configured to run weekly when the web application is created. I mentioned that it might be a good idea to run some sort of statistics refresh after a large migration (into SharePoint) or similar operation. Well, the interesting thing is that I traced this timer job definition all the way down to a stored procedure that exists in every content database. The stored procedure is called proc_UpdateStatistics.
So if it were my farm, I might be inclined to try to modify the schedule for this timer job but this may not be possible (haven't had time to check). The other possibility is to just execute the stored procedure manually but that might not be supported by Microsoft. Interestingly, according to this KB article, Microsoft specifically says that it's OK to update statistics, so maybe it is supported.
Anyway, I just thought all of that was interesting. On a related note, I want to clarify, that this timer job runs the statistics update on CONTENT DATABASES. So it is highly likely that you will need to create an additional maintenance plan specifically for the SSP search database. If the search database is large and/or supports millions of documents, it's a good idea to keep it cleaned up regularly.
Finally, a bit of a warning. No matter how you manage your indexes/statistics refresh, you don't want to be updating statistics any time a crawl is running. Particularly if the crawl has a lot to do (full crawl), if you're trying to update statistics at the same time, you could end up grinding the SQL Server into oblivion.
Russ Houberg
Hi
Do you know if the timer job database statistics writes a log anywhere to say how long it took to run ?
I’m not aware of anything off the top of my head. But if you needed that information, you should be able to monitor that through the ULS logs. Specifically, you should be able to get start and end times for the timer jobs and then calculate it manually. If you’re not getting enough detail, try turning up the Diagnostic Log (ULS log) “verboseness” for the Timer Job category.
Also, if you have access to your SQL Server, you can just run proc_UpdateStatistics manually from SQL Mgmt Studio. You’ll find it in any Content Database. Just run it in the context of whatever database you want. I would recommend that if you’re concerned about time-to-run, that your run it manually after hours. There are no parameters to worry about, so you could just kick it off and see how long it takes.
There is probably some debate as to whether or not it’s “allowed” for you to run this manually. But in TechNet, Microsoft says that we’re allowed to run maintenance jobs as long as they don’t change the schema of the database. So theoretically, you could write this very same stored proc.
Hope that helps.