Physical and Logical Reads in SQL Server

In this article we learn about Query Plan, Physical and Logical Operators, Physical and Logical Reads.

QUERY PLAN

• Execute SQL statement is a three steps process.

QUERY PLAN

PARSE:

• When we execute the SQL statement firstly SQL engine checks the syntax like compilation error etc.
• After that, It generates the Query Processor Tree. And this query processor fetches to the optimizer.

OPTIMIZE:

• The optimizer takes the data and analyzes the data statistics. It analyzes how many rows on the table? Is there unique key on the table? etc.
• Thereafter depending on data statistics and query processing tree, it generates estimate plan.

Execute:

• Finally, this estimated plan fetches the SQL engine for execution. After execution, the actual plan is prepared.

LOGICAL AND PHYSICAL OPERATORS

• The logical operator has a conceptual plan whereas the Physical operator has actual logic.

Display Estimated Execution Plan:

• The estimated plan will provide you how your query will execute on the server without actually running it.
• This will gives you an idea of whether you need to tune to create a better plan or the estimated plan looks ok.

Include Actual Execution Plan:

• The actual plan shows what actually happened when the server executed the query.

PHYSICAL AND LOGICAL READ

• SET STATISTICS IO ON statement displays the Physical and Logical reads. It displays statistical information generated by SQL statement.

• When data read on actual database then it called physical read. When data read on cache then it called logical read.
• The SQL Server allocates some kind of memory for SQL cache. When we fire SQL statements, again and again, those records in SQL cache.

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -