Full-text query plans and stats in Denali

I've been looking at full-text query plans in Denali lately, and looking at the SQL query plan for the full-text part, there isn't much to see (modulo docid lookup or lack of it, see a couple of posts ago). Everything seemed to be hidden behind the TableValuedFunction iterator for FulltextMatch function, and although there's the usual iterator info on this one, there's nothing about how they do the full-text query. Not even in the parameters passed to the TVF. So what does FulltextMatch do?

Notice I said "seemed" to be hidden. Just ran across some new extended events for FTS that are quite interesting.These are fulltext_exec_query_stats and fulltext_query_recompile. Note: I don't find these events pre-Denali. Sounds promising. Started up an Extended Event session with these, set on "Watch Live Data" (did I already say a few times how much I liked the new "extended event trace" GUI?). Along with the action for sql_text.

For a full-text query that had a predicate that read like this: "WHERE CONTAINS(file_stream, 'NEAR(("data", "SQL"), 5, FALSE)');", fulltext_exec_query_stats retrieved this lovely chunk of XML. Hope it doesn't get munged in the poster or your reader.

<Root FragmentSelectionTimestamp="0x000001ec34" QueryHandle="1241413008" IsParallel="false">
 <Scalar Name="ContainsTableSSERankForNear" InclusiveTime="0ms" RowCount="92">
  <AND Name="AND" InclusiveTime="0ms" RowCount="92">
   <Filter Name="SingleFragmentDocidFilter" InclusiveTime="0ms" RowCount="124" FilterTableStrategy="Scan" SetRangeCalls="2">
    <Filter Name="SingleFragmentSeekFilter" InclusiveTime="0ms" RowCount="124">
     <Fragment Keyword="SQL" InclusiveTime="0ms" RowCount="124">
      <PhysicalRead InclusiveTime="0ms" RowCount="19" Scans="1" LogicalReads="6" PhysicalReads="0" ReadAheads="0" LobLogicalReads="0" LobPhysicalReads="0" LobReadAheads="0" />
      <Decompression InclusiveTime="0ms" RowCount="124" />
     </Fragment>
    </Filter>
  </Filter>
  <Filter Name="SingleFragmentDocidFilter" InclusiveTime="0ms" RowCount="124" FilterTableStrategy="Scan" SetRangeCalls="2">
   <Filter Name="SingleFragmentSeekFilter" InclusiveTime="0ms" RowCount="124">
    <Fragment Keyword="data" InclusiveTime="0ms" RowCount="124">
     <PhysicalRead InclusiveTime="0ms" RowCount="18" Scans="1" LogicalReads="6" PhysicalReads="0" ReadAheads="0" LobLogicalReads="0" LobPhysicalReads="0" LobReadAheads="0" />
     <Decompression InclusiveTime="0ms" RowCount="124" />
    </Fragment>
   </Filter>
   </Filter>
  </AND>
 </Scalar>
</Root>

VERY cool. So, although there's no magic decoder ring for this one (yet, I'm working on it), you can deduce (OK, guess) the following just by reading:
   FTS in Denali has the ability to execute this function in Parallel (although I haven't made it do this yet)
   The CONTAINS predicate appears to be implemented using CONTAINSTABLE to begin with. They seem to be using a "rank for NEAR" rather than the CONTAINSTABLE rank.
   I think for "SSE" may have something to do with FTS using streaming SIMD Extensions
   The FTS portion of the query has its own cache (haven't hit the full_text_recompile event yet).
   Each FTS "Filter iterator" (for lack of a better term) has its own strategy and records its own I/O info, timing, and row count.
   BTW, There are a lot more topmost-level iterators than in this relatively simple query. There's one (sometimes multiple) for FREETEXT, TOP N, etc.

Nice. If you're tired of wondering, "what are those FTS predicates and TVFs doing", check out this mechanism for looking into it.

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.