Here is the question asked in my yesterday’s Comprehensive Database Performance Health Check about rows sampled from sys.dm_db_stats_properties.
SQL SERVER – Statistics Modification Counter – sys.dm_db_stats_properties
The Statistics Modification Counter tracks changes relevant to each statistics object. I inspect it alongside the affected query and estimates.
Scalar Function Statistics With sys.dm_exec_function_stats
Read dm_exec_function_stats to rank scalar UDF activity, inspect callers, verify collection settings, and recognize inlined work.
Memory Used by Each Table in the SQL Server Buffer Pool
Memory used by each table is the number of its pages in the buffer pool times 8 KB. This post adds them up with sys.dm_os_buffer_descriptors, shows why a plain division by 128 hides small tables, and compares the cached pages with the pages each table owns on disk.
Buffer Pool Pages for One Table in SQL Server
Buffer pool pages for one table show how much of one table sits in memory right now. This post lists them by index, page type and page level with sys.dm_os_buffer_descriptors, then shows which pages a seek, a scan and a large text read pull in. It uses the join that the documentation gives for each allocation unit type.
Memory-Optimized TempDB Tables: List Them and Check Status
Memory-optimized TempDB tables are the system tables that SQL Server 2019 and later can keep in memory. This post shows two queries: one that checks whether the feature is on, and one that lists the tables it covers. It also explains the limits that come with the feature.
Visible Offline Schedulers: Find CPUs SQL Server Ignores
Visible offline schedulers are CPUs that Windows shows and SQL Server refuses to use. This post reads the scheduler status with tested queries, compares it with the CPU count and affinity, and explains the two common causes. It also shows the statement that returns the CPUs to automatic affinity.