Sample Query Tuning Demo

Below is an example of Tuning Queries using Visual Explain Tools in Aqua Data Studio

A table, Customers, exists in SQL Server 2012. It has no Indexes.

1. Table Structure

Before Tuning - Query Execution Plan

2. Query with simple WHERE clause	3. Query Cost displayed in an Execution Plan	4. Table Scan in a Visual Explain Diagram

From Aqua Data Studio's Visual Explain Tool, you can find out that the Optimizer uses a full table scan while the query

SELECT * FROM Customers WHERE CompanyName='Alfreds Futterkiste' is executed. Try to reduce the Query Cost by introducing an index for CompanyName.

After Tuning - Introducing an Index

5. CREATE UNIQUE INDEX statement executed	6. Reduced query Cost displayed in an Execution Plan	7. Index Scan in a Visual Explain Diagram

Table scan versus index scan

The Optimizer normally chooses a table scan if no appropriate index has been created, or if an index scan would be more costly. An index scan might be more costly when the table is small and the index-clustering ratio is low, or the query requires most of the table rows. In the example above, the Optimizer uses an Index Scan.