Unbelievable

I am trying to optimise a stored proc to remove table scans and would like anyone to provide a rational explanation to the problem I am about to explain.
This is a simplified version of my stored proc.

CREATE PROCEDURE usp_MyProc
(
@pi_UserOIDObjectID -- a user defined type of BIGINT
)
AS
SET NOCOUNT ON
Select * from MyTable Where UserOID = @pi_UserOID

MyTable has many rows, but is indexed onthe field UserOID which is of ObjectID Type
When I run the execution plan it says I perform a table scan rather than a bookmark and a clustered index seek.

OK so If change the stored proc to be

CREATE PROCEDURE usp_MyProc
(
@pi_UserOIDObjectID -- a user defined type of BIGINT
)
AS
SET NOCOUNT ON
DECLARE @v_MyID ObjectID
SET @v_MyID = @pi_UserID
Select * from MyTable Where UserOID = @v_MyID

In the execution plan it performs as expected, a clustered index seek.


WHY WHY WHY???

Any answers are much appreciated.

Thanks

I think we are dealing with a typical case where caching of execution plan is creating problems.

I think SQL Server generated an execution plan for this stored procedure when it was executed first based on the input value. Now if this input value was not good enough to make query selective, SQL Server decided to have table scan. For future queries also it is using the same plan.

So if the input criteia varies so muh in your query, I would recommend you create this procedure using WITH RECOMPILE option so that every time a new plan will be developed based on the input value.

HTH.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I tried calling my stored proc using the WITH RECOMPILE directive but it doesnt stop the problem.

Is it possible that SQL server thinks that a Table scan is more optimal/faster in certain cases?

In that case you may have to force the use of index using an hint in the query.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

What are the indexes on the table? What is the PK?

The clustered one is a unique index on UserOID?

The parameter passed to the proc is never below or above the range of currently valid UserOIDs?

Cheers
Twan