Exam 70-764 topic 1 question 309 discussion

HOTSPOT -
You have a Microsoft SQL Server instance that hosts a database named DB1 that contains 800 gigabyte (GB) of data. The database is used 24 hours each day.
You implement indexes and set the value of the Auto Update Statistics option set to True.
Users report that queries take a long time to complete.
You need to identify tables that meet the following requirements:
✑ More than 1,000 rows have changed.
✑ The statistics have not been updated in over a week.
How should you complete the Transact-SQL statement?
Hot Area:

Suggested Answer:
Example:
SELECT obj.name, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter

FROM sys.objects AS obj -
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE modification_counter > 1000
order by modification_counter desc;
sys.sysindexes contains one row for each index and table in the current database. rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
Example 2:

SELECT -
id AS [Table ID]
, OBJECT_NAME(id) AS [Table Name]
, name AS [Index Name]
, STATS_DATE(id, indid) AS [LastUpdated]
, rowmodctr AS [Rows Modified]

FROM sys.sysindexes -
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE())
AND rowmodctr>10 AND (OBJECTPROPERTY(id,'IsUserTable'))=1
References: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/493b90e3-cdb8-4a16-8249-849ba0f82fcb/how-to-find-outdated-statistics-in-sql-server? forum=transactsql