[dba-SQLServer] Execution statistics

Not sure how much signficance to attach to the execution time of this 
example since it is mostly less than 20 milliseconds because there might be 
something running in background that can affect its execution time (although 
after repeated running the query with and without the index, with the new 
region index I can get it under 10ms a few times but not at all without the 
region index).

The query with the region index looks like it is getting region data from 
the region index you created and then getting the rest of the employee data 
via bookmark lookup (a sign for improvement?).  It might be better for this 
query if your region index includes the other columns (ie. firstname 
lastname) of your query instead of just the region column column; to 
experiment, if you create a second region index (without dropping the first) 
but this time include the name columns, the optimzer will see that there are 
two region indexes to choose from but will choose the second index because 
it is cheaper execution plan.

CREATE INDEX IX_EMPLOYEES_region2 on employees(region, LASTNAME, FIRSTNAME)

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

With the second new index, I can now get the query execution time under 
10ms.

HTH
Billy


>From: "Susan Harkins" <ssharkins at bellsouth.net>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: RE: [dba-SQLServer] Execution statistics
>Date: Thu, 11 Aug 2005 09:43:59 -0400
>
>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.
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>