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