ASE Traceflag 200 prints the query tree before optimization.
1> dbcc traceon(3604,200)
2> go
1> select name from sysusers
2> go
TREE FOR STATEMENT 1 BEFORE OPTIMIZATION: (Pss->pmainroot: 0x100051f7000)
SEQ (!:0x100051f7488) (L: 0x100051f7470 R: 0x0) (TokClass: MARK) (seqline:1 seqaction:1 seqstmttype:193 (SELECT) seqgoto:0x0 seqmpcr:0x100051f7750 seqautop:0x0 seqsetop:0x0 seqstmt:0x0 seqoffset:0 seqstat: (0x0800 (POST_10)) seqcu
rsread: 0x0 (0x0000) seqisolation: 0 (NOT_SPECIFIED))
seqstat3: (0x00)
seqstat2: (0x0000) seqstat4: (0x2000 (CANT_SQLREP)) seqstat5: (0x0000) seqomnistat: (0x0000) seqbmwktab: ()
CMD (!:0x100051f7470) (L: 0x100051f7000 R: 0x0) (TokClass: MARK)
ROOT
/
RESDOM
name
\
VAR
(0,4)
name
ROOT (L:0x100051f7000) (L: 0x100051f7598 R: 0x0) (TokClass: MARK) (querytype:SELECT resvar:-1 workvar:-1 varct:1 workct:0 subqct:0 toplimit:0 workrg:0x100051f7280 rootstat:(0x00000001 (USER)) root2stat:(0x00000000) root3stat:(0
x00000000) root4stat:(0x00000000) root5stat:(0x00000040 (OJ_PROCESSED), 0x00000020 (AGG_PROCESSED)) root6stat:(0x00000000) root7stat:(0x00000000) joindata:0x0 procnum:0 forupdlst: 0x0 )
RANGE: (0x100051f7620) (varno: 0 tablename:sysusers rgcorr: level: 0 subq: 0 rgcol: 0x100051f7520 rgjoin: 0x0 rgor: 0x0 rgviewp: 0x0 rgindname: 0x0 rgptnname: 0x0 rgtrigdata: 0x0 rgnondiskres=0x10005b61138 rgparent=0x100051f7620 rgconst
rl=0x0 rglastconstr=0x0
rgencrkey=0x0 rglastencrkey=0x0
rgdes=0x1000282c850 rgndreop=0x0 rgtextoff=0x0
rgdefaultcol=0x0, rgtabstatp=0x0
Updated colids=(0x0 ( ))
rgdepends: ( )
rgndrstat:(0x00000020 (VARLEN_PART))
rgndrstat2:(0x0000)
rgdcacheid=0 rgselfjoin=-1 rgnumpart=0
rgmaxpartsize=0 rgsuffclass=0 rgundermergeop=0
rgptnids=0x0 (num=0) rgadmlob=0x0
rgoldvarno=0 rgWorktabVtup=0 rgCache=0
rgcursor: -1 rgdbid: 1 rgtabid: 10 rgprefetch: 0 rgschema2: 3 rgschema: 0 rglock: 0 rgcoldel: 0: rgcrtime: 10028910 rgstat:(0x00000800 (MADEPROT)) rgstat2:(0x0000) rgstat3:(0x0004 (LOCK_DATAROWS)) rgstat4:(0x00) rgstat5:(0x01 (EXPLI
CIT NAME)) rgstat6:(0x00)
SUBQUERY LIST: (at 0x100051f73f0) (subqct=0) is empty.
RESDOM (L:0x100051f7598) (TokClass: MARK) (resname:name colid:0 coltype:0x27 (VARCHAR) (hierarchy:7) colen:30 colstat: 0x0 (0x00) cstatus2: (0x00000000) cstatus3: (0x0000) colprec:0 colscale:0 coloff:0 resutypeid:18 (sysname)
resorder:0 resstat2: (0x0000) resstat3: (0x0000) resstat4: (0x0000) resstat5: (0x0000) resstat6: (0x00) resstat7: (0x00) resstat8: (0x00000000) resomnistat: (0x0000) resvarno:0 resnamelist: 0x100051f77a0
[names=0x100051f77a0 name='name' ntype=(0x02 (COLNAME1)) namenext=0x100051f77c0]
[names=0x100051f77c0 name='sysusers' ntype=(0x01 (TABNAME)) namenext=0x0] )
VAR (R:0x100051f7520) (TokClass: GVAR) (varname:name varno:0 colid:4 colstat: 0x0 (0x00) cstatus2: (0x00000000) cstatus3: (0x0000) coltype:0x27 (VARCHAR) (hierarchy:7) colen:30 colprec:0 colscale:0 coloff:-1 varutypeid:1
8 varusecnt:1 varjtcid:-1 varstat:0x1 ((0x0001 (TL))) varstat2:0x0 ((0x00000000)) varlevel:0 varsubq:0)
name
------------------------------
dbo
dtm_tm_role
guest
ha_role
js_admin_role
js_client_role
js_user_role
keycustodian_role
messaging_role
mon_role
navigator_role
oper_role
probe
public
replication_role
sa_role
sa_serverprivs_role
sso_role