How to Update Statistics In MSSQL Server

The steps for performing SQL Server Update Statistics and an overview of SQL Server Statistics are provided in this article.

The query optimizer needs SQL Server statistics in order to provide an efficient and optimised execution plan. These statistics aid SQL Server in estimating the number of rows (often referred to as cardinality) by giving the query optimizer a distribution of column values. Regular updates are necessary for the query optimizer. A query optimizer may be misled by inaccurate statistics to select expensive operators over index seek, such as index scan, which could result in high CPU, memory, and input/output (IO) costs in SQL Server. Blocking and deadlocks may also occur, which could eventually affect the underlying queries and resources.

To view SQL Server Statistics, use T-SQL.

To see the attributes of statistics for a particular item in the current database, use DMV sys.dm_db_stats_properties.

Run the following query to view the HumanResources statistics.Table of employees.

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'); 

 

Let's update the SQL Server statistics using the UPDATE STATISTICS command in different ways

Example 1:

          SQL Server UPDATE STATISTICS for all statistics in an object

                Execute the following query to update SQL Server Statistics on HumanResources.Employee table.

            Update STATISTICS HumanResources.Employee

Example 2:

        SQL Server UPDATE STATISTICS for specific statistics

Let’s say we want to update SQL Server statistics for statistics IX_Employee_OrganizationNode. Execute the following code.

            Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode

 Example 3:

         SQL Server UPDATE STATISTICS with FULL Scan

We use FULL SCAN in the UPDATE STATISTICS to scan all rows of a table. In the previous examples, we did not specify the FULL SCAN parameter. Therefore, SQL Server automatically decides whether it requires FULL SCAN or not.

 The following query does a full scan and updates the statistics for specific statistics in the specified object.

 Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH FULLSCAN

     We can also use the WITH SAMPLE 100 PERCENT clause instead of WITH FULLSCAN and both returns the same result.

  Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH SAMPLE 100 PERCENT

Example 4:

           UPDATE STATISTICS with SAMPLE

We can use WITH SAMPLE CLAUSE to specify the percentage or number of rows for the query optimizer to update statistics.

The following query specifies a 10 percent sample to update the statistics.

Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH SAMPLE 10 PERCENT

 The following query specifies 1000 rows sample to update the statistics.

Update STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH SAMPLE 1000 ROWS

 We usually perform database maintenance such as index rebuild or index reorganize. SQL Server automatically updates the statistics after the index rebuild. It is equivalent to update statistics with FULL SCAN however; it does not update the column statistics. We should update column statistics after index rebuild as well. We can use the following queries to do the task for all statistics on a specified object.

Update STATISTICS HumanResources.Employee  WITH FULLSCAN, COLUMNS

 SQL Server does not update statistics with the index reorganize activity. We should manually update the statistics, if required or need to rely on the automatically updated statistics

SQL Server does not update statistics with the index reorganize activity. We should manually update the statistics, if required or need to rely on the automatically updated statistics.

        Updating All Statistics with sp_updatestats

We can update use sp_updatestats to update all statistics in the database. It does through each object statistics and performs the required update. For the large databases, it might take unnecessary longer time and system resources as well because it performs a check on each statistic on the object.

 exec sp_updatestats

In this article, we explored the concept of SQL Server Statistics and various options to update these statistics with both automated and manual methods. We should regularly monitor the statistics and update them as per the requirement.