The database which has several tables does have very heavy write operations. These table are sometimes very large. The performance of this database will get slower if maintenance is not done in a regular base. A Auto Update Statistics function is causing a performance degradation. Make sure that the database Statistics are updated every week.
Statistics are objects which contain information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses this statistical data to estimate the number of rows in the query result.
Create New SQL Job
Open Microsoft SQL Server Management Studio and navigate to SQL Server Agent -> Jobs using Object Explorer. Right click Jobs and choose New Job.
Enter the job name, owner, category (Database maintenance) and description on the following screen:
Click on Steps in the left side pane. Fill in the job name and database. The command type is T-SQL script. You should enter the following command: EXEC sp_updatestats
On the advanced tab you can choose success/failure actions. I recommend you log the job output to a file. You should enter a run as user, but please note that only the DBO and sysadmins can execute this procedure.
The last task is to create a schedule to run the job in off-peak periods. It depends on your database usage on how often you should update the statistics: daily, twice a day, etc. It really depends on your database size, the number of changing rows, etc… I recommend you experiment with this to find the optimal solution for your database. First schedule the job to run once a day in a convenient time and also measure how long it runs. Check the performance gain and consider whether more frequent updates are required.
