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 statistics that have not been updated for a week for tables where more than 1,000 rows changed.

How should you complete the Transact-SQL statement? To answer, configure the appropriate Transact-SQL segments in the answer area.

View Answer

Answer:

Explanation:

Box 1: stats_date

See example below.

Box 2: rowmodctr

See examplebelow.

Box 3: stats_date

You need to identify statistics that have not been updated for a week.

Box 4: rowmodctr

You need to identify that more than 1,000 rows changed.

Rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.

Example: We will query every statistics object which was not updated in the last day and has rows modified since the last update. We will use the rowmodctr field of sys.sysindexes because it shows how many rows were inserted, updated or deleted since the last update occurred. Please note that it is not always 100% accurate in SQL Server 2005 and later, but it can be used to check if any rows were modified.

–Get the list of outdated statistics

SELECTOBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr

FROM sys.sysindexes

WHERE STATS_DATE (id, indid)<=DATEADD(DAY,-1,GETDATE())

AND rowmodctr>0

AND id IN (SELECT object_id FROM sys.tables)

GO

After collecting this information, we can decide which statistics require an update.

References: https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/syssysindexes-transact-sql

https://www.mssqltips.com/sqlservertip/2628/how-to-find-outdated-statistics-in-sql-server-2008/