CODEWITHSUNDEEP
sql-server
slavisam87
slavisam87

Reputation: 87

Query to know at what sample percentage stats last updated

To find the status last update date it can be done simply with this query:

SELECT 
    OBJECT_NAME(object_id) AS [ObjectName],
    [name] AS [StatisticName],
    STATS_DATE([object_id], 
    [stats_id]) AS [StatisticUpdateDate]
FROM 
    sys.stats;

On top of knowing when (date and time), what table can I also query to find at what sampling percentage the stats were last updated with? Knowing if stats executed is valuable, but know when they executed with what stat collection is even more valuable.

Upvotes: 1

Views: 3753

Answers (2)

slavisam87
slavisam87

Reputation: 87

Here is what my final SQL that gives you very valuable information in regards to stats when doing quick validations:

[select stats.object_id,
        stats.name stat_name,
        stats.stats_id,
        objects.name table_name,
        stats_date(stats.object_id, stats.stats_id) last_stat_update,
        sp.rows_sampled rows_sampled_when_last_stat_update,
        sp.rows total_rows_when_last_stat_update,
        (to_number(sp.rows_sampled) / to_number(sp.rows)) * 100 percent_rows_sampled,
        sp.modification_counter changes_since_last_stat_update
   from sys.objects
   join sys.stats
     on stats.object_id = objects.object_id 
  cross apply sys.dm_db_stats_properties(stats.object_id, stats.stats_id) sp
  where objects.type_desc = 'USER_TABLE']

Upvotes: 4

SS_DBA
SS_DBA

Reputation: 2423

See if this is what you are looking for.

SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications"
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]

Upvotes: 1

Related Questions