Search This Blog

How to find outdated Statistics in SQL server



SQL server uses the stats to find the appropriate Query plan, so if you are experiencing performance issues for your queries, this should be the place to start.
An outdated statistic can make the sql server choose a wrong plan, use the following query to find outdated stats; this makes use of the STATS_DATE() function

SELECT OBJECT_NAME(id) AS Tabel ,st.[name] AS Tnaam, si.name,STATS_DATE(id, indid) AS DateLastUpdate,rowmodctr rowsModifiedSinceLastUpdate
FROM sys.sysindexes AS si
INNER JOIN sys.[tables] AS st ON si.[id] = st.[object_id]
INNER JOIN sys.schemas AS ss ON st.[schema_id] = [ss].[schema_id]
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE())
  AND rowmodctr>10
ORDER BY [rowmodctr] DESC

Once you have the outdated stats identified, use “UPDATE STATISTICS( )”  to do the update. You can even write a cursor to do that.

No comments: