Skip to end of metadata
Go to start of metadata

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

  • No labels