I have a friend who’s really into Pokemon. Really into Pokemon. I had no idea there was such a thing as a “shiny” pokemon until he, uh, graciously spent a whole evening lecturing me on their value and capture.
It’s a passion I don’t really get, but have to tolerate, maybe even respect. It turns out I’m the same, just with different monsters. You see, I really like plan operators. I like knowing their behavior, I like learning their natural habitat, and I love hunting for exotics. If I could find a way to stuff and mount Flow Distinct on my wall, I probably would.
There a handful of ways to see what operators roam your production environment. If you’re lucky enough to be working with 2016 or later
(I’ve seen the stats though, condolences for those of you still on 2005), you can peek into Query Store. Actually, with how long the query takes to run, it might be more of an expedition than a peek. Caveat executor.
Here’s the script – again, mind that this can be an expensive query.
DROP TABLE IF EXISTS #QS_xml
GO
CREATE TABLE #QS_xml (
query_plan XML
)
INSERT #QS_xml
SELECT TRY_CONVERT(XML,query_plan) query_plan
FROM sys.query_store_plan qsp
GO
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,CTE AS (
SELECT x.value('./@PhysicalOp','varchar(200)') AS PhysicalOp, x.value('./@LogicalOp','varchar(200)') AS LogicalOp
FROM #QS_xml qsx
CROSS APPLY qsx.query_plan.nodes('//RelOp') c(x)
)
SELECT CTE.PhysicalOp, CTE.LogicalOp, COUNT(*) AS [Count]
FROM CTE
GROUP BY PhysicalOp, CTE.LogicalOp
ORDER BY [Count] DESC
The most common operator for me, at a whopping quarter-million occurrences, is the Clustered Index Seek. That’s probably a good thing, since I do OLTP. I still see plenty of scans, because developers, and a decent range of not-rare-but-not-common operators, like the Table Spool (hmm, I should probably look into that query…).
Then there’s the esoteric bottom of the list, where operators are only seen a couple of places instead of a quarter-million. One of these was new to me: Inserted Scan, which comes from the Inserted pseudo-table in a trigger. I have a couple oddities like Parameter Table Scan and Hash Match for a Full Outer Join.
Of course, hiding down there are three shiny Flow Distincts. Time for me to hunt down a repro!