10.3 Sample Query Tuning Demo

Tuning Queries using Visual Explain Tools in Aqua Data Studio 7.0

A table Person.Address is created in SQL Server 2008 with 19614 rows

Table Structure

Before Tuning - Understanding Query Plan

Count	Query Cost	Table Scan

From ADS 7.0 Visual Explain Tool, you can find out that the Optimizer uses a full table scan while the query SELECT * FROM    PERSON.ADDRESS WHERE ROWGUID='421F2082-5FE3-4CD8-AC24-C7F594BD7C42' is executed. Let us  try to reduce the Query Cost by introducing an index for ROWGUID

After Tuning - Introducing Index

Index	Query Cost	Index Scan

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 this example, the Optimizer uses an Index Scan this time