SQL Server Diagnostic Information Queries Detailed, Day 26

For Day 26 of this series, we start out with Query #59, which is Table Properties. This query retrieves information from the sys.tables object catalog view, and the sys.partitions object catalog view about various table properties in the current database. Query #59 is shown in Figure 1.

   1: -- Get some key table properties (Query 59) (Table Properties)

   2: SELECT OBJECT_NAME(t.[object_id]) AS [ObjectName], p.[rows] AS [Table Rows], p.index_id, 

   3:        p.data_compression_desc AS [Index Data Compression],

   4:        t.create_date, t.lock_on_bulk_load, t.is_replicated, t.has_replication_filter, 

   5:        t.is_tracked_by_cdc, t.lock_escalation_desc, t.is_memory_optimized, t.durability_desc, t.is_filetable,

   6:        t.temporal_type_desc, t.is_remote_data_archive_enabled, t.remote_data_archive_migration_state_desc, t.is_external -- new for SQL Server 2016

   7: FROM sys.tables AS t WITH (NOLOCK)

   8: INNER JOIN sys.partitions AS p WITH (NOLOCK)

   9: ON t.[object_id] = p.[object_id]

  10: WHERE OBJECT_NAME(t.[object_id]) NOT LIKE N'sys%'

  11: ORDER BY OBJECT_NAME(t.[object_id]), p.index_id OPTION (RECOMPILE);


  13: -- Gives you some good information about your tables

  14: -- Is Memory optimized and durability description are Hekaton-related properties that were new in SQL Server 2014

  15: -- temporal_type_desc, is_remote_data_archive_enabled, remote_data_archive_migration_state_desc, is_external are new in SQL Server 2016

Figure 1: Query #59 Table Properties

This query helps you understand what is going on with properties for the tables in the current database, showing you things such as whether they are being replicated, being tracked by change data capture, whether they are Hekaton tables, whether they are StretchDB tables, etc. It also shows you the data compression status for every index in each table. This can help you find possible data compression candidates.


Query #60 is Statistics Update. This query retrieves information from the sys.objects object catalog view, the sys.indexes object catalog view, the sys.stats object catalog view, and the sys.dm_db_partition_stats dynamic management view about the properties and status of the statistics in the current database. Query #60 is shown in Figure 2.

   1: -- When were Statistics last updated on all indexes?  (Query 60) (Statistics Update)

   2: SELECT SCHEMA_NAME(o.Schema_ID) + N'.' + o.NAME AS [Object Name], o.type_desc AS [Object Type],

   3:       i.name AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 

   4:       s.auto_created, s.no_recompute, s.user_created, s.is_incremental, s.is_temporary,

   5:       st.row_count, st.used_page_count

   6: FROM sys.objects AS o WITH (NOLOCK)

   7: INNER JOIN sys.indexes AS i WITH (NOLOCK)

   8: ON o.[object_id] = i.[object_id]

   9: INNER JOIN sys.stats AS s WITH (NOLOCK)

  10: ON i.[object_id] = s.[object_id] 

  11: AND i.index_id = s.stats_id

  12: INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)

  13: ON o.[object_id] = st.[object_id]

  14: AND i.[index_id] = st.[index_id]

  15: WHERE o.[type] IN ('U', 'V')

  16: AND st.row_count > 0

  17: ORDER BY STATS_DATE(i.[object_id], i.index_id) DESC OPTION (RECOMPILE);  


  19: -- Helps discover possible problems with out-of-date statistics

  20: -- Also gives you an idea which indexes are the most active

Figure 2: Query #60 Statistics Update

This query shows you a number of relevant properties about the index-associated statistics in your current database, ordered by the last time that statistics were updated. This can help you determine whether you might have a problem with out of date statistics. My general guidance about statistics is that you should use the default database properties of auto create and auto update for statistics, plus I think you should also use the auto update statistics asynchronously database property, along with global trace flag 2371. In some situations, you may also want/need to do additional statistics maintenance on highly volatile tables with SQL Server Agent jobs.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.