In SQL Server, when should a database administrator run the UPDATE_STATISTICS command, and what is its main purpose for query optimization?

Introduction / Context:
SQL Server uses statistics on indexes and columns to estimate how many rows will match a query predicate. These estimates are crucial for the query optimizer to choose efficient execution plans. The UPDATE_STATISTICS command allows administrators to refresh these statistics. Interviewers often ask when and why this command should be used, because it relates directly to performance tuning and maintenance tasks.

Given Data / Assumptions:
We are dealing with Microsoft SQL Server databases that handle ongoing inserts, updates, and deletes.Statistics describe data distribution in indexes and columns.SQL Server may auto update statistics, but manual control is sometimes useful.The focus is on performance optimization, not backup or security.

Concept / Approach:
Statistics help the optimizer estimate cardinalities, which influence choices such as index usage, join strategies, and join order. When data volumes or distributions change significantly, old statistics become inaccurate. UPDATE_STATISTICS can be run for specific tables, indexes, or the whole database to recalculate these statistics. While auto update features exist, they may not always trigger at the ideal time for large or highly volatile tables, so administrators schedule manual updates during maintenance windows to keep query plans efficient.

Step-by-Step Solution:
Step 1: Recall that statistics are used by the optimizer, not by backup or login mechanisms.Step 2: Understand that statistics become stale when there are many changes in table data.Step 3: Recognize that updating statistics refreshes the underlying histograms and density values.Step 4: Look at the answer options and identify the one that explicitly connects UPDATE_STATISTICS with data distribution changes and better query plans.Step 5: Option A matches this concept and is therefore correct.

Verification / Alternative check:
You can verify the effect of statistics by examining execution plans before and after running UPDATE_STATISTICS on a heavily changed table. Often, the optimizer will choose different indexes or join methods once it has more accurate information. Monitoring tools may also show improvements in query runtime. These observations confirm that the command is associated with performance and plan quality, not with the tasks suggested in the other options.

Why Other Options Are Wrong:
Option B says UPDATE_STATISTICS must run before every SELECT, which would be impractical and is not required, especially since SQL Server has automatic mechanisms. Option C confuses the command with crash recovery, which uses the transaction log and recovery processes, not statistics updates. Option D claims that it deletes backup files, but backup management is handled by other scripts and maintenance jobs. Option E associates it with login creation, which is unrelated to query optimization.

Common Pitfalls:
Some administrators ignore statistics, assuming that automatic updates are always sufficient. In very large or busy systems, manual updates can be necessary to address performance regressions. Another pitfall is updating statistics too frequently without considering the overhead, which can cause unnecessary load. A balanced strategy considers workload patterns, table size, and query behavior when scheduling UPDATE_STATISTICS jobs.

Final Answer:
The correct answer is: It should be used when data distribution has changed significantly to refresh statistics so that the optimizer can generate better query plans.