How to Update Statistics in SQL Server

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; 
UPDATE STATISTICS Adventureworks.<table_name> <index_name>; 
Update statistics for a table

With the help of the following T-SQL Command, you can update statistics for a table:

USE AdventureWorks; 
UPDATE STATISTICS Adventureworks.<table_name>; 
Update all statistics

To update all statistics for internal and user-defined tables in the database, use sp_updatestats Transact-SQL Command:

EXEC sp_updatestats;

Update Statistics using SqlBak Maintenance Job

All you need to do is simply set a 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 a 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.

1 thought on “How to Update Statistics in SQL Server”

Leave a Comment