Jaa

Logical I/Os - Not a useful performance metric?

  • Artikkeli

Joe Chang wrote a compelling post on "Why Logical IO is a poor performance metric".  I do admit that I will continue to look at logical IO within the context of the Waits and Queues methodology, however as a stand-alone metric, I believe Joe Chang has a good point.

 I thought I would run a few tests to see whether I can confirm what Joe Chang describes.  One example I'll show here...  I executed the following query in the AdventureWorks database:

SELECT e.[EmployeeID], c.[PasswordSalt]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
    ON c.[ContactID] = e.[ContactID]

In my test, this query was performing a nested loop join, and had a total of 616 logical reads.  The elapsed time was 3 ms and CPU time was 0 ms according to SET STATISTICS TIME.

Next, I executed the following query, forcing a HASH join:

SELECT e.[EmployeeID], c.[PasswordSalt]
FROM [HumanResources].[Employee] e
INNER HASH JOIN  [Person].[Contact] c
    ON c.[ContactID] = e.[ContactID]

The query performed a hash join, and had a total of 570 logical reads (less than the previous query).  The elapsed time was 20 ms and CPU time was 31 ms according to SET STATISTICS TIME.

So although the logical IOs were less in the HASH scenario, the execution time was longer.  CPU and execution time was higher in the HASH scenario. 

So following CPU time tells us the true story - whereas looking at Logical IO alone as a performance metric would have led us to the wrong conclusion regarding which query was less effecient.

Comments