What is an OPTIMIZED nested loop?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Boyd - 17 Jul 2008 19:28 GMT

i'm looking at a query running on two different servers. It's the same
query, but in the SHOWPLAN and execution plan in QA on one server show the
keyword OPTMIZED.

e.g.:
Nested Loops(Left Outer Join,
OUTER
REFERENCES:([NiagaraEventManager].[dbo].[Persons].[PersonGUID] )
OPTIMIZED)
vs
Nested Loops(Left Outer Join,
OUTER
REFERENCES:([NiagaraEventManager].[dbo].[Persons].[PersonGUID] ))

and
Nested Loops(
Inner Join,
OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventGuests].[PersonGUID])
OPTIMIZED)
vs
Nested Loops(
Inner Join,
OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventGuests].[PersonGUID]))

i can't find any reference in the BOL to "OPTIMIZED", and trying to google
for 'sql server OPTIMIZED' leads one astray.

The closest i can come to "optimized" in SQL Server proper is in reference
to either the LAZY SPOOL or EAGER SPOOL operators, one of which is used to
'optimize rewinds." But this is a NESTED LOOP operator, not a SPOOL.

What is an 'optimized' nested loop?

Reply to this Message

Andrew J. Kelly - 17 Jul 2008 20:52 GMT

Is this 2000? My guess would be that the OPTIMIZED one is a clustered index
or covering index where as the other is not so it does not have to do a
bookmark lookup.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> i'm looking at a query running on two different servers. It's the same
> query, but in the SHOWPLAN and execution plan in QA on one server show the
[quoted text clipped - 30 lines]
>
> What is an 'optimized' nested loop?

Reply to this Message

Ian Boyd - 18 Jul 2008 18:33 GMT

> Is this 2000? My guess would be that the OPTIMIZED one is a clustered
> index or covering index where as the other is not so it does not have to
> do a bookmark lookup.

2005. Everything else about the plans are identical; from the joins they do,
in what order, seeking what indexes, no bookmark lookups. Even the defined
expression names (e.g. "Expr1067") are identical. The only difference is
that in 5 of the 12 Nested Loop joins one server is doing them 'optimized'.

The two queries perform nearly identically, almost identical number of reads
and executes on each step (the only difference is that in some steps in the
plan the optimized one handles 5491 rows, and 5491 executes, while the
unoptimized one handles 5490 rows and 5490 executes).

i was just curious what the undocumented OPTIMIZED is, and perhaps to help
future googlers try to find it when they search for "sql server optimized
nested loop", especially since that google search gives this post as the #2
hit.

i know it's ugly, and wraps wrongly, but here's the two plans:

Optimized Query
============
Compute Scalar(DEFINE:([Expr1067]=CONVERT_IMPLICIT(int,[Expr1068],0)))
|--Stream Aggregate(DEFINE:([Expr1068]=Count(*)))
|--Filter(WHERE:([NiagaraEventManager].[dbo].[EventReservations].[EventReservationGUID]
IS NULL))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventReservationGUID]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventGuests].[PartyMemberPersonGUID]))
|

|--Filter(WHERE:([NiagaraEventManager].[dbo].[EventSeats].[EventSeatGUID] IS

NULL))
| | |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventSessionGuestGUID],
[NiagaraEventManager].[dbo].[EventAttendanceObjects].[EventAttendanceObjectGUID]))
| | |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[Persons].[PersonGUID]) OPTIMIZED)
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventGuests].[PersonGUID])
OPTIMIZED)
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([Uniq1006],
[NiagaraEventManager].[dbo].[EventGuests].[EventGUID]) OPTIMIZED)
| | | | | |--Nested Loops(Inner Join,
OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventGuestGUID])
OPTIMIZED)
| | | | | | |--Nested Loops(Inner
Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventAttendanceObjectGUID]))
| | | | | | | |--Nested
Loops(Inner Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventLocations].[EventSessionGUID]))
| | | | | | | | |--Nested
Loops(Inner Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventLocations].[EventSessionGUID]))
| | | | | | | | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventLocations].[PK_EventLocations]),
SEEK:([NiagaraEventManager].[dbo].[EventLocations].[EventLocationGUID]={guid'C34D07F7-72D9-4B8E-8556-E7D16F34C40C'})
ORDERED FORWARD)
| | | | | | | | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventSessions].[PK_EventSessions]),
SEEK:([NiagaraEventManager].[dbo].[EventSessions].[EventSessionGUID]=[NiagaraEventManager].[dbo].[EventLocations].[EventSessionGUID])
ORDERED FORWARD)
| | | | | | | | |--Clustered
Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventSessionGuests].[IX_EventSessionGuests]),
SEEK:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventSessionGUID]=[NiagaraEventManager].[dbo].[EventLocations].[EventSessionGUID]),
WHERE:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventLocationGUID]={guid'C34D07F7-72D9-4B8E-8556-E7D16F34C40C'}
AND ([NiagaraEventManager].[dbo].[EventSessionGuests].[Status]='gsBooked' OR
[NiagaraEventManager].[dbo].[EventSessionGuests].[Status]='gsBookedNoSeat'
OR
[NiagaraEventManager].[dbo].[EventSessionGuests].[Status]='gsRegistered'))
ORDERED FORWARD)
| | | | | | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventAttendanceObjects].[PK_EventAttendanceObjects]),
SEEK:([NiagaraEventManager].[dbo].[EventAttendanceObjects].[EventAttendanceObjectGUID]=[NiagaraEventManager].[dbo].[EventSessionGuests].[EventAttendanceObjectGUID]),
WHERE:([NiagaraEventManager].[dbo].[EventAttendanceObjects].[EventLocationGUID]={guid'C34D07F7-72D9-4B8E-8556-E7D16F34C40C'})
ORDERED FORWARD)
| | | | | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventGuests].[PK_EventGuests]),
SEEK:([NiagaraEventManager].[dbo].[EventGuests].[EventGuestGUID]=[NiagaraEventManager].[dbo].[EventSessionGuests].[EventGuestGUID])
ORDERED FORWARD)
| | | | | |--Clustered Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventGuests].[IX_EventGuests]),
SEEK:([NiagaraEventManager].[dbo].[EventGuests].[EventGUID]=[NiagaraEventManager].[dbo].[EventGuests].[EventGUID]
AND [Uniq1006]=[Uniq1006]) LOOKUP ORDERED FORWARD)
| | | | |--Clustered Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Persons].[PK_Persons]),
SEEK:([NiagaraEventManager].[dbo].[Persons].[PersonGUID]=[NiagaraEventManager].[dbo].[EventGuests].[PersonGUID])
ORDERED FORWARD)
| | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Patrons].[IX_Patrons]),
SEEK:([NiagaraEventManager].[dbo].[Patrons].[PersonGUID]=[NiagaraEventManager].[dbo].[Persons].[PersonGUID])
ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventSeats].[IX_EventSeats_EventAttendanceObjectGUID]),
SEEK:([NiagaraEventManager].[dbo].[EventSeats].[EventAttendanceObjectGUID]=[NiagaraEventManager].[dbo].[EventAttendanceObjects].[EventAttendanceObjectGUID]),
WHERE:([NiagaraEventManager].[dbo].[EventSeats].[EventSessionGuestGUID]=[NiagaraEventManager].[dbo].[EventSessionGuests].[EventSessionGuestGUID])
ORDERED FORWARD)
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([PartyMemberPersons].[PersonGUID]) OPTIMIZED)
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([PartyMemberPersons].[PersonGUID]))
| | |--Clustered Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Persons].[PK_Persons] AS
[PartyMemberPersons]),
SEEK:([PartyMemberPersons].[PersonGUID]=[NiagaraEventManager].[dbo].[EventGuests].[PartyMemberPersonGUID])
ORDERED FORWARD)
| | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Dependents].[IX_Dependents]),
SEEK:([NiagaraEventManager].[dbo].[Dependents].[PersonGUID]=[NiagaraEventManager].[dbo].[Persons].[PersonGUID]
as [PartyMemberPersons].[PersonGUID]) ORDERED FORWARD)
| |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Patrons].[IX_Patrons] AS
[PartyMemberPatrons]),
SEEK:([PartyMemberPatrons].[PersonGUID]=[NiagaraEventManager].[dbo].[Persons].[PersonGUID]
as [PartyMemberPersons].[PersonGUID]) ORDERED FORWARD)
|--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventReservations].[PK_EventReservations]),
SEEK:([NiagaraEventManager].[dbo].[EventReservations].[EventReservationGUID]=[NiagaraEventManager].[dbo].[EventSessionGuests].[EventReservationGUID])
ORDERED FORWARD)

Un-Optimized Query
================
Compute Scalar(DEFINE:([Expr1067]=CONVERT_IMPLICIT(int,[Expr1068],0)))
|--Stream Aggregate(DEFINE:([Expr1068]=Count(*)))
|--Filter(WHERE:([NiagaraEventManager].[dbo].[EventReservations].[EventReservationGUID]
IS NULL))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventReservationGUID]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventGuests].[PartyMemberPersonGUID]))
|

|--Filter(WHERE:([NiagaraEventManager].[dbo].[EventSeats].[EventSeatGUID] IS

NULL))
| | |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventSessionGuestGUID],
[NiagaraEventManager].[dbo].[EventAttendanceObjects].[EventAttendanceObjectGUID]))
| | |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[Persons].[PersonGUID]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventGuests].[PersonGUID]))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([Uniq1006],
[NiagaraEventManager].[dbo].[EventGuests].[EventGUID]))
| | | | | |--Nested Loops(Inner Join,
OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventGuestGUID]))
| | | | | | |--Nested Loops(Inner
Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventAttendanceObjectGUID]))
| | | | | | | |--Nested
Loops(Inner Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventLocations].[EventSessionGUID]))
| | | | | | | | |--Nested
Loops(Inner Join, OUTER
REFERENCES:([NiagaraEventManager].[dbo].[EventLocations].[EventSessionGUID]))
| | | | | | | | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventLocations].[PK_EventLocations]),
SEEK:([NiagaraEventManager].[dbo].[EventLocations].[EventLocationGUID]={guid'C34D07F7-72D9-4B8E-8556-E7D16F34C40C'})
ORDERED FORWARD)
| | | | | | | | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventSessions].[PK_EventSessions]),
SEEK:([NiagaraEventManager].[dbo].[EventSessions].[EventSessionGUID]=[NiagaraEventManager].[dbo].[EventLocations].[EventSessionGUID])
ORDERED FORWARD)
| | | | | | | | |--Clustered
Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventSessionGuests].[IX_EventSessionGuests]),
SEEK:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventSessionGUID]=[NiagaraEventManager].[dbo].[EventLocations].[EventSessionGUID]),
WHERE:([NiagaraEventManager].[dbo].[EventSessionGuests].[EventLocationGUID]={guid'C34D07F7-72D9-4B8E-8556-E7D16F34C40C'}
AND ([NiagaraEventManager].[dbo].[EventSessionGuests].[Status]='gsBooked' OR
[NiagaraEventManager].[dbo].[EventSessionGuests].[Status]='gsBookedNoSeat'
OR
[NiagaraEventManager].[dbo].[EventSessionGuests].[Status]='gsRegistered'))
ORDERED FORWARD)
| | | | | | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventAttendanceObjects].[PK_EventAttendanceObjects]),
SEEK:([NiagaraEventManager].[dbo].[EventAttendanceObjects].[EventAttendanceObjectGUID]=[NiagaraEventManager].[dbo].[EventSessionGuests].[EventAttendanceObjectGUID]),
WHERE:([NiagaraEventManager].[dbo].[EventAttendanceObjects].[EventLocationGUID]={guid'C34D07F7-72D9-4B8E-8556-E7D16F34C40C'})
ORDERED FORWARD)
| | | | | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventGuests].[PK_EventGuests]),
SEEK:([NiagaraEventManager].[dbo].[EventGuests].[EventGuestGUID]=[NiagaraEventManager].[dbo].[EventSessionGuests].[EventGuestGUID])
ORDERED FORWARD)
| | | | | |--Clustered Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventGuests].[IX_EventGuests]),
SEEK:([NiagaraEventManager].[dbo].[EventGuests].[EventGUID]=[NiagaraEventManager].[dbo].[EventGuests].[EventGUID]
AND [Uniq1006]=[Uniq1006]) LOOKUP ORDERED FORWARD)
| | | | |--Clustered Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Persons].[PK_Persons]),
SEEK:([NiagaraEventManager].[dbo].[Persons].[PersonGUID]=[NiagaraEventManager].[dbo].[EventGuests].[PersonGUID])
ORDERED FORWARD)
| | | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Patrons].[IX_Patrons]),
SEEK:([NiagaraEventManager].[dbo].[Patrons].[PersonGUID]=[NiagaraEventManager].[dbo].[Persons].[PersonGUID])
ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventSeats].[IX_EventSeats_EventAttendanceObjectGUID]),
SEEK:([NiagaraEventManager].[dbo].[EventSeats].[EventAttendanceObjectGUID]=[NiagaraEventManager].[dbo].[EventAttendanceObjects].[EventAttendanceObjectGUID]),
WHERE:([NiagaraEventManager].[dbo].[EventSeats].[EventSessionGuestGUID]=[NiagaraEventManager].[dbo].[EventSessionGuests].[EventSessionGuestGUID])
ORDERED FORWARD)
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([PartyMemberPersons].[PersonGUID]))
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES:([PartyMemberPersons].[PersonGUID]))
| | |--Clustered Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Persons].[PK_Persons] AS
[PartyMemberPersons]),
SEEK:([PartyMemberPersons].[PersonGUID]=[NiagaraEventManager].[dbo].[EventGuests].[PartyMemberPersonGUID])
ORDERED FORWARD)
| | |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Dependents].[IX_Dependents]),
SEEK:([NiagaraEventManager].[dbo].[Dependents].[PersonGUID]=[NiagaraEventManager].[dbo].[Persons].[PersonGUID]
as [PartyMemberPersons].[PersonGUID]) ORDERED FORWARD)
| |--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[Patrons].[IX_Patrons] AS
[PartyMemberPatrons]),
SEEK:([PartyMemberPatrons].[PersonGUID]=[NiagaraEventManager].[dbo].[Persons].[PersonGUID]
as [PartyMemberPersons].[PersonGUID]) ORDERED FORWARD)
|--Index
Seek(OBJECT:([NiagaraEventManager].[dbo].[EventReservations].[PK_EventReservations]),
SEEK:([NiagaraEventManager].[dbo].[EventReservations].[EventReservationGUID]=[NiagaraEventManager].[dbo].[EventSessionGuests].[EventReservationGUID])
ORDERED FORWARD)

Reply to this Message

Gert-Jan Strik - 20 Jul 2008 01:02 GMT

> > Is this 2000? My guess would be that the OPTIMIZED one is a clustered
> > index or covering index where as the other is not so it does not have to
[quoted text clipped - 14 lines]
> nested loop", especially since that google search gives this post as the #2
> hit.

[snipped]

Interesting question. I have found quite a few of my nested loops to be
"optimized" as well.

In case you find the answer, then please post it here.

My guess is, that there is a difference in statistics. Maybe in the
"optimized" version, the optimizer expects that more nested loops need
to be done. However, whether OPTIMIZED means that the storage engine
should do more read aheads, or whether the optimizer analyzed more
options, or something completely different, I don't know.

Signature

Gert-Jan
SQL Server MVP

Reply to this Message

Ian Boyd - 21 Jul 2008 15:24 GMT

> Maybe in the
> "optimized" version, the optimizer expects that more nested loops need
> to be done. However, whether OPTIMIZED means that the storage engine
> should do more read aheads, or whether the optimizer analyzed more
> options, or something completely different, I don't know.

In case anyone got the wrong idea of the 5490 vs 5491 reads due to
'optimized', the extra read was almost certainly due to an extra page of
data in one version of the database.

Reply to this Message

Gert-Jan Strik - 24 Jul 2008 11:36 GMT

Ian,

I Think I got the answer.

Quote Craig Freedman:
OPTIMIZED refers to sorting the keys to make index searches
sequential
(instead of random) and to reduce disk latency. WITH PREFETCH refers
to performing multiple index searches asynchronously and in parallel
to hide some of the disk latency. They can be used separately or
together.

This optimizing can have both positive and negative effects.

The positive effect is that there will be less Random I/O if many
clustered index' rows have to be read from disk, and potentially less
disk head thrashing. On a system with little memory (of high memory
pressure) there will less I/O because each clustered index row is read
only once (or not at all).

The negative effect is that there is a cost associated with the sort. A
bigger negative effect has to do with locks. Any read lock in Read
Committed transaction isolation level will be retained until the end of
the transaction, instead of release immediately after use. This could
cause blocking delays. This is explained in more detail here:
http://blogs.msdn.com/craigfr/archive/2007/06/07/read-committed-and-bookmark-loo
kup.aspx

So if you have a very high Buffer Cache Hit Ratio, then this
optimization could work out very poorly for you, and I don't know any
"hint" to disallow this optimization.

I have a follow question though. The server with the query plan that
does not have the "OPTIMIZED" keyword, is that a SQL Server 2000
instance?

Because on "earlier" versions of SQL Server 2005 I found that I did not
see the OPTIMIZED keyword, but then the input stream had a Sort
immediately before the (un-optimized) Nested Loops.

Signature

Gert-Jan
SQL Server MVP

Reply to this Message

Ian Boyd - 24 Jul 2008 14:11 GMT

> OPTIMIZED refers to sorting the keys to make index searches
> sequential
> (instead of random) and to reduce disk latency.

i would have thought having the server deal with a non-sorted index would be
called an INDEX SCAN, as opposed to an INDEX SEEK.

> I have a follow question though. The server with the query plan that
> does not have the "OPTIMIZED" keyword, is that a SQL Server 2000
> instance?

Hunting down for the files with the traces in thes, i believe so. OPTIMIZED
was on the 2005 machine, the other machine was 2000.

> Because on "earlier" versions of SQL Server 2005 I found that I did not
> see the OPTIMIZED keyword, but then the input stream had a Sort
> immediately before the (un-optimized) Nested Loops.

No extra sort on the 2000 machine though.

Reply to this Message

Andrew J. Kelly - 24 Jul 2008 15:18 GMT

> i would have thought having the server deal with a non-sorted index would
> be called an INDEX SCAN, as opposed to an INDEX SEEK.

This is not about a non-sorted index. Indexes are always sorted but not
necessarily in the order that you want them to be for the type of operation
you are doing. This is taking the keys that it will need to lookup in the
next operation and sorting them so the lookups are potentially physically
less intensive.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

>> OPTIMIZED refers to sorting the keys to make index searches
>> sequential
[quoted text clipped - 15 lines]
>
> No extra sort on the 2000 machine though.

Reply to this Message

Ian Boyd - 25 Jul 2008 21:16 GMT

> This is not about a non-sorted index. Indexes are always sorted but not
> necessarily in the order that you want them to be for the type of
> operation you are doing. This is taking the keys that it will need to
> lookup in the next operation and sorting them so the lookups are
> potentially physically less intensive.

Ohhhh. Using a covering index to save I/O cost, but sorting it on a
different key to make it work for a particular join.

Clever.

Reply to this Message