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)
*/
