The Query Processor, as the name suggests, accepts all queries submitted to SQL Server, devises a plan for their optimal execution, and then executes the plan and delivers the required results. The Storage Engine is responsible for reading data between the disk and memory in a manner that optimizes concurrency while maintaining data integrity. How the Query Optimizer WorksĪt the core of the SQL Server Database Engine are two major components: the Storage Engine and the Query Processor, also called the Relational Engine. During installation you may choose to install all the databases or at least the AdventureWorks and AdventureWorksDW. You need to download the family of sample databases for your version, either SQL Server 2008 or SQL Server 2008 R2. Note that these sample databases are not included in your SQL Server installation by default, but can be downloaded from the CodePlex web site. All code has been tested for these databases on both SQL Server 2008 and SQL Server 2008 R2.
Note: This article contains a large number of example SQL queries, all of which are based on the AdventureWorks database. We’ll also cover some of the background and challenges of query optimization, such as cardinality and cost estimations, and a section on how to read and understand them is included as well. We’ll end with a discussion of join ordering, one of the most complex problems in query optimization, and shows how joining tables in an efficient order improves the performance of a query but, at the same time, can exponentially increase the number of execution plans that should be analyzed by the Query Optimizer. This article starts with an overview on how the SQL Server Query Optimizer works and introduces the concepts that I cover in more detail in my book. Naturally, a better understanding of how the Query Optimizer works can help both database administrators and developers to write better queries and to provide the Query Optimizer with the information it needs to produce efficient execution plans. After all, selecting the right (or wrong) execution plan could mean the difference between a query execution time of milliseconds, and one of minutes or even hours. Therefore, it is the SQL Server component that has the biggest impact on the performance of your databases. Indeed, given that the Query Optimizer cannot consider every possible plan for every query, it actually has to do a cost-based balancing act, considering both the cost of finding potential plans and the costs of plans themselves. It analyzes a number of candidate execution plans for a given query, estimates the cost of each of these plans and selects the plan with the lowest cost of the choices considered.
The SQL Server Query Optimizer is a cost-based optimizer. The SQL Server Query Optimizer - Simple Talk Skip to content
0 kommentar(er)
