Uniqueidentifier causing strange execution plan

[Vinny]

Can anyone help me with this strange problem please?

I have a stored procedure, with a parameter defined as a
uniqueidentifier. The procedure does a select with a number of joins,
and filters within the Where clause using this parameter.
(@orderHeader_id uniqueidentifier)

SELECT *
FROM originalOrderHeader ooh
INNER JOIN originalOrderLine ool
ON ooh.id = ool.id
FULL OUTER JOIN orderLine ol
on ool.id = ol.id
AND ool.productCode = ol.productCode
where (ooh.id = @orderHeader_id)
There is a clustered index on the id column of originalOrderHeader,
and on id and productCode of both originalOrderLine and orderLine.
These indexes are regularly rebuilt. The execution plan shows a seek
against these indexes, but the estimated row count values are huge,
and should be single figures.

If I change the SP to accept the parameter as a varchar, and then
explictly cast back to a uniqueidentifier in the where clause, the SP
runs much, much quicker, the execution plan is doing far less work,
and crucially the estimated row counts on the clustered index seeks
are correct (single figures).

Does anyone have any ideas what might be causing this?

[Erland Sommarskog]

Vinny (vi*************@emulous.co.uk) writes:

I have a stored procedure, with a parameter defined as a
uniqueidentifier. The procedure does a select with a number of joins,
and filters within the Where clause using this parameter.
(@orderHeader_id uniqueidentifier)

SELECT *
FROM originalOrderHeader ooh
INNER JOIN originalOrderLine ool
ON ooh.id = ool.id
FULL OUTER JOIN orderLine ol
on ool.id = ol.id
AND ool.productCode = ol.productCode
where (ooh.id = @orderHeader_id)
This does not look right. Do you really mean FULL JOIN? Thanks to the
WHERE predicate, this is effectively an LEFT JOIN.
There is a clustered index on the id column of originalOrderHeader,
A clustered index on uniqueidentifier column is rarely a good idea,
because you easily get large amount of fragmentation.
and on id and productCode of both originalOrderLine and orderLine.
These indexes are regularly rebuilt. The execution plan shows a seek
against these indexes, but the estimated row count values are huge,
and should be single figures.

If I change the SP to accept the parameter as a varchar, and then
explictly cast back to a uniqueidentifier in the where clause, the SP
runs much, much quicker, the execution plan is doing far less work,
and crucially the estimated row counts on the clustered index seeks
are correct (single figures).

I can't say whether the mysterious use of FULL JOIN has anything to
do with it, but at least it's worth to investigate.

Since I don't know the full story how the tables look like, and have not
seen the query plans, it's difficult to be very precise. Certainly a
feature known as "parameter sniffing" is involved here. When SQL Server
builds the query plan for a stored procedure, it looks at the input
parameters and the value of these as a hint when it builds the plan.
Since the plan is cached, this means that if the first invocation of the
procedure has an odd value, you may end up with a bad plan. What you
achieve with your varchar parameter, is that you effectively kill
parameter sniffing, since the optimizer is blind for the values of
variables.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp