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.
Hot Area:

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.
Hot Area:

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



Leave a Reply 0

Your email address will not be published. Required fields are marked *