troubleshooting Question

Constant Scan

Avatar of Faiga Diegel
Faiga DiegelFlag for United States of America asked on
Microsoft SQL Server 2005
2 Comments1 Solution1187 ViewsLast Modified:
I have a simple select, something like this:

SELECT Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9
FROM Table1 T1
    INNER JOIN Table2 T2 on T1.Column20 = T2.Column1
WHERE T1.Column10 = @Col10
     AND T1.Column11 = @Col11
     AND T1.Column12 = @Col12
     AND T1.Column13 = @Col13
     AND T1.Column14 = @Col14
     AND ISNULL(T1.Column15) = ISNULL(@Col15,0)

clustered index n T2.column1
nonclustered index on T1.Column20
nonclustered index on T1.Column10

I keep getting Constant Scan. The Execution Plan looks something like:

SELECT (Cost: 0%) <-------------------- Constatn Scan (Cost: 100%)

I notice one thing though, the T1.Column11 pass in value is NULL. If I remove that WHERE condition line (AND T1.Column11 = @Col11), then I will get the usual execution plan of Index Seek, KeyLookup, Nested Loops, etc -- which is pretty accetable.

T1.Column11 is non nullable but somewhere we pass in NULL value but sometimes we pass in some value too.

Why am I getting constant scan with that candition? I read and read and read about constant scan, but couldn't really grasp what was that. I don't think my ISNULL function on my WHERE condition is the culprit to the constant scan, i am sure it's the NULL value pass in to T1.Column11.

Any suggestions and advice  are greatly appreciated, Thanks!


ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros