--- PDF PAGE 302 ---
285
You can allow SQL Server to manage statistics automatically. A database-level option 
called AUTO_CREATE_STATISTICS automatically generates single column statistics, where 
SQL Server believes better cardinality estimates will help query performance. There 
are limitations to this however. For example, filtered statistics or multicolumn statistics 
cannot be created automatically.
Tip  The only exception to this is when an index is created. When you create an 
index, statistics are always generated, even multicolumn statistics, to cover the 
index key. It also includes filtered statistics on filtered indexes. This is regardless of 
the AUTO_CREATE_STATS setting.
Auto Create Incremental Stats causes statistics on partitioned tables to be 
automatically created on a per-partition basis, as opposed to being generated for the 
whole table. This can reduce contention by stopping a scan of the full table from being 
required.
Statistics become out of date as DML operations are performed against a table. The 
database-level option, AUTO_UPDATE_STATISTICS, rebuilds statistics when they become 
outdated. The rules in Table 8-3 are used to determine if statistics are out of date.
Table 8-3.  Statistics Update Algorithms
No of Rows in Table
Rule
0
Table has greater than 0 rows.
<= 500
500 or more values in the first column of the statistics object have changed.
> 500
500 + 20% or more values in the first column of the statistics object 
have changed.
Partitioned table with 
INCREMENTAL statistics
20% or more values in the first column of the statistics object for a 
specific partition have changed.
The AUTO_UPDATE_STATISTICS process is very useful, and it is normally a good 
idea to use it. An issue can arise, however, because the process is synchronous and 
blocking. Therefore, if a query is run, SQL Server checks to see if the statistics need to be 
updated. If they do, SQL Server updates them, but this blocks the query and any other 
queries that require the same statistics, until the operation completes. During times of 
Chapter 8  Indexes and Statistics

--- PDF PAGE 305 ---
288
Listing 8-23.  Creating Statistics
USE Chapter8
GO
--Create multicolumn statistic on FirstName and LastName
CREATE STATISTICS Stat_FirstName_LastName ON dbo.CustomersDisk(FirstName, 
LastName) ;
GO
--Create filtered statistic on NetAmount
CREATE STATISTICS Stat_NetAmount_Filter_OrderDate ON dbo.
OrdersDisk(NetAmount)
WHERE OrderDate > '2019-01-01' ;
GO
When creating statistics, you can use the options detailed in Table 8-4.
Table 8-4.  Creating Statistics Options
Option
Description
FULLSCAN
Creates the statistic object on a sample of 100% of rows in the table. This 
option creates the most accurate statistics but takes the longest time to 
generate.
SAMPLE
Specifies the number of rows or percentage of rows you need to use to build 
the statistic object. The larger the sample, the more accurate the statistic, but 
the longer it takes to generate. Specifying 0 creates the statistic but does not 
populate it.
NORECOMPUTE
Excludes the statistic object from being automatically updated with  
AUTO_UPDATE_STATISTICS.
INCREMENTAL
Overrides the database-level setting for incremental statistics.
Individual statistics, or all statistics on an individual table, can be updated by  
using the UPDATE STATISTICS statement. The script in Listing 8-24 first updates the 
Stat_NetAmount_Filter_OrderDate statistics object that we created on the OrdersDisk 
table and then updates all statistics on the CustomersDisk table.
Chapter 8  Indexes and Statistics

--- PDF PAGE 306 ---
289
Listing 8-24.  Updating Statistics
--Update a single statistics object
UPDATE STATISTICS dbo.OrdersDisk Stat_NetAmount_Filter_OrderDate ;
GO
--Update all statistics on a table
UPDATE STATISTICS dbo.CustomersDisk ;
GO
When using UPDATE STATISTICS, in addition to the options specified in Table 8-4 for 
creating statistics, which are all valid when updating statistics, the options detailed in 
Table 8-5 are also available.
Table 8-5.  Updating Statistics Options
Option
Description
RESAMPLE
Uses the most recent sample rate to update the statistics.
ON PARTITIONS
Causes statistics to be generated for the partitions listed and then 
merges them together to create global statistics.
ALL | COLUMNS | INDEX
Specifies if statistics should be updated for just columns, just 
indexes, or both. The default is ALL.
You can also update statistics for an entire database by using the sp_updatestats 
system stored procedure. This procedure updates out-of-date statistics on disk-based 
tables and all statistics on memory-optimized tables regardless of whether they are out 
of date or not. Listing 8-25 demonstrates this system stored procedure’s usage to update 
statistics in the Chapter8 database. Passing in the RESAMPLE parameter causes the most 
recent sample rate to be used. Omitting this parameter causes the default sample rate to 
be used.
Listing 8-25.  Sp_updatestats
EXEC sp_updatestats 'RESAMPLE' ;
Chapter 8  Indexes and Statistics

--- PDF PAGE 306 ---
289
Listing 8-24.  Updating Statistics
--Update a single statistics object
UPDATE STATISTICS dbo.OrdersDisk Stat_NetAmount_Filter_OrderDate ;
GO
--Update all statistics on a table
UPDATE STATISTICS dbo.CustomersDisk ;
GO
When using UPDATE STATISTICS, in addition to the options specified in Table 8-4 for 
creating statistics, which are all valid when updating statistics, the options detailed in 
Table 8-5 are also available.
Table 8-5.  Updating Statistics Options
Option
Description
RESAMPLE
Uses the most recent sample rate to update the statistics.
ON PARTITIONS
Causes statistics to be generated for the partitions listed and then 
merges them together to create global statistics.
ALL | COLUMNS | INDEX
Specifies if statistics should be updated for just columns, just 
indexes, or both. The default is ALL.
You can also update statistics for an entire database by using the sp_updatestats 
system stored procedure. This procedure updates out-of-date statistics on disk-based 
tables and all statistics on memory-optimized tables regardless of whether they are out 
of date or not. Listing 8-25 demonstrates this system stored procedure’s usage to update 
statistics in the Chapter8 database. Passing in the RESAMPLE parameter causes the most 
recent sample rate to be used. Omitting this parameter causes the default sample rate to 
be used.
Listing 8-25.  Sp_updatestats
EXEC sp_updatestats 'RESAMPLE' ;
Chapter 8  Indexes and Statistics