Index statistics are the basis for the SQL Server database engine to generate the most efficient query plan. By default statistics are updated automaticly. The query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view. In the situation you encounter a performance problem and you do not understand the generated execution plan you can doubt if the statistics are up to date. Now you can do 2 things. First directly execute an update statistics on the table, but much better check how recent your statistics are. In case they are very recent, it is not necessary to execute an update statistics.
First check if indexes are disabled for auto update statistcs (No_recompute = 0). Next query will retrieve all indexes for which auto update statistics are disabled:
SELECT o.name AS [Table], i.name AS [Index Name],
STATS_DATE(i.object_id, i.index_id) AS [Update Statistics date],
s.auto_created AS [Created by QueryProcessor], s.no_recompute AS [Disabled Auto Update Statistics],
s.user_created AS [Created by user]
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.object_id = i.object_id
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.object_id = s.object_id AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
AND no_recompute = 1
ORDER BY STATS_DATE(i.object_id, i.index_id) ASC;
Use next query to enable the Auto Update Statistics for IndexA of TableX.
ALTER INDEX
ON dbo.
SET (STATISTICS_NORECOMPUTE = OFF);
Use next query to see the update statistics date.
SELECT o.name AS [Table], i.name AS [Index Name],
STATS_DATE(i.object_id, i.index_id) AS [Update Statistics date],
s.auto_created AS [Created by QueryProcessor], s.no_recompute AS [Disabled Auto Update Statistics],
s.user_created AS [Created by user]
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.object_id = i.object_id
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.object_id = s.object_id AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.object_id, i.index_id) ASC;
In case your indexes are outdated use next query to update the statistics:
UPDATE STATISTICS TableX IndexA
Enjoy your performance tuning.
