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 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

EXEC sp_updatestats;

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” then select “Add maintenance job”

UPDATE STATISTICS sqlbak-dashboard-page

Connect to SQL Server

Select the computer that you need to work with and check the SQL Server connection.

UPDATE STATISTICS select-the-computer-and-check-the-connection

Add Maintenance Scripts

You can do it manually

UPDATE STATISTICS maintenance-scripts

or just click on “Add from template” and select the one you need

UPDATE STATISTICS add-from-template

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.

UPDATE STATISTICS schedule-maintenance

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”.

UPDATE STATISTICS e-mail-notifications

As you can see, all process will take about few minutes and your SqlBak maintenance job will work according to the selected schedule.

[Total: 1    Average: 5/5]

Leave a Reply

Your email address will not be published. Required fields are marked *