Statistics updates
Updating statistics is important. If the statistics are out of date, queries to a table would take longer time to execute. This is also a typical issue for bulk inserts.
Statistics are generated when you add an index to a table that contains data or when you run the UPDATE STATISTICS command. In most cases, you should allow SQL Server to create and update statistics automatically by setting one of the following database options, each of which is enabled by default:
AUTO_UPDATE_STATISTICS
SQL Server updates statistics automatically as needed. It determines an update is necessary by using a counter on modifications to column values. This counter is incremented when a row is inserted or deleted or when an indexed column is updated. The counter is reset to 0 when the statistics are generated. When it does this, it acquires compile locks and query plans might require recompilation. You can disable this option by using the sp_autostats system stored procedure.
AUTO_UPDATE_STATISTICS_ASYNC
When it is enabled, SQL Server updates statistics asynchronously. That is, SQL Server uses a background thread so as not to block query execution. In this case, the query optimizer might choose a less than optimal query execution plan until the statistics are updated. Use the ALTER DATABASE T-SQL command to disable this option.
AUTO_CREATE_STATISTICS
During query execution, SQL Server creates statistics on individual columns in query predicates to help the query optimizer improve query plans. Use the ALTER DATABASE T-SQL command to disable this option.
Even when statistics are set to update automatically, SQL Server does not update statistics unless one of the following thresholds is met:
One or more rows is added to an empty table.
More than 500 rows are added to a table having fewer than 500 rows.
More than 500 rows are added to a table having more than 500 rows and the number of rows added is more than a dynamic percentage of total rows. With a small table under 25,000 rows, this percentage is around 20 percent. As the number of rows in the table increases, the percentage rate that triggers a statistics update is lower. For example, SQL Server updates statistics for a table with 1 billion rows when more than 1 million changes occur, or 0.1 percent. Prior to SQL Server 2016, this threshold was fixed at 20 percent of the original total number of rows in the table which means that 200 million rows were required to trigger an update of statistics.
You can check to see if SQL Server automatically created statistics in a database by checking the value of the auto_created column in the sys.stats catalog view.
To check the last time the statistics were updated for each statistics object in a table, you can use the STATS_DATE system function. As an alternative, you can use the sys.dm_db_stats_properties DMV to get row counts and modifications occurring since the last statistics update in addition to the last update date.
Question:
You recently started as a new database administrator at Consolidated Messenger. Because you are unfamiliar with this SQL Server environment as a new employee, you decide to perform some analysis to determine if there are any significant problems to fix.
Which query or command do you use to find the most recent update for statistics on tables or indexed views?
A. DBCC SHOW_STATISTICS(‘ConsolidatedMessengerDB’, All Indexes);
B. SELECT name, STATS_DATE(object_id, stats_id) FROM sys.stats WHERE object_id IN (SELECT object_id FROM sys.objects WHERE type = ‘U’);
C. SELECT name, auto_created (object_id, stats_id) FROM sys.stats WHERE object_id IN (SELECT object_id FROM sys.objects WHERE type = ‘U’);
D. SELECT name, auto_created (object_id, stats_id) FROM sys.stats WHERE object_id IN (SELECT object_id FROM sys.objects WHERE type = ‘U’);
The answer is B. The sys.stats catalog view contains both the stats_id and object_id columns necessary to use the STATS_DATE system function that returns the most recent update date for an object’s statistics. The DBCC SHOW_STATISTICS command requires you to include a specific index name as the second argument, therefore A is incorrect due to the syntax. Similarly, C and D are examples of incorrect syntax because T-SQL does not include an auto_created function.
Example:
Answer:
