X hits on this document

129 views

0 shares

0 downloads

0 comments

28 / 48

662

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

  • 0

    index(es)/statistic(s) have been updated

necessary...

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

,n]]

The arguments of this command are described in Table 28-4.

Document info
Document views129
Page views129
Page last viewedSat Dec 10 23:20:00 UTC 2016
Pages48
Paragraphs987
Words15822

Comments