So I like kung-fu movies. I don’t care how bad the acting is, as long as there is some master/relative/friend who has been killed who needs avenged. Often good movies are made this way.
In software, a good idea often comes out of getting tired of seeing bugs on a sore subject, often not even your fault. So, in a fit of rage, I locked myself in my office for a few weeks and came up with the streaming table-valued function, since used as the proverbial duct-tape within the QP for all sorts of unforseen features. However, it has a “walking on rice paper without leaving footprints” vibe, so I will tell you a bit about this guy ;).
(I did the QO part for this operator and a chunk of the architecture for it – there were others who worked on it too, however).
So a query operator takes rows in the bottom and spits rows out the top (yes, none of the exposed query plan graphs are drawn this way, but that’s how people talk about it ;). Some operators take more than one input – most of the ones with 2 inputs are called “joins”. Some can take an arbitrary number of inputs (usually called UNIONs). Some take parameters (Compute Scalar). most don’t take parameters, however.
After you’ve done the 10 or so common operators in a query processor, there’s a lot of specialty problems that don’t really fit well into those operators. So people end up trying all sorts of crazy paths to get the data they need out to customers. Exuberence can often cause a few bugs, however, so eventually something needed to be done.
So, the “Streaming Table Valued Function” (STVF) is a relational operator (takes in rows and passes out rows). In this case, it takes in some number of scalar parameters (like any good function) and spits out zero or more rows. The beauty of this operator is that anyone can program anything to get returned, as long as they return back rows – it’s like a user-defined operator for internal folks to write features for customers. Amazingly, there are a lot of these, and you can see them in Dynamic Management Views (amongst others).
So, when you are looking at the plans in the plan cache or some other DMV, you are using this nifty operator:
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1
ORDER BY usecounts DESC;
(actually you are using it _twice_ in this case, as there are two different sys.dm* references).
|–Nested Loops(Inner Join, OUTER REFERENCES:(SYSDMEXECCACHEDPLANS.[plan_handle]))
|–Sort(ORDER BY:(SYSDMEXECCACHEDPLANS.[usecounts] DESC))
| |–Filter(WHERE:(SYSDMEXECCACHEDPLANS.[usecounts]>(1)))
| |–Table-valued function
|–Table-valued function
In this plan, you can see that rows are pulled from one TVF, are filtered, then sorted, then passed into the second TVF.
The “OUTER REFERENCE” is the key that the loops join is passing something from the inner side to the outer side (remember in the crazy syntax used here, the first row below the Nested Loops is the “inner” and the second line is the “outer”. So, the Sort/Filter/TVF is the “inner” in this case.
These things differ from the traditional TVFs that you could create in SQL Server as a user. Those were actually backed by temporary tables in many cases, and this made it a pretty heavy-weight solution if you just want to pass a few rows into the server. _Streaming_ sounds a lot better than that, you must admit.
Many operators in a QP are streaming – filter is, as is a table scan. However a sort operator is called a “stop-and-go” operator because it generally consumes all of its input rows before producing the first output row. Streaming TVFs would fall into the “not stop-and-go” category.
This operator is used heavily starting in SQL 2005 in lots and lots of places. It is not really something you can directly reference (there is no exposed syntax for this operator), and as such it requires a bit of hunting to track down the various places it gets used.
I’ll leave it as an exercise for you guys to find a few non-DMV places that use STVFs. Any TVF that takes arguments is likely an STVF. So go look at those query plans, folks! First one who tells me a non-DMV/DMF STVF feature gets a star next to their name!
I also know that there is at least one new feature in SQL Server 2008 that uses the STVF.
Happy querying!
Conor Cunningham