QUERY  1 
set showplan on
set statistics time on
set statistics io on
go
select count(*)
from BusEntRoleProd BERP(index cBusEntRoleProd), BusinessEntity BE
where BE.businessEntityId = BERP.businessEntityId
and BERP.roleId =  11 
and BERP.productId = BERP.productId
go               
             
 1              
 2               Execution Time  0 .
 3               SQL Server cpu time:  0  ms.  SQL Server elapsed time:  0  ms.
 4               Total writes for this command:  0 
 5              
 6               Execution Time  0 .
 7               SQL Server cpu time:  0  ms.  SQL Server elapsed time:  0  ms.
 8              
 9               QUERY PLAN FOR STATEMENT  1  (at line  1 ).
 10           
 11           
 12                STEP  1 
 13                    The type of query is SELECT.
 14                    Evaluate Ungrouped COUNT AGGREGATE.
 15           
 16                    FROM TABLE
 17                        BusEntRoleProd
 18                        BERP
 19                    Nested iteration.
 20                    Using Clustered Index.
 21                    Index : cBusEntRoleProd
 22                    Forward scan.
 23                    Positioning at start of table.
 24                    Using I/O Size  16  Kbytes for data pages.
 25                    With LRU Buffer Replacement Strategy for data pages.
 26           
 27                    FROM TABLE
 28                        BusinessEntity
 29                        BE
 30                    Nested iteration.
 31                    Using Clustered Index.
 32                    Index : x1
 33                    Forward scan.
 34                    Positioning by key.
 35                    Keys are:
 36                        businessEntityId  ASC
 37                    Using I/O Size  16  Kbytes for data pages.
 38                    With LRU Buffer Replacement Strategy for data pages.
 39           
 40                STEP  2 
 41                    The type of query is SELECT.
 42           
 43            Parse and Compile Time  0 .
 44            SQL Server cpu time:  0  ms.
 1                56500 
 1               Table: BusEntRoleProd scan count  1 , logical reads: (regular= 7034  apf= 0  total= 7034 ), physical reads: (regular= 0  apf= 0  total= 0 ), apf IOs used= 0 
 2               Table: BusinessEntity scan count  56575 , logical reads: (regular= 171548  apf= 0  total= 171548 ), physical reads: (regular= 0  apf= 0  total= 0 ), apf IOs used= 0 
 3               Total writes for this command:  0 
 4              
 5               Execution Time  5 .
 6               SQL Server cpu time:  500  ms.  SQL Server elapsed time:  513  ms.

QUERY  2 
set showplan on
set statistics time on
set statistics io on
go
select count(*)
from  BusEntRoleProd BERP join BusinessEntity BE on (BERP.businessEntityId = BE.businessEntityId and BERP.productId = BERP.productId)
where BERP.roleId =  11 
go

 1              
 2               Execution Time  0 .
 3               SQL Server cpu time:  0  ms.  SQL Server elapsed time:  0  ms.
 4               Total writes for this command:  0 
 5              
 6               Execution Time  0 .
 7               SQL Server cpu time:  0  ms.  SQL Server elapsed time:  0  ms.
 8              
 9               QUERY PLAN FOR STATEMENT  1  (at line  1 ).
 10            Executed in parallel by coordinating process and  7  worker processes.
 11           
 12           
 13                STEP  1 
 14                    The type of query is SELECT.
 15                    Evaluate Ungrouped COUNT AGGREGATE.
 16                    Executed in parallel by coordinating process and  7  worker processes.
 17           
 18                    FROM TABLE
 19                        BusEntRoleProd
 20                        BERP
 21                    Nested iteration.
 22                    Table Scan.
 23                    Forward scan.
 24                    Positioning at start of table.
 25                    Executed in parallel with a  7 -way hash scan.
 26                    Using I/O Size  16  Kbytes for data pages.
 27                    With LRU Buffer Replacement Strategy for data pages.
 28           
 29                    FROM TABLE
 30                        BusinessEntity
 31                        BE
 32                    Nested iteration.
 33                    Using Clustered Index.
 34                    Index : x1
 35                    Forward scan.
 36                    Positioning by key.
 37                    Keys are:
 38                        businessEntityId  ASC
 39                    Using I/O Size  2  Kbytes for data pages.
 40                    With LRU Buffer Replacement Strategy for data pages.
 41           
 42                    Parallel result buffer merge.
 43           
 44                STEP  2 
 45                    The type of query is SELECT.
 46                    Executed by coordinating process.
 47           
 48            Parse and Compile Time  0 .
 49            SQL Server cpu time:  0  ms.
 1                56500 
 1               Table: BusEntRoleProd scan count  7 , logical reads: (regular= 49238  apf= 0  total= 49238 ), physical reads: (regular= 0  apf= 0  total= 0 ), apf IOs used= 0 
 2               Table: BusinessEntity scan count  56575 , logical reads: (regular= 171548  apf= 0  total= 171548 ), physical reads: (regular= 0  apf= 0  total= 0 ), apf IOs used= 0 
 3               Total writes for this command:  0 
 4              
 5               Execution Time  0 .
 6               SQL Server cpu time:  0  ms.  SQL Server elapsed time:  380  ms.