Search code examples
sql-serversqlperformance

High logical reads for a simple query

I have a very simple query like

select col1,col2 from table A where pk_col =1

where pk_col is a primary key.

When I run this query with statistics IO and time ON,I get below

(1 row affected)
Table 'A'. Scan count 1, logical reads 2948, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

1)Why are logical reads so high even though only record is being retrieved ?

Solution

  • Since pk_col is varchar SQL Server needs to convert pk_col to int because int has higher precedence:

    Data type precedence (Transact-SQL)

    So the query is equivalent to:

    SELECT col1,col2 FROM table A 
where CONVERT(int, pk_col) =1

    And enclosing columns into functions or expressions prevents SQL Server from using indexes. Therefore SQL Server has to scan the entire table. Hence the high number of logical reads