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.
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.
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.