Inconsistent SQL Server Keyword Search

Question

I have a large table in SQL Server 2005 in which I have to select records from a non-clustered key, and I try to optimize this process as much as possible.

There are several columns in the table, and I added non-clustered indexes in three different columns.

SELECT * FROM table WHERE Field1 = 10;
SELECT * FROM table WHERE Field2 = 40;
SELECT * FROM table WHERE Field3 = 'A';

Field1 and Field2 are integer fields, and Field3 is varchar.

When I request an evaluation query plan from SQL Server for these three queries, I get basically the same plan for each:

SELECT -> Nested Loop -> Index Seek
                      -> Key Lookup

But I find that the execution time is very inconsistent. In particular, the second request takes 98% of the total cost of the request. Its execution plan is the same as the rest, except that the Key Lookup step has a cost of 100% compared to Index Seek. In the other two queries, it is closer to 50%.

I understand that key search is undesirable and can be avoided by adding columns to indexes so that extra columns do not need to be searched. However, in this case, I want all the columns in the table to be returned to me, so it makes no sense to add them to the index. But how can one index result in a Key Lookup operation taking much longer than another keyword search?

Answer 1

, , ?

.

(QO) , . b 100, 10000 ( 100 ). , , averaged.

, *, actual execution time . QO , .

SELECT * FROM table WHERE Field2 = 40;
SELECT * FROM table WHERE Field2 = 42;

, 42 - , 80% . 40 - , 1 . , QO ? , , /-, , clustered index , 80% () .

*

set statistics io on
set statistics time on

Answer 2

, . , , , , . , , .

, , . , (, , , ), . ( , , ..). , . "" "where", .

Answer 3

, " ( )" , .

It is better to look at actual execution plans in conjunction with io and time statistics to understand what is actually happening.

set statistics io on
set statistics time on

SELECT * FROM table WHERE Field1 = 10;
SELECT * FROM table WHERE Field2 = 40;
SELECT * FROM table WHERE Field3 = 'A';

Then look at the logical reads, processor time, and elapsed time for each request.