Get last statistics updated date

 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!

Get Table names, row counts, and compression status

 Get Table names, row counts, and compression status

When working with SQL Server, understanding the row counts and data compression for your **clustered indexes or heaps** is crucial for performance monitoring and optimization. This SQL query provides a clear overview of this information.

SELECT SCHEMA_NAME(o.Schema_ID) AS [Schema Name]
 ,OBJECT_NAME(p.object_id) AS [ObjectName]
 ,SUM(p.Rows) AS [RowCount]
 ,p.data_compression_desc AS [Compression Type]
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.objects AS o WITH (NOLOCK) ON p.object_id = o.object_id
WHERE index_id < 2 -- This filters for clustered indexes (index_id = 1) and heaps (index_id = 0)
 AND OBJECT_NAME(p.object_id) NOT LIKE N'sys%' -- Exclude system tables
 AND OBJECT_NAME(p.object_id) NOT LIKE N'spt_%'
 AND OBJECT_NAME(p.object_id) NOT LIKE N'queue_%'
 AND OBJECT_NAME(p.object_id) NOT LIKE N'filestream_tombstone%'
 AND OBJECT_NAME(p.object_id) NOT LIKE N'fulltext%'
 AND OBJECT_NAME(p.object_id) NOT LIKE N'ifts_comp_fragment%'
 AND OBJECT_NAME(p.object_id) NOT LIKE N'filetable_updates%'
 AND OBJECT_NAME(p.object_id) NOT LIKE N'xml_index_nodes%'
 AND OBJECT_NAME(p.object_id) NOT LIKE N'sqlagent_job%'
 AND OBJECT_NAME(p.object_id) NOT LIKE N'plan_persist%'
GROUP BY SCHEMA_NAME(o.Schema_ID)
 ,p.object_id
 ,data_compression_desc
ORDER BY SUM(p.Rows) DESC
OPTION (RECOMPILE);

This query will help you quickly identify tables with large row counts and see what type of data compression is applied to them. It's a handy script to keep in your DBA toolkit!