how to trigger a statistics update

Question

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?).

Answer 1

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.

Answer 2

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".

Answer 3

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.