SQL : AUTO STATS Troubleshooting

SQL : AUTO STATS Troubleshooting

Statistics for Query Optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.

To view the indexes and their last update time use the following command:

 SELECT
        o.name AS Table_Name
       ,i.name AS Index_Name
       ,STATS_DATE(o.id,i.indid) AS Date_Updated

FROM
        sysobjects o JOIN
        sysindexes i ON i.id = o.id

WHERE
        xtype = ‘U’ AND
        i.name IS NOT NULL

ORDER BY
        o.name ASC
       ,i.name ASC

To update statistic:

•     On a single index: UPDATE STATISTICS [Table_Name][INDEX_NAME]
•     On a single table: UPDATE STATISTICS[TABLE_NAME]
•     On the entire Database: EXEC sp_updatestats