Wednesday, May 16, 2018

Query to determine last statistics update for a database

Statistics are a vital part of SQL Server database engine, these are used for the query optimizer to create query plan.
These statistics store distribution information about one or more columns on tables and views, you can read more about how statistics works from Microsoft documentation in the first link.

On this post I am sharing a lightweight query to determine last update statistics date for each user table on a database. Please note that objects with null values are objects without statistics.
the ones with _WA_Sys names, are the ones generated automatically when you have enabled the AUTO_CREATE_STATISTICS option.

SELECT obj.name [table name],
 sp.stats_id,
 stat.name [stat Name],
 stat.filter_definition,
 sp.last_updated,
 sp.rows [object rows],
 sp.rows_sampled,
 sp.unfiltered_rows
FROM sys.objects obj
LEFT JOIN sys.stats AS stat --Include objects without statistics
 ON obj.object_id = stat.object_id
OUTER APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.type = 'U' -- Only user tables
ORDER BY [table name],
 last_updated

Remember that you can update them using the UPDATE STATISTICS option.

2 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. Hi Charley, Braindumps are not allowed for Microsoft tests, i am deleting this comment.

      Delete