Statistics - Pro SQL Server Internals - page 67

It is recommended that you update statistics manually in the case of large tables. You must analyze the size of the
table, data modification patterns, and system availability when picking an optimal statistics maintenance strategy.
For example, you can decide to update statistics on critical tables on a nightly basis if the system does not have a
heavy load outside of business hours.

■ Statistics and/or index maintenance adds additional load to SQL Server. You must analyze how it affects other
databases on the same server and/or disk arrays.

Note

Another important factor to consider while designing a statistics maintenance strategy is how data is modified.
You need to update statistics more often in the case of indexes with ever-increasing or decreasing key values, such as
when the leftmost columns in the index are defined as identity or populated with sequence objects. As you have seen,
SQL Server hugely underestimates the number of rows if specific key values are outside of the histogram. This behavior
may be different in SQL Server 2014, as we will see later in this chapter.
You can update statistics by using the UPDATE STATISTICS command. When SQL Server updates statistics, it
reads a sample of the data rather than scanning the entire index. You can change that behavior by using the FULLSCAN
option, which forces SQL Server to read and analyze all of the data from the index. As you may guess, that option
provides the most accurate results, although it can introduce heavy I/O activity in the case of large tables.

■ SQL Server updates statistics when you rebuild the index. We will talk about index maintenance in greater
detail in Chapter 5, “index Fragmentation.”

Note

You can update all of the statistics in the database by using the sp_updatestats system stored procedure.

■ You should update all of the statistics in the database after you upgrade it to a new version of SQL Server.
there are changes in Query Optimizer behavior in every version, and you can end up with suboptimal execution plans
if the statistics are not updated.

Tip

There is a sys.dm_db_stats_properties DMV, which shows you the number of modifications in statistics
columns after the last statistics update. The code, which utilizes that DMV, is shown in Listing 3-11.

Listing 3-11. Using sys.dm_db_stats_properties
select
s.stats_id as [Stat ID]
,sc.name + '.' + t.name as [Table]
,s.name as [Statistics]
,p.last_updated
,p.rows
,p.rows_sampled
,p.modification_counter as [Mod Count]
from
sys.stats s join sys.tables t on
s.object_id = t.object_id