Monitoring Auto-Update Statistics
As discussed in Chapter 31, “Understanding Query Optimization,” SQL Server updates index statistics automatically as data is changed in a table. In some environments, excessive auto-updating of statistics can affect system performance while the statistics are being updated. SQL Profiler can be used to monitor auto-updating of statistics as well as automatic statistics creation.
To monitor auto-updating of statistics, you create a trace and include the AutoStats
event from the Performance
event category. Then you select the TextData
, Integer Data
, Success
, and Object ID
columns. When the AutoStats
event is captured, the Integer Data
column contains the number of statistics updated for a given table, the Object ...
Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.