[dba-SQLServer] Execution statistics

I wonder why the logical reads went up even though execution time went down.

  Maybe the optimizer found that it was cheaper to create an executin plan
that gets most of the data from the index and the rest from the table?  Was
the fillfactor of the new index set to low percentage (index created over
many pages)?

===========This was my expectation too -- it's a very simple example query
-- but there's one JOIN and one WHERE clause. Just to experiment, I added an
index to the column specified in the WHERE clause. Ordinarily, this column
would not be a good candidate for an index. The reads increase on the table
specified in the WHERE clause (region), the one with the new index.
Execution time does go down. 

I know that inappropriate indexes can be a serious performance problem. 

Another interesting side-product was that even after I removed the new index
on the region column the execution stayed down -- once it went down, it
stayed down. I hadn't expected that. 



USE northwind
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT lastname, firstname, orderid 
FROM Employees
INNER JOIN ORDERS
ON Employees.EmployeeID = Orders.EmployeeID 
WHERE region = 'WA'
SET STATISTICS IO ON
SET STATISTICS TIME OFF


Before Index:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(606 row(s) affected)

Table 'Orders'. Scan count 5, logical reads 11, physical reads 0, read-ahead
reads 0.
Table 'Employees'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 15 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

After Index:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(606 row(s) affected)

Table 'Orders'. Scan count 5, logical reads 11, physical reads 0, read-ahead
reads 0.
Table 'Employees'. Scan count 1, logical reads 11, physical reads 0,
read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 6 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.