SQL Server statistics help the query optimizer generate efficient execution plans, improving query performance by making better decisions about index usage and join strategies.
That is why, DBAs should keep table statistics up to date to ensure optimal query performance.
In Prod, DBAs should update statistics during off-peak hours to minimize impact on database performance.
The code fetches various statistical properties of the statistics associated with the ‘HumanResources.Employee’ table from the system catalog views sys.stats and sys.dm_db_stats_properties.
SELECT sp.stats_id,
name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee');- Stats_ID: It is the unique ID of the statistics object
- Name: It is the statistics name
- Last_updated: It is the date and time of the last statistics update
- Rows: It shows the total number of rows at the time of the last statistics update
- Rows_sampled: It gives the total number of sample rows for the statistics
- Unfiltered_rows: In the screenshot, you can see both rows_sampled and unfiltered_rows value the same because we did not use any filter in…
