CHAPTER 28 ■ PERFORMANCE TUNING
If you wish to update all statistics in the current database, you can use the system-stored pro- cedure sp_updatestats. Improved in SQL Server 2005, this stored procedure only updates statistics when necessary (when data changes have occurred). Statistics on unchanged data will not be updated.
This example demonstrates creating new statistics on columns in the database that don’t already have statistics created for them:
EXEC sp_createstats GO
This returns the following (abridged) result set:
Table 'AdventureWorks.Production.ProductProductPhoto': Creating statistics for the following columns: Primary ModifiedDate 'AdventureWorks.Sales.StoreContact': Creating statistics for the following columns: ModifiedDate 'AdventureWorks.Person.Address': Creating statistics for the following columns: AddressLine2 City PostalCode ModifiedDate Table Table
This next example automatically updates all statistics in the current database:
EXEC sp_updatestats GO
This returns the following (abridged) results. Notice the informational message of 'update is not necessary'. The results you see may differ based on the state of your table statistics:
Updating [Production].[ProductProductPhoto] [PK_ProductProductPhoto_ProductID_ProductPhotoID], update is not necessary... [AK_ProductProductPhoto_ProductID_ProductPhotoID], update is not necessary...
[_WA_Sys_00000002_01142BA1], update is not [Primary], update is not necessary... [ModifiedDate], update is not necessary...
index(es)/statistic(s) have been updated
, 5 did not require update.
Viewing Statistics Information
If you suspect that the statistics on a specific table are not being updated appropriately, or contain out-of-date information, you can use the DBCC SHOW STATISTICS command to view detailed statis- tics information in order to verify.
The syntax is as follows:
D B C C S H O W _ S T A T I S T I C S ( ' t a b l e _ n a m e ' | ' v i e w _ n a m e '
, target )
< [ WITH [ NO_INFOMSGS ] S T A T _ H E A D E R | D E N S I T Y _ V E C T O R | H I S T O G R A M > [
The arguments of this command are described in Table 28-4.