Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a certain table in my OLTP database, which is bulk-updated by several users.
There is no way to know when they will update the table (somewhere between 5 times a day and once in a week).

The problem is, that the update does not cause the statistics to be automatically updated,
but it is a big enough update to cause SQL Server to use a poor query plan.
(the table has ~500k rows and the operation inserts/updates between 5k and 20k rows)

My question is how should i trigger a statistics update?
My thoughts:

  • A job which will run every 30 minutes and check for changes using rowmodctr. The job will then update statistics if necessary
  • A DML trigger which will check for changes and start a job when necessary. The job will update statistics

Of course the solution has to be server side, application changes are not welcome :)

One more thing is that I might need the same or similiar for more than just this one table so the solution has to be generic (use a modified-sp_updatestats with parameters for... everything?).

share|improve this question
add comment

3 Answers

If you are running SQL Server 2008 R2 SP1 or newer you can turn on the trace flag which changes when auto update stats runs. The traceflag is 2371 and it changes the stats update trigger from 20% + 500 rows to a sliding scale.

I talk about the finer points of the traceflag here.

share|improve this answer
    
+1 I did not know that, and maybe after our scheduled upgrade from SQL Server 2005 to SQL Server 2012 it will solve my problem, though it is a server-wide configuration and my need is for a specific database. –  Justicator Oct 15 '13 at 10:06
    
that's good post. but as you also mentioned in real world enabling this flag server wide requires very much testing. and slight change in client/system workflow can mess that up. what I have came across is very specific workflow of application requires updating statistics on selected tables. not even whole DB. and that's where only manual update is helpful. if something not working fine we know what we updated and can be easily troubleshoot or roll backed. –  Anup Shah Oct 15 '13 at 16:58
add comment

Unless the potential delay between a stats update possibly being needed and the possible need for that update being spotted could be a significant problem, I would go with the timed job option rather than a DML trigger. If your scheduled job fails it won't introduce surprise exceptions to the rest of the application like a failure in a trigger could, and the trigger option would make an already sub-optimal situation noticeably worse if some code in the current code base or added later pushes in many changes individually where you are expecting them to be a single bulk operation (so you get thousands of trigger calls for one row at a time instead of one for thousands or rows).

If you need to pick up on the need to update the stats pretty quickly, then the trigger option is the best method as you can act immediately - but be careful how you optimise the process particularly considering what your dev/users could do "wrong".

share|improve this answer
add comment

Check the DB setting for “Auto Update Statistics Asynchronously” . Note that Update Statistics gets triggered during the first select after such insert/update but not immediate after the insert/update finishes. So in our case if the statistics are set to update “Asynchronously” then SQL server will continue with existing statistics for first select and will start updating statistics. Subsequent select will get benefits from that if update statistics is done.

In both options you gave the update statistics event is triggered on schedule and that may not work as intended. Also as you mentioned the update or inserts are 5K to 20K which is not enough to trigger update statistics automatically so that means you have to trigger using FULLSCAN option. Which could be expensive operation on such large table.

Let’s say you schedule job or trigger schedule at 30 minutes.

Insert/update will suffer from this if JOB is running at time of insert/update.

Lets say, Job is just finished and insert/update came in. then select before 30 minutes will suffer because it doesn't have updated statistics.

Guaranteed way is doing it immediately after insert/update on that specific tables.

If the number of table are few you can store those table names in DB table and write procedure that that will update statistics on those table. You can call this procedure at the end of set of insert/update only once and will updated statistics on all interested tables listed. if you needed new table to update statistics add it in to the list.

enter image description here

share|improve this answer
    
We don't use the Async option so it is not the problem. We also don't have to use FULLSCAN, but even if we do it is not to expensive to scan 500k rows (takes about 10-15 seconds of shared lock on our system, which is acceptable if done only when needed). My question was about that "guaranteed way" which I am still looking for. of course using a stored procedure is an option, but with the constraint of not changing the application. –  Justicator Oct 16 '13 at 9:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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