What is that Garbage in my Execution Plan? (Dear SQL DBA Episode 27)

Today I was working on some code samples for a user question, and I hit a weird roadblock.

There was a bunch of garbage in my execution plan that I couldn’t explain. And by ‘garbage’, I mean a nested loop to a whole branch of code that I hadn’t asked SQL Server to run – and a warning about an implicit conversion possibly causing problems with the quality of my execution plan.

It took me a while to figure out the issue, and along the way I asked the following questions:

Am I really querying a table, or am I accidentally querying a view? (It’s a table! I checked at least three times.)

Is there some weird computed column in the table that I don’t know about? (Nope, nothing involved was a computed column, I checked that twice.)

Am I awake right now? (Pinched myself, appeared awake. I have had weird dreams about SQL Server before, though.)

Do I actually know anything about SQL Server? (Just about everyone has imposter syndrome sometimes.)

Having gone through this checklist, I decided that I was awake, and that I could figure things out by looking through the execution plan carefully.

Sure enough, calming down and stepping through the plan did the trick. Just like it always has.

Watch the 18 minute video to find out the mysteries in this execution plan, or scroll on down to read about what I discovered.

Here’s what the weirdness looked like

I was using the WideWorldImporters sample database from Microsoft. And I was running a stupid simple query.

Stupid simple like this:

SELECT 
    CustomerName
FROM Sales.Customers;
GO

I’m selecting one column. There are no predicates at all. Sales.Customers is a normal old rowstore table, and CustomerName is nvarchar(100).

For a query like this, I’d expect a very simple plan: an index seek or scan operator to pull back the data, and a SELECT operator.

Instead, I saw an execution plan with 20 nodes, and a big old warning on the SELECT operator.

Here’s what it looked like in SQL Sentry Plan Explorer:

If you’d like to play along in a more interactive version, here’s the query over at Paste the Plan. (The website view doesn’t show al the operator properties I’m going to talk about here, but you can grab the XML and use it in SSMS or Plan Explorer to see the detail if you’d like.)

Hovering over the warning on the SELECT operator, here’s the warning (this is the tooltip from SSMS):

Weird, it’s warning about the SalesTerritory column. I didn’t ask for it to do anything at all with SalesTerritory. Why is it doing a type conversion on that column?

Let’s start at the top right of the query

When I’m in doubt about an execution plan, I like to just start at the top right of the query plan and work my way through. I think of that top right operator as the “driver”.

In this case, it’s a clustered index scan of Sales.Customers. That makes sense: I asked for all the customer names. Hovering over that operator, though, there is something funny. When I look at the ‘output’ columns, it is outputting not only CustomerName, but also DeliveryCityID!

So what’s it doing with DeliveryCityID?

Moving one step to the left in the plan, there’s a nested loop operator. Hovering over that operator, it says that it outputs the CustomerName column to the select operator. (Good, because that’s what we asked for!)

It also says that the Outer References for the nested loop are based on DeliveryCityID. OK, so it’s pulling back that column because it needs it to run the nested loop. We still don’t know why, but if we hunt around in that branch of the plan, maybe there’ll be a clue.

At this point, I started hovering over operators in that branch of the plan

As in life, when you’re lost in an execution plan, move around slowly and carefully, observe your surroundings, and look for your mom. I mean, look for inspiration.

I could see that the query was pulling from the Cities and StateProvinces tables. And there were a bunch of filter operators as well.

Here’s what the filters are doing:

• is_rolemember(N’db_owner')<>(0)
• is_rolemember([Expr1011]+N' Sales')<>(0)
• [Expr1012]=session_context(N’SalesTerritory')
• original_login()=N’Website'

This is security garbage! Err… a security feature!

Aha! This is a definite clue. Some sort of security wizardry has been applied to this table, so that when I query it, a bunch of junk gets tacked onto my query.

I have no shame in admitting that I couldn’t remember at all what feature this was and how it works. A lot of security features were added in SQL Server 2016, and the whole point of a sample database like this to kick the tires of the features.

I did a little remembering, and a little searching, and figured out that this is the Row Level Security feature (RLS) in SQL Server 2016.

Row Level Security (RLS) adds predicates to your query execution plans

Here’s how row level security works. You create a table valued function that can be “inlined” (meaning merged) into your query execution plan. That function determines who can see the data.

Then you create a Security Policy for Row Level Security which defines when the table valued function will be applied to queries against a table.

The whole point of Row Level Security is, actually, that it adds these predicates to your execution plans.

How do I tell if Row Level Security changed my plan?

There’s a really easy way to tell if your plan was modified by RLS, I just didn’t know to look for it.

Click on the ‘SELECT’ operator in the plan and look down in the properties pane. If you see ‘SecurityPolicyApplied’ = True, then parts of your execution plan may have come from a table valued function that Row Level Security added in.

Should you use row level security?

Wellll…. maybe. If you’re interested, read up on the possible loopholes in RLS as it stands now, and consider if those would impact you or not. Aaron Bertrand has a great article to get you started: read SQL Server 2016 Row Level Security Limitations, Performance and Troubleshooting.

Everyone feels dumb looking at Execution Plans sometimes

I look at plans a lot, and still, they had me questioning my sanity today. When I first started doing performance tuning in SQL Server, I understood so little about plans that I gave up pretty easily.

I’m really happy that I kept going, though. Because as confusing as they are, most of the time the answers you’re looking for are right there in the plan. Somewhere.

Just keep swimming.