SQL Server statistics operations

--- Query the index operation information
Select * From SYS. dm_db_index_usage_stats

-- Query the statistical information of a specified table (Combined Query by sys. Stats and sysobjects)
Select
O. Name, -- table name
S. Name, -- Name of the statistics
Auto_created, -- whether the statistics are automatically created by the query Processor
User_created -- whether the statistics are displayed and created by the user
From
SYS. Stats
Inner join
Sysobjects o
On
S. object_id = O. ID
Where
O. Name = 'table name'
Go

-- View the column information in Statistics
Select
O. Name, -- table name
S. Name, -- Name of the statistics
SC. stats_column_id,
C. Name --- column name
From
SYS. stats_columns SC
Inner join
Sysobjects o
On
SC. object_id = O. ID
Inner join
SYS. Stats s
On
SC. stats_id = S. stats_id and SC. object_id = S. object_id
Inner join
SYS. Columns C
On
SC. column_id = C. column_id and SC. object_id = C. object_id
Where
O. Name = 'table name'

-- View detailed statistics
DBCC show_statistics

-- View the statistics automatically created by the index
Exec sp_autostats 'object name'

-- Disable the database option for automatically generating statistics
Alter datebase database name set auto_create_statistics off

-- Create statistics
Create Statistics statistical information name on table name (column name)
[
[[Fullscan
Sample number {percent | rows}]
[Norecompute]
]
Go
The preceding parameters are described as follows:
Fullscan: Specify to collect statistics for all rows in a table or view.
Sample number {percent | rows}: specifies the number of data rows or percentage of data to be read by random sampling. The sample option cannot be used together with the fullscan option.
Norecompute: the specified database engine does not automatically recalculate statistics.

-- Calculate random sampling statistics
Create Statistics statistical information name on table name (column name)
With sample 5 percent --- create statistical information, calculate the random sampling statistics by 5%
Go

-- Create statistics
Exec sp_createstats -- check the parameters for help. Here we will not list them one by one.

-- Modify statistics
Update statistics table name | view name
Index name | statistical information name, index name | statistical information name ,.....
[
[[Fullscan
Sample number {percent | rows}]
[Norecompute]
]
--- The parameters are similar to the create statistics statement. The following describes several common applications.
1. Update all statistics of a specified table
Update statistics table name

2. Update the statistics of a single index of a specified table
Update statistics table name index name

3. perform a full scan of the table to update statistics
Update statistics table name (column name) with fullscan