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.
No comments:
Post a Comment