|
Below is an example of Tuning Queries using Visual
Explain Tools in Aqua Data Studio
A table Person.Address is created in SQL Server 2008 with 19614
rows
 |
| 1. Table Structure |
Before Tuning - Query Execution Plan
 |
 |
 |
2. COUNT(*)
statement executed
|
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 PERSON.ADDRESS WHERE
ROWGUID='421F2082-5FE3-4CD8-AC24-C7F594BD7C42'
is executed. Try to reduce the Query Cost by introducing an
index for ROWGUID
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.
|
