Execute SP_UPDATESTATS

The query optimizer uses the statistics to create the query plan to improve the query performance. The query optimizer automatically generates the necessary statistics to build high quality query plan to improve the performance of the query.

SQL Server uses the  auto update statistics option, for automated statistics redistribution. There are other options – such as  SQL Server sp_updatstats and UPDATE STATISTICS  for more granular control

I use these options for maintenance and troubleshooting. Reasons could be:

1) Users complaining about slow queries

2) Bulk load data

3)Planned maintenance

In fewer cases we need to create statistics for better result.

The statistics of the query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed views.
The query optimizer use this statistics to estimates the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.  The query optimizer could use cardinality estimates to choose the index seek operator instead index scan operator, and in doing so improve query performance.

1) Updates statistics on all user defined and internal tables

2) Updates statistics on disabled non-clustered indexes

3) Doesn’t update statistics on disabled clustered indexes

4) Uses rowmodctr in sys.sysindexes to decide on whether to update statistics

Every index or table has a row in sys.sysindexes. The rowmodctr column maintains a running total of : INSERT,DELETE,UPDATE statement since the last statistics update.

5) Preserves NORECOMPUTE if compatibility 90 or above

 If NORECOMPUTE is specified on an UPDATE STATISTICS statement , SQL Server will not perform automated statistics update . The AUTO_STATISTICS_UPDATE is disabled for the object.

What is UPDATE STATISTICS ?

SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

-If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.
-If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

Statistics can be created or updated on tables with computed columns only if the conditions are such that an index can be created on these columns.

How we can do the UPDATE STATISTICS in SQL Server?

If you want to find out if an index has had its indexes updated or not, you can use the command, like this:
DBCC SHOW_STATISTICS (table_name , index_name)

This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining.

You can update the statistics using this command:

USE <database_name>
EXEC sp_updatestats

Example

EXEC sp_updatestats

DBCC SHOW_STATISTICS ('tbl_stat', stats_NIndxData) WITH STAT_HEADER

Your index rebuild will automatically update the statistics for the index (with 100% sample size which is usually better then what you end up with when using sp_updatestats). After the index rebuild is complete you can use sp_updatestats to update the other statistics that require attention