Database maintenance and when is it needed (Part 4: Update Statistics)

This week’s special is the database maintenance step called “Update Statistics”.

What does this step do?

It updates the statistics of each table and index at the specified database.

What exactly are these statistics?

Statistics are numbers that help the SQL Server query optimizer make the correct decisions when building a query plan. They are SQL Server’s “spies” that feed him crucial information about the data he tries to access.

e.g. How many times is “John”mentioned in the “FirstName” column of the “Employees” table? If 90% of the employees are called “John”, why should SQL Server bother using his indexes to return all the employees called “John” and not do a table scan instead?

How often should I update statistics?

The answer depends on how fast are the statistics getting outdated in your environment? At a table where the majority of statements are SELECT operations, the statistics are staying updated for a long time. At a table where the majority of operations are INSERT statements, statistics will get outdated fast.

If I reorganize my indexes, do I also need to update the statistics?

Yes, as the reorganization of the indexes does not update the statistics.

If I rebuild my indexes, do I also need to update the statistics?

No, as the rebuilding of the indexes automatically updates the statistics.

Should I use the FULLSCAN option or SAMPLE option?

Unless your maintenance window is very short, I would always choose the FULLSCAN option as it “draws” a more “accurate picture” of the data. The SAMPLE option is used only if you want to have the maintenance finish faster at the cost of the statistics quality .

Recommended reading:

Books Online: https://technet.microsoft.com/en-us/library/ms187348.aspx

Paul Randal’s blog: https://www.sqlskills.com/blogs/paul/category/statistics/

 

Next week we will finish the database maintenance discussion with the Backup Database steps!