The Query Store feature in SQL Server automatically captures a history of queries, plans, and runtime statistics, and persists this information in the database. The Database Engine Tuning Advisor (DTA) supports a new option to use the Query Store to automatically select an appropriate workload for tuning.
The optimizer attempts to create the most optimal setup program for a SQL statement.
The optimizer choose the program with the most affordable cost among all considered candidate plans. The optimizer utilizes available figures to compute price. For a specific query in a provided atmosphere, the price computation balances for elements of query setup such as I/U, Central processing unit, and communication.
For illustration, a query might demand details about employees who are administrators. If the optimizer statistics show that 80% of employees are managers, then the optimizer may determine that a complete table scan is nearly all efficient. However, if figures suggest that really few employees are managers, then reading through an index followed by a table gain access to by rowid may be more efficient than a complete table scan.
Because the database has several internal data and tools at its disposal, the optimizer is certainly generally in a much better placement than the user to determine the optimal technique of statement setup. For this reason, all SQL claims make use of the optimizer.