Microsoft’s SQL Server query optimizer uses a cost-based approach by deciding on the “good enough” way — not necessarily the best way to retrieve results. This is because considering all possibilities for retrieving the data might be inefficient.
When a query is executed, SQL Server considers several factors including available indexes and statistics. Statistics track the distribution of the data and the uniqueness or selectivity of the data. These statistics influence the optimizer in selecting a proper path to return results. Statistics are maintained on indexes and columns within the system, and they can be automatically or manually updated. For more information on indexes, see additional blog posts.
Consider the following query:
Select last, first from customer
where city = ‘Austin’
If I own a bakery in Austin, Texas and most of my customers are located there, then the value in the where clause isn’t very specific. In this case, SQL Server might choose to scan the rows of the table to provide the results. If, however, I own an international business and we have customers in many different cities and countries, then the value of ‘Austin’ might be very specific. In the first scenario, having an index on city might be ignored altogether while it would be very useful in the second scenario.
Estimated vs. Actual Execution Plans
In order to determine what the optimizer will do or plans to do (or actually does once we run the query) is by viewing execution plans. The estimated execution plan is just that — we are asking SQL Server to tell us what it thinks it will do when the query is executed. It’s almost like getting a bid on a job or having a blueprint with estimates for building a house. The actual execution plan returns results of what the optimizer actually chose when we executed the query. If we consider our house analogy, there could be a difference between estimates and actual. Perhaps the permits ended up costing more, or maybe we were able to save money on materials. In much the same way, structural modifications, statistics changes, or index changes could all cause the estimated and actual execution plans to differ.
To access the actual or estimated execution plan, right-click in the query window and choose “Actual Execution Plan” or “Estimated Execution Plan.” Remember that the actual plan will not show up until you actually run the query.
Related Courses
Writing Queries Using Microsoft SQL Server 2008 Transact-SQL (M2778)
MCSA: SQL Server 2012 Boot Camp
Querying Microsoft SQL Server (M20461)