Get last statistics updated date
Ever wondered how to get a comprehensive overview of your SQL database's objects, indexes, and their associated statistics? This SQL query provides a powerful way to do just that!
It's incredibly useful for database administrators and developers who need to monitor database health, identify potential performance bottlenecks, or simply gain deeper insights into how their data is organized and optimized.
Understanding Your Database: A SQL Query for Object and Index Statistics
The following SQL script delves into various system views to pull out crucial information about your database's tables and views, their indexes, and the current state of their statistics. Keeping an eye on your statistics' freshness is vital for the SQL Server query optimizer to generate efficient execution plans.
SELECT
SCHEMA_NAME(o.Schema_ID) + N'.' + o.[NAME] AS [Object Name],
o.[type_desc] AS [Object Type],
i.[name] AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created,
s.no_recompute,
s.user_created,
s.is_incremental,
s.is_temporary,
st.row_count,
st.used_page_count
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
INNER JOIN
sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id]
WHERE
o.[type] IN ('U', 'V') -- 'U' for User-defined table, 'V' for View
AND st.row_count > 0
ORDER BY
STATS_DATE(i.[object_id], i.index_id) DESC
OPTION (RECOMPILE);
What Does This Query Tell You?
- Object Name: The schema and name of your tables or views.
- Object Type: Clearly distinguishes between user-defined tables ('U') and views ('V').
- Index Name: The name of the index associated with the object.
- Statistics Date: Crucially, this shows when the statistics for that index were last updated. Older dates might indicate outdated statistics, which can impact query performance.
- auto_created, no_recompute, user_created, is_incremental, is_temporary: These flags provide insights into how the statistics were created and their properties.
- row_count: The number of rows in the partition.
- used_page_count: The number of pages used by the partition.
Why Is This Important?
Regularly reviewing this information helps you:
- Identify **stale statistics** that might need updating.
- Understand the **structure and indexing** of your database objects.
- Monitor **database growth** and object sizes.
This query provides a solid foundation for proactive database maintenance and performance tuning. Give it a try in your SQL Server environment!
