First we need to clear buffer cache and procedure cache
before executing the query. Also we need to set on the statistics IO and TIME
on befor running the query each and everytime.
DBCC
DROPCLEANBUFFERS
DBCC
FREEPROCCACHE
SET STATISTICS IO ON
SET STATISTICS TIME ON
select * from dbo.PER_Person P
inner join dbo.PCR_PersonContactRelation
PC
on P.PER_ID = PC.PCR_PER_ID
Result:
SQL Server parse and compile
time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile
time:
CPU time = 0 ms, elapsed time = 64 ms.
(25609 row(s) affected)
Table 'Worktable'. Scan count
0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Table 'PER_Person'. Scan
count 1, logical reads 1139, physical reads 0, read-ahead reads 0, lob logical
reads 0, lob physical reads 0, lob read-ahead reads 0.
Table
'PCR_PersonContactRelation'. Scan count 1, logical reads 290, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 562 ms, elapsed time
= 1642 ms.
SQL Server parse and compile
time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU
time = 0 ms, elapsed time = 0 ms.
Another query which has specified only 1 column:
select P.PER_Country from dbo.PER_Person P
inner join dbo.PCR_PersonContactRelation
PC
on P.PER_ID = PC.PCR_PER_ID
Result:
SQL Server parse and compile
time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile
time:
CPU time = 4 ms, elapsed time = 4 ms.
(25609 row(s) affected)
Table
'PCR_PersonContactRelation'. Scan count 1, logical reads 111, physical reads 5,
read-ahead reads 109, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
Table 'PER_Person'. Scan
count 1, logical reads 1139, physical reads 0, read-ahead reads 0, lob logical
reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time =
140 ms.
SQL Server parse and compile
time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU
time = 0 ms, elapsed time = 0 ms.
Also we can look one more thing in Execution
plan. For first query you can see the Estimated subtree cost 2.93753.
Here you can see the second query has Estimated
subtree cost is 1.10072.
