Home » Microsoft » 70-764 v.2 » How should you complete the Transact-SQL statement?
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:
Correct Answer:
Explanation/Reference:
Explanation:
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