This is a post for T-SQL Tuesday #43 hosted by my good friend Rob Farley. The topic this month is Plan Operators. I haven’t taken part in T-SQL Tuesday before, but I do like to write about execution plans, so this seemed like a good time to start. This post is in two parts. The first part is primarily an excuse to use a pretty bad play on words in the title of this blog post (if you’re too young to know what a telephone operator or a switchboard is, I hate you). The second part of the post looks at an invisible query plan operator (so to speak). 1. My Switch Is Bored Allow me to present the rare and interesting execution plan operator, Switch: Books Online has this to say about Switch: Following that description, I had a go at producing a Fast Forward Cursor plan that used the TOP operator, but had no luck. That may be due to my lack of skill with cursors, I’m not too sure. The only application of Switch in SQL Server 2012 that I am familiar with requires a local partitioned view: CREATE TABLE dbo.T1 (c1 int NOT NULL CHECK (c1 BETWEEN 00 AND 24));
CREATE TABLE dbo.T2 (c1 int NOT NULL CHECK (c1 BETWEEN 25 AND 49));
CREATE TABLE dbo.T3 (c1 int NOT NULL CHECK (c1 BETWEEN 50 AND 74));
CREATE TABLE dbo.T4 (c1 int NOT NULL CHECK (c1 BETWEEN 75 AND 99));
GO
CREATE VIEW V1 AS
SELECT c1 FROM dbo.T1
UNION ALL
SELECT c1 FROM dbo.T2
UNION ALL
SELECT c1 FROM dbo.T3
UNION ALL
SELECT c1 FROM dbo.T4;
Not only that, but it needs an updatable local partitioned view. We’ll need some primary keys to meet that requirement:
ALTER TABLE dbo.T1
ADD CONSTRAINT PK_T1
PRIMARY KEY (c1);
ALTER TABLE dbo.T2
ADD CONSTRAINT PK_T2
PRIMARY KEY (c1);
ALTER TABLE dbo.T3
ADD CONSTRAINT PK_T3
PRIMARY KEY (c1);
ALTER TABLE dbo.T4
ADD CONSTRAINT PK_T4
PRIMARY KEY (c1);
We also need an INSERT statement that references the view. Even more specifically, to see a Switch operator, we need to perform a single-row insert (multi-row inserts use a different plan shape):
INSERT dbo.V1 (c1)
VALUES (1);
And now…the execution plan:
The Constant Scan manufactures a single row with no columns. The Compute Scalar works out which partition of the view the new value should go in. The Assert checks that the computed partition number is not null (if it is, an error is returned). The Nested Loops Join executes exactly once, with the partition id as an outer reference (correlated parameter).
The Switch operator checks the value of the parameter and executes the corresponding input only. If the partition id is 0, the uppermost Clustered Index Insert is executed, adding a row to table T1. If the partition id is 1, the next lower Clustered Index Insert is executed, adding a row to table T2…and so on.
In case you were wondering, here’s a query and execution plan for a multi-row insert to the view:
INSERT dbo.V1 (c1)
VALUES (1), (2);
Yuck! An Eager Table Spool and four Filters! I prefer the Switch plan.
My guess is that almost all the old strategies that used a Switch operator have been replaced over time, using things like a regular Concatenation Union All combined with Start-Up Filters on its inputs. Other new (relative to the Switch operator) features like table partitioning have specific execution plan support that doesn’t need the Switch operator either. This feels like a bit of a shame, but perhaps it is just nostalgia on my part, it’s hard to know.
Please do let me know if you encounter a query that can still use the Switch operator in 2012 – it must be very bored if this is the only possible modern usage!
2. Invisible Plan Operators
The second part of this post uses an example based on a question Dave Ballantyne asked using the SQL Sentry Plan Explorer plan upload facility. If you haven’t tried that yet, make sure you’re on the latest version of the (free) Plan Explorer software, and then click the Post to SQLPerformance.com button. That will create a site question with the query plan attached (which can be anonymized if the plan contains sensitive information). Aaron Bertrand and I keep a close eye on questions there, so if you have ever wanted to ask a query plan question of either of us, that’s a good way to do it.
The problem
The issue I want to talk about revolves around a query issued against a calendar table. The script below creates a simplified version and adds 100 years of per-day information to it:
USE tempdb;
GO
CREATE TABLE dbo.Calendar
(
dt date NOT NULL,
isWeekday bit NOT NULL,
theYear smallint NOT NULL,
CONSTRAINT PK__dbo_Calendar_dt
PRIMARY KEY CLUSTERED (dt)
);
GO
-- Monday is the first day of the week for me
SET DATEFIRST 1;
-- Add 100 years of data
INSERT dbo.Calendar WITH (TABLOCKX)
(dt, isWeekday, theYear)
SELECT
CA.dt,
isWeekday =
CASE
WHEN DATEPART(WEEKDAY, CA.dt) IN (6, 7)
THEN 0
ELSE 1
END,
theYear = YEAR(CA.dt)
FROM Sandpit.dbo.Numbers AS N
CROSS APPLY
(
VALUES (DATEADD(DAY, N.n - 1, CONVERT(date, '01 Jan 2000', 113)))
) AS CA (dt)
WHERE
N.n BETWEEN 1 AND 36525;
The following query counts the number of weekend days in 2013:
SELECT
Days = COUNT_BIG(*)
FROM dbo.Calendar AS C
WHERE
theYear = 2013
AND isWeekday = 0;
It returns the correct result (104) using the following execution plan:
The query optimizer has managed to estimate the number of rows returned from the table exactly, based purely on the default statistics created separately on the two columns referenced in the query’s WHERE clause. (Well, almost exactly, the unrounded estimate is 104.289 rows.)
There is already an invisible operator in this query plan – a Filter operator used to apply the WHERE clause predicates. We can see it by re-running the query with the enormously useful (but undocumented) trace flag 9130 enabled:
Now we can see the full picture. The whole table is scanned, returning all 36,525 rows, before the Filter narrows that down to just the 104 we want. Without the trace flag, the Filter is incorporated in the Clustered Index Scan as a residual predicate. It is a little bit more efficient than using a separate operator, but residual predicates are still something you will want to avoid where possible. The estimates are still spot on though:
Anyway, looking to improve the performance of this query, Dave added the following filtered index to the Calendar table:
CREATE NONCLUSTERED INDEX Weekends
ON dbo.Calendar(theYear)
WHERE isWeekday = 0;
The original query now produces a much more efficient plan:
Unfortunately, the estimated number of rows produced by the seek is now wrong (365 instead of 104):
What’s going on? The estimate was spot on before we added the index!
Explanation
You might want to grab a coffee for this bit.
Using another trace flag or two (8606 and 8612) we can see that the cardinality estimates were exactly right initially:
The highlighted information shows the initial cardinality estimates for the base table (36,525 rows), the result of applying the two relational selects in our WHERE clause (104 rows), and after performing the COUNT_BIG(*) group by aggregate (1 row). All of these are correct, but that was before cost-based optimization got involved :)
Cost-based optimization
When cost-based optimization starts up, the logical tree above is copied into a structure (the ‘memo’) that has one group per logical operation (roughly speaking). The logical read of the base table (LogOp_Get) ends up in group 7; the two predicates (LogOp_Select) end up in group 8 (with the details of the selections in subgroups 0-6). These two groups still have the correct cardinalities as trace flag 8608 output (initial memo contents) shows:
During cost-based optimization, a rule called SelToIdxStrategy runs on group 8. It’s job is to match logical selections to indexable expressions (SARGs). It successfully matches the selections (theYear = 2013, is Weekday = 0) to the filtered index, and writes a new alternative into the memo structure. The new alternative is entered into group 8 as option 1 (option 0 was the original LogOp_Select):
The new alternative is to do nothing (PhyOp_NOP = no operation), but to instead follow the new logical instructions listed below the NOP.
The LogOp_GetIdx (full read of an index) goes into group 21, and the LogOp_SelectIdx (selection on an index) is placed in group 22, operating on the result of group 21. The definition of the comparison ‘the Year = 2013’ (ScaOp_Comp downwards) was already present in the memo starting at group 2, so no new memo groups are created for that.
New Cardinality Estimates
The new memo groups require two new cardinality estimates to be derived. First, LogOp_Idx (full read of the index) gets a predicted cardinality of 10,436. This number comes from the filtered index statistics:
DBCC SHOW_STATISTICS (Calendar, Weekends)
WITH STAT_HEADER;
The second new cardinality derivation is for the LogOp_SelectIdx applying the predicate (theYear = 2013). To get a number for this, the cardinality estimator uses statistics for the column ‘theYear’, producing an estimate of 365 rows (there are 365 days in 2013!):
DBCC SHOW_STATISTICS (Calendar, theYear)
WITH HISTOGRAM;
This is where the mistake happens. Cardinality estimation should have used the filtered index statistics here, to get an estimate of 104 rows:
DBCC SHOW_STATISTICS (Calendar, Weekends)
WITH HISTOGRAM;
Unfortunately, the logic has lost sight of the link between the read of the filtered index (LogOp_GetIdx) in group 22, and the selection on that index (LogOp_SelectIdx) that it is deriving a cardinality estimate for, in group 21. The correct cardinality estimate (104 rows) is still present in the memo, attached to group 8, but that group now has a PhyOp_NOP implementation.
Skipping over the rest of cost-based optimization (in a belated attempt at brevity) we can see the optimizer’s final output using trace flag 8607:
This output shows the (incorrect, but understandable) 365 row estimate for the index range operation, and the correct 104 estimate still attached to its PhyOp_NOP. This tree still has to go through a few post-optimizer rewrites and ‘copy out’ from the memo structure into a tree suitable for the execution engine. One step in this process removes PhyOp_NOP, discarding its 104-row cardinality estimate as it does so.
To finish this section on a more positive note, consider what happens if we add an OVER clause to the query aggregate. This isn’t intended to be a ‘fix’ of any sort, I just want to show you that the 104 estimate can survive and be used if later cardinality estimation needs it:
SELECT
Days = COUNT_BIG(*) OVER ()
FROM dbo.Calendar AS C
WHERE
theYear = 2013
AND isWeekday = 0;
The estimated execution plan is:
Note the 365 estimate at the Index Seek, but the 104 lives again at the Segment! We can imagine the lost predicate ‘isWeekday = 0’ as sitting between the seek and the segment in an invisible Filter operator that drops the estimate from 365 to 104.
Even though the NOP group is removed after optimization (so we don’t see it in the execution plan) bear in mind that all cost-based choices were made with the 104-row memo group present, so although things look a bit odd, it shouldn’t affect the optimizer’s plan selection. I should also mention that we can work around the estimation issue by including the index’s filtering columns in the index key:
CREATE NONCLUSTERED INDEX Weekends
ON dbo.Calendar(theYear, isWeekday)
WHERE isWeekday = 0
WITH (DROP_EXISTING = ON);
There are some downsides to doing this, including that changes to the isWeekday column may now require Halloween Protection, but that is unlikely to be a big problem for a static calendar table ;) With the updated index in place, the original query produces an execution plan with the correct cardinality estimation showing at the Index Seek:
That’s all for today, remember to let me know about any Switch plans you come across on a modern instance of SQL Server!
Finally, here are some other posts of mine that cover other plan operators:
Segment and Sequence Project
Common Subexpression Spools
Why Plan Operators Run Backwards
Row Goals and the Top Operator
Hash Match Flow Distinct
Top N Sort
Index Spools and Page Splits
Singleton and Range Seeks
Bitmaps
Hash Join Performance
Compute Scalar
© 2013 Paul White – All Rights Reserved
Twitter: @SQL_Kiwi
