Query Processor Engine in SQL Server

Query Processor Engine in SQL Server

By : Kasim Wirama, MCDBA

Are you interested with SQL Server engine internal? If yes, I would like to show you what you should know about what query processor engine is and how it works.

Query processor engine is one of core components in SQL Server. Together with SQL Server storage engine, it processes query request. If I look it deeper into query processor, it consists of 2 sub components, i.e.: query optimizer and query execution engine. Query optimizer provides, as possible, the cheapest and the most efficient query plan. Query execution engine takes query plan and process it, including data read and writing to tables and indexes in coordination with SQL Server storage engine. Besides the task, query execution engine performs join and aggregation tasks.

What does query plan look like? If you see graphical representation of query plan in SSMS (by clicking “Include Actual Execution Plan” or “Display Estimated Execution Plan”), it consists of a set of icons. Those icons are called operators. Operators are independent among others. Each of them has specialized tasks and has no knowledge about other operators’ task. It has one output and none or more children.

Operators are classified into 3 categories; memory consuming operator, unblock and blocking operator and dynamic cursor support operator. A memory consuming operator usually stored result in temporary work table. The examples of memory consuming operator are sort, hash join and hash aggregate. Unblocking operator has capability to receive input and give output at the same time. Unblocking operator is not memory consuming operator and it is optimal in busy OLTP environment, for example EXISTS and TOP sub clauses are recommended in scenario that user need to check whether a criteria should be met within a query. Blocking operator will receive all input rows and then produces output. More often than not, blocking operator is also memory consuming operator. But there are blocking operators that do not memory consuming operator, for example COUNT aggregate function. The criteria of operator that supporting dynamic cursor is that it can save and restore its state, be able to scan forward and backward, have only one input row and non-blocking nature. GROUP BY sub clause doesn’t provide support for dynamic cursor.