For this query device two execution plans and estimate their

For this query, device two execution plans and estimate their costs (that is, the number of block accesses). Do not include the cost of writing the final answer. Try to use more efficient execution plans.

select *

from COURSES, STUDENTS, TRANSCRIPTS

where STUDENTS.ID = TRANSCRIPTS.ID and COURSES.CNo = TRANSCRIPTS.CNo and STUDENT.Dept = \'CS\' and COURSES.Dept = \'Math\'

Solution

Answer :-

Execution Plan:-

When you run a query against the relational database engine, SQL Server carries out two major steps. The first step is to compile the query into a plan. The second step involves executing the plan. SQL Server 2012 can return a query execution plan in three formats: text, XML, and graphical.

The easiest way to look at an execution plan is to view its graphical representation. With SQL Server 2012, you use SSMS to do so. For example, after opening a new query window in SSMS, select Query, Include Actual Execution Plan and run the following query against the AdventureWorks sample database.

Query:-

select * from COURSES, STUDENTS, TRANSCRIPTS

where STUDENTS.ID = TRANSCRIPTS.ID and COURSES.CNo = TRANSCRIPTS.CNo and STUDENT.Dept = \'CS\' and COURSES.Dept = \'Math\'

First, an execution plan is returned for each query submitted in a query batch. (In our example, we submitted only one query.) An execution plan is read from top to bottom, then right to left.

For example, the first step in this execution plan is to perform an Index Scan of the COURSES index to return the COURSES and ID columns.

The results of this step are combined with the next step (a Cluster Index Scan against the SalesPerson clustered index). Finally, the results of this nested loop are fed into the final SELECT operation.

Estimated Execution Plans :

To view an estimated execution plan, select Query, then choose Display Estimated Execution Plan in SSMS. Unlike an actual plan, the query isn’t run; instead, only an estimated plan is returned. For large, complex queries, you’ll notice the estimated plan is returned very quickly (relative to running the actual query and viewing the actual plan). Also, when you look at the step details of an estimated plan, you’ll see only estimated values (i.e., none of the actual values associated with actual plans are returned).

Using Estimated Execution Plans in an Application :

The execution plans work and how to view the graphical form of an execution plan within SSMS. Now I’ll walk you through how to use an execution plan within an application.

Estimate Cost:

The customer’s requirement of identifying complex queries and/or queries that return large numbers of rows, we’re going to focus on two estimated values: Estimated Number of Rows and Estimated Subtree Cost. The Estimated Number of Rows value is self-explanatory. Estimated Subtree Cost is defined as the estimated cumulative cost of an operation and all child operations.