I have the following two tables in different databases:

CREATE TABLE [dbo].[UnitPermission]
    [userID] [varchar](10) NOT NULL,
    [unitID] [nvarchar](10) NOT NULL,

        [userID] ASC,
        [unitID] ASC


CREATE TABLE [dbo].[tb_Request]
    [ID] [int] NOT NULL,
    [typeID] [int] NOT NULL,
    [statusID] [int] NOT NULL,
    [userUnit] [nvarchar](10) NULL,

        [ID] ASC

with nonclustered index

CREATE NONCLUSTERED INDEX [unitIndex] ON [dbo].[tb_Request] 
    [userUnit] ASC

The following stored procedure (not written by me) is causing some problems:

CREATE PROCEDURE [dbo].[sp_GetRequestsByFilter] 
    @userID as varchar(10)



    from tb_Request
    inner join otherdb.dbo.UnitPermission on (otherdb.dbo.UnitPermission.unitID = dbo.tb_Request.userUnit or otherdb.dbo.UnitPermission.unitID like '0')
    where otherdb.dbo.UnitPermission.userID = @userID


In the execution plan there is a Clustered Index Seek on PK_UnitPermission with cost of 94%, Estimated Number of Rows = 1, Actual Number of Rows = 6855.

Seek Predicate:
Prefix: otherdb.dbo.UnitPermission.userID = Scalar Operator (@userID)  
Start: otherdb.dbo.UnitPermission.unitID > Scalar Operator([Expr1016]),
End: otherdb.dbo.UnitPermission.unitID < Scalar Operator([Expr1017])

What is causing this failure in estimation?

I'm guessing it has something to do with like '0' on unitID, but don't understand why the predicates are created this way.

I can't provide the execution plan. It's a local network, no access to www.


You say there is a clustered index seek on the PK_UnitPermission index. That index defines the primary key for a table and consists of the [userID] and [unitID] columns. In your query, you have the following filters or join predicates:

otherdb.dbo.UnitPermission.unitID = dbo.tb_Request.userUnit 
otherdb.dbo.UnitPermission.userID = @userID

You are filtering on both columns of the primary key. By definition, equality filters on all columns of a primary key must return either 0 or 1 rows. The cardinality estimator will never give you a cardinality estimate of 0 rows, so it gives you an estimate of 1 row per seek.

There is no cardinality estimate issue described in the question. The estimated number of rows for an index seek on the inner side of a nested loop is per execution but the actual number of rows reported for the operator is the sum of rows returned over all executions of the operator. In your case, the index seek returned 0 or 1 rows per execution and executed at least 6855 times.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.