Database Reference
In-Depth Information
Figure 8-7.
Execution plan with a
WHERE
clause
The resultant number of logical reads is as follows:
Table 'Product'. Scan count 0, logical reads 2
The behavior of the query optimizer shows that the
WHERE
clause column helps the optimizer choose an optimal
indexing operation for a query. This is also applicable for a column used in the
JOIN
criteria between two tables.
The optimizer looks for the indexes on the
WHERE
clause column or the
JOIN
criterion column and, if available,
considers using the index to retrieve the rows from the table. The query optimizer considers indexes on the
WHERE
clause columns and the
JOIN
criteria columns while executing a query. Therefore, having indexes on the frequently
used columns in the
WHERE
clause, the
HAVING
clause, and the
JOIN
criteria of a SQL query helps the optimizer avoid
scanning a base table.
When the amount of data inside a table is so small that it fits onto a single page (8KB), a table scan may work
better than an index seek. If you have a good index in place but you're still getting a scan, consider this issue.
Use Narrow Indexes
For best performance, you should use as narrow a data type as is practical when creating indexes. Narrow in this
context means as small a data type as you realistically can. You should also avoid very wide data type columns in an
index. Columns with string data types (
CHAR
,
VARCHAR
,
NCHAR
, and
NVARCHAR
) sometimes can be quite wide, as can
binary and globally unique identifiers (GUIDs). Unless they are absolutely necessary, minimize the use of wide data
type columns with large sizes in an index. You can create indexes on a combination of columns in a table. For the best
performance, use as few columns in an index as necessary. But, use the columns you need to use to define a useful
key for the index.
A narrow index can accommodate more rows in an 8KB index page than a wide index. This has the following effects:
•
Reduces I/O (by having to read fewer 8KB pages)
•
Makes database caching more effective because SQL Server can cache fewer index pages,
consequently reducing the logical reads required for the index pages in the memory
•
Reduces the storage space for the database
To understand how a narrow index can reduce the number of logical reads, create a test table with 20 rows and
an index.
IF (SELECT OBJECT_ID('Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1 (C1 INT, C2 INT);
