Memo Structure in SQL Server

Query optimizer generate alternate execution plans through transformation rules for optimal plan, these alternate execution plans are stored in a structure called MEMO for the duration of optimization process.

MEMO provides efficient way to store alternate plan through groups, each group in memo initially contain one entry.

Optimizer initially copy the query tree logical expression in memo structure based on the operator it copies into equivalent groups and optimization process start through transformation.

Transformation process starts from initial logical expression and generate alternative plan, if required it add new logical expression and generate new group and new alternative plan and adding them to equivalent group.

We have some undocumented trace flag which help us to see the memo structure, please do not try these in production environment.

Scenarios/Examples in test environment only:

 CREATE TABLE TempMemo

( id INT )

CREATE TABLE TempMemo1

( id INT )

INSERT INTO TempMemo

SELECT 1

UNION

SELECT 2

INSERT INTO TempMemo1

SELECT 1

UNION

SELECT 3

-- Check the data in tables

SELECT *  FROM   TempMemo1

SELECT *  FROM   TempMemo

-- start trace flag 3604 to return output info to client

DBCC traceon(3604);

-- clear buffer/cache

DBCC freeproccache

DBCC dropcleanbuffers

-- Start Trace flag 8608, which will show Initial memo strucute, marked in color Yellow

SELECT     *

FROM       TempMemo tm  INNER JOIN TempMemo tm1

       ON         tm.id =tm1.id

OPTION(QueryTraceON 8608);

/*

--- Initial Memo Structure ---

Root Group 5: Card=2 (Max=1e+008, Min=0)

   0 LogOp_Join 3 4 2 (Distance = 0)

Group 4: Card=2 (Max=10000, Min=0)

   0 LogOp_Get (Distance = 0)

Group 3: Card=2 (Max=10000, Min=0)

   0 LogOp_Get (Distance = 0)

Group 2:

   0 ScaOp_Comp  0 1 (Distance = 0)

Group 1:

   0 ScaOp_Identifier  (Distance = 0)

Group 0:

   0 ScaOp_Identifier  (Distance = 0)

------------------------------

(2 row(s) affected)

*/

DBCC freeproccache

DBCC dropcleanbuffers

-- Start Trace flag 8615, which will show Final memo strucute, marked in color Green

SELECT     * FROM       TempMemo tm

       INNER JOIN TempMemo tm1 ON         tm.id =tm1.id

OPTION (QueryTraceON 8615);

/*

--- Final Memo Structure ---

Group 7: Card=2 (Max=10000, Min=0)

   1 PhyOp_Range 0 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0032842(Distance = 2)

   0 LogOp_GetIdx (Distance = 1)

Group 6: Card=2 (Max=10000, Min=0)

   1 PhyOp_Range 0 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0032842(Distance = 2)

   0 LogOp_GetIdx (Distance = 1)

Root Group 5: Card=2 (Max=1e+008, Min=0)

   3 PhyOp_HashJoinx_jtInner 4.2 3.2 2.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0243645(Distance = 2)

   1 LogOp_Join 4 3 2 (Distance = 1)

   0 LogOp_Join 3 4 2 (Distance = 0)

Group 4: Card=2 (Max=10000, Min=0)

   6 PhyOp_Sort 4.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0146486(Distance = 0)

   2 PhyOp_Range 0 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0032842(Distance = 1)

   0 LogOp_Get (Distance = 0)

Group 3: Card=2 (Max=10000, Min=0)

   4 PhyOp_Sort 3.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0146486(Distance = 0)

   2 PhyOp_Range 0 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0032842(Distance = 1)

   0 LogOp_Get (Distance = 0)

Group 2:

   0 ScaOp_Comp  0.0 1.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 3(Distance = 0)

Group 1:

   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1(Distance = 0)

Group 0:

   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1(Distance = 0)

----------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(2 row(s) affected)

*/