"Statistics: Staleness and Updates"

1. What does the histogram in statistics indicate about data distribution in SQL Server?

It contains metadata about database schemas. It describes the range boundaries and row counts of the leading column. It shows the structure of user-defined functions. It provides execution plans for queries.

2. What happens when statistics are stale in SQL Server?

The query is automatically optimized for better performance. The query may not utilize the latest data effectively, leading to poorly estimated execution plans. The database automatically updates the statistics to avoid staleness. The performance of all queries is improved.

3. Which option is a recommended mitigation for handling recent data estimates being wrong on large tables?

Scheduling backups more frequently. Using filtered statistics on the current year only. Inserting data in smaller transactions. Removing indices on large tables.

4. What is the purpose of the `PERSIST_SAMPLE_PERCENT` option in SQL Server?

To permanently delete statistics for a table. To ensure that auto-updates keep using a specific sample rate. To disable auto-updating of statistics altogether. To increase the histogram step count.

Continue: "SQL Server 2022 Intelligent Query Processing" → Review this lesson Retake quiz