Update Statistics of table indexes or columns in MS SQL Server

In SQL Server, Statistics are created on table indexes or table columns for the optimizer to tune the query. For a table, the statistics object is created on either an index or a list of table columns. 

Check the statistics for the table:

SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, 
       steps, unfiltered_rows, modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee');

OR 


SELECT name AS index_name, STATS_DATE(OBJECT_ID, index_id) AS statistics_update_date 
FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('HumanResources.Employee'); 

Check the statistics by using the DBCC package:

DBCC SHOW_STATISTICS ('HumanResources.Employee', IX_Employee_OrganizationNode);  
GO  

DBCC SHOW_STATISTICS ('HumanResources.Employee', IX_Employee_OrganizationNode) WITH HISTOGRAM;  
GO  

Create and Update the Statistics:

Example of creating statistics on table columns with different options:

-- Create Statistics for table on columns with 100 percent sample:

CREATE STATISTICS Newstatistics ON HumanResources.Employee (SickLeaveHours)  WITH SAMPLE 100 PERCENT;  

--Create statistics for table on columns with 50 percent sample:

CREATE STATISTICS Newstatistics ON HumanResources.Employee (SickLeaveHours)  WITH SAMPLE 100 PERCENT;  

--Create statistics for table with fullscan:
CREATE STATISTICS Newstatistics ON HumanResources.Employee (SickLeaveHours) WITH FULLSCAN;

--Create statistics for table with 1000 rows:
CREATE STATISTICS Newstatistics ON HumanResources.Employee (SickLeaveHours) WITH SAMPLE 1000 ROWS;

Update Statistics for the table:

Example of using update Statistics for the table:

-- Update statistics for all statistics
Update STATISTICS HumanResources.Employee;

-- Update statistics for specific index or statistics
Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode

--update statistics with fullscan
Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH FULLSCAN

--update statistics with 100%
Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH SAMPLE 100 PERCENT

--Update statistics with 10%
Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH SAMPLE 10 PERCENT

--Update statistics with 1000 rows base 
Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH SAMPLE 1000 ROWS

Note: If you do not want to update statistics for any table automatically then use NORECOMPUTE.

-- NORECOMPUTE: is used to disable the future automatic statistics updation.

UPDATE STATISTICS HumanResources.Employee (IX_Employee_OrganizationNode) WITH NORECOMPUTE;

Drop the Statistics

Syntax:


DROP STATISTICS table.statistics_name

Example:
DROP STATISTICS HumanResources.Employee.Newstatistics

Note: If you tried to drop the index statistics name then it will give an error:

 DROP STATISTICS HumanResources.Employee.AK_Employee_LoginID

Msg 3739, Level 11, State 1, Line 17
Cannot DROP the index 'HumanResources.Employee.AK_Employee_LoginID' because it is not a statistics collection.

Check all information of Statistics:

SELECT
 ss.[name] AS SchemaName,
 obj.[name] AS TableName,
 stat.[name] AS StatisticsName,
  CASE
    WHEN stat.[auto_created] = 0
    AND stat.[user_created] = 0 THEN 'Index Statistic'
    WHEN stat.[auto_created] = 0
    AND stat.[user_created] = 1 THEN 'User Created'
    WHEN stat.[auto_created] = 1
    AND stat.[user_created] = 0  THEN 'Auto Created'
    WHEN stat.[auto_created] = 1
    AND stat.[user_created] = 1 THEN 'Updated stats available in Secondary'
  END AS StatisticType,
  CASE
    WHEN stat.[is_temporary] = 0 THEN 'Stats in DB'
    WHEN stat.[is_temporary] = 1 THEN 'Stats in Tempdb'
  END AS IsTemporary,
  CASE
    WHEN stat.[has_filter] = 1 THEN 'Filtered Index'
    WHEN stat.[has_filter] = 0 THEN 'No Filter'
  END AS IsFiltered,
  c.[name] AS ColumnName, stat.[filter_definition], sp.[last_updated], sp.[rows],
  sp.[rows_sampled], sp.[steps] AS HistorgramSteps, sp.[unfiltered_rows],
  sp.[modification_counter] AS RowsModified
FROM  
  sys.[objects] AS obj
  INNER JOIN sys.[schemas] ss
    ON obj.[schema_id] = ss.[schema_id]
  INNER JOIN sys.[stats] stat
    ON stat.[object_id] = obj.[object_id]
  JOIN sys.[stats_columns] sc
    ON sc.[object_id] = stat.[object_id]
    AND sc.[stats_id] = stat.[stats_id]
  JOIN sys.columns c
    ON c.[object_id] = sc.[object_id]
    AND c.[column_id] = sc.[column_id]
  CROSS apply sys.Dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE ( obj.[is_ms_shipped] = 0 AND obj.[object_id] = @object_id )
  OR ( obj.[is_ms_shipped] = 0 )
ORDER BY ss.[name], obj.[name], stat.[name]; 

Refer: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-ver16