Execution Plan Question

Question

Execution Plan Question

mharding 65939

SSC Eights!

Points: 896
More actions
May 8, 2015 at 4:06 pm

#317308

I am executed a query in SQL Server Management Studio and captured the actual execution plan and I noticed that an index scan on a non-clustered index occurred. When I hovered with my mouse over the index, a window opened and showed me the predicats, objects and output list. And I am trying to make some sense out of it. Are the predicats the columns in the WHERE clause of a SELECT statement? I understand the object. In this case it is the non-clustered index. But what is the output list? Are these the columns that are selected in the SELECT statement?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Are the [predicates] the columns in the WHERE clause of a SELECT statement?

There is no WHERE clause in a SELECT statement. There is a WHERE clause in a SQL Query. I'm not trying to be snarky here - it's just worth pointing out that a SQL Query is broken up by phases (referred to as Logical Query Processing Phases). WHERE and SELECT are seperate phases of a SQL query.

That said, the answer to your question is yes. The predicate is a filter applied to that object (clustered index, non-clustered index or "table") based on one of it's columns. E.g. Where sales_amt > 10.

It's worth noting that if the WHERE clause was something like, WHERE @variable = "All" and no columns are involved you would not see that when looking at the index scan. The optimizer would (usually) create a Filter.

But what is the output list? Are these the columns that are selected in the SELECT statement?

Short answer: Usually but not always. Sometimes the query optimizer needs those output columns for other things and they never appear in the final output. Sometimes there isn't an output list. For example: SELECT NULL FROM <sometable> will show no output list (and no predicates for that matter.)

This stuff is so hard to articulate. It makes you appreciate authors that can. Take a look at SQL Server Execution Plans, Second Edition

By Grant Fritchey. It's a must-read. You can even download it in PDF format for free.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mharding 65939 SSC Eights! Points: 896 More actions · Answer 2

Thank you for responding so quickly. Every bit of explanation helps. That will make it easier to build indexes to change the index scan into an index seek. Thanks again.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

You'll also see the predicates used against the indexes come from the ON clause or the HAVING clause in queries. Don't be surprised if it's not just your WHERE clause. Also, the OUTPUT, depending on the operator, can include additional columns that are part of the internal processing of the plans.

A good habit to get into is to right click on the operators and use the properties window instead of the tooltips. The tooltips are frequently incomplete and getting them to pop up can be a pain. With the properties window opened and pinned, you can just click on an operator to see it's properties.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Alan.B (5/8/2015)
There is no WHERE clause in a SELECT statement. There is a WHERE clause in a SQL Query. I'm not trying to be snarky here - it's just worth pointing out that a SQL Query is broken up by phases (referred to as Logical Query Processing Phases). WHERE and SELECT are seperate phases of a SQL query.

Also not trying to be snarky but there is a WHERE clause in the SELECT [font="Arial Black"]statement[/font]... just not in the SELECT [font="Arial Black"]clause[/font]. 😛

This link identifies what clauses a SELECT [font="Arial Black"]statement [/font]is made up of. If you scroll down to the "Remarks" section, you find that one of the clauses is the SELECT [font="Arial Black"]CLAUSE[/font].

https://technet.microsoft.com/en-us/library/ms189499(v=sql.105).aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)