If you care about SQL Server performance you need to take SQL Server Statistics into account. Statistics are lightweight and small objects which describe how data in SQL Server tables are distributed. The query optimizer uses them to create query plans that improve query performance.
If you have AUTO_UPDATE_STATISTICS option turned on for the database the query optimizer will automatically determine when statistics might be out-of-date and then update them when they are used by a query. But you need to update your statistics manually when the automatic update does not occur frequently enough to provide you with a proper set of statistics or because the sampled nature of the automatic updates is causing your statistics to be inaccurate.
Note, though, that updating statistics causes queries to recompile and experienced users recommend not to update statistic too often. All you need to do is to find the middle ground between the time it takes to recompile queries and improving query plans.
How to update statistics
Below we are going to show how to update statistics for an index, table and how to update all statistics. You can do it using T-SQL Commands or simply set SqlBak Maintenance Job (see below).
T-SQL Commands for Updating Statistics
Update statistics for an index
Use the following T-SQL Command to update the statistics for an index
USE AdventureWorks; GO UPDATE STATISTICS Adventureworks.<table_name> <index_name>; GO
Update statistics for a table
With the help of the following T-SQL Command you can update statistics for a table
USE AdventureWorks; GO UPDATE STATISTICS Adventureworks.<table_name>; GO
Update all statistics
To update all statistics for internal and user-defined tables in the database, use sp_updatestats Transact-SQL Command
Update Statistics using SqlBak Maintenance Job
All you need is simply set SqlBak maintenance job. You can do it in the following way:
Add Maintenance Job
Connect to SQL Server
Select the computer that you need to work with and check the SQL Server connection.
Add Maintenance Scripts
You can do it manually
or just click on “Add from template” and select the one you need
Set Maintenance Schedule
Also, you can set the schedule for your SqlBak maintenance job. To do it click “Schedule maintenance” and specify the date of the first start and the interval with which the operation will be repeated.
Add E-mail Notifications
Besides all this, you can turn “on” the e-mail notification and receive letters if the maintenance job is “success” or “fail”.
As you can see, all process will take about few minutes and your SqlBak maintenance job will work according to the selected schedule.