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 statistics 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
Go to your “Dashboard” page and click “Add new job”
at the popup window, select your server and set a “Maintenance job” as your job type.
The server is chosen and connection to your DBMS is established, now it’s time to set a schedule.
Set Maintenance Schedule
To set a schedule for your SqlBak maintenance job click on the “Schedule maintenance” and specify the date of the first start and the interval with which the operation will be repeated.
More complicated schedule settings can be found by clicking the “Advanced settings…” link.
Add E-mail Notifications
You can turn on the e-mail notification and receive emails if the maintenance job is successful or fail.
Add Maintenance Scripts
Click on the “Add script” button at the “Maintenance scripts” and choose “SQL script”.
Set the script you need manually
or just click on “Add from template” and select the one you need.
As you can see, all the settings take about a few minutes and your SqlBak maintenance job will work according to the selected schedule.