Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Clustered Index Scan Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 10:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:39 AM
Points: 435, Visits: 437
Hi,
I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. Please let me know what are the cause of the Index scan how can we change that to index seek?

I am giving that kind of similar query below

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '

Thanks,
Ramana
Post #1556056
Posted Friday, March 28, 2014 11:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 2,691, Visits: 3,375
ramana3327 (3/28/2014)
Hi,
I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. Please let me know what are the cause of the Index scan how can we change that to index seek?

I am giving that kind of similar query below

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '

Thanks,
Ramana
You really haven't provided enough information. Please look at the link referring to Jeff Moden in my signature to help us help you. That being said, I assume that since this is a product table, that the productid is the clustered key. Clustered index contains all of your data. So, since you are filtering on SalesID and it is not the first predicate in the clustered key (also assuming you have no other indexes), it has to scan the clustered index (data) to find what you are looking for. One solution (Assuming it makes sense on all levels), is to add a non-clustered index on Product(SalesID, Product) and include the productID column.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1556071
Posted Friday, March 28, 2014 11:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:50 PM
Points: 33,073, Visits: 15,187
First, don't use NOLOCK. It's a poor practice.

Second, an index scan occurs when the query optimizer thinks that this is the fastest way to find data. Without an index that works better, a scan occurs. If you want to avoid a scan, you'll likely need some index on Product or SalesID. Also, your SalesID value looks wrong. Are you somehow building dynamic SQL?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1556072
Posted Friday, March 28, 2014 2:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 1,293, Visits: 3,716
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '


Apart from not being SARGable, this query puzzles me, would you ever have a SalesID value of "@salesId"? or Product = "Clothes " with a trailing space?

As Steve has already mentioned, the dirty read / nolock, I'm not going to overstate the obvious there, but still ask the question, why using this hint? This is something that is justifiable, only if there is a specific problem, not as a blanket protection!

Back to the question, the optimizer may deem an index scan to be less work than other options. This can happen if the table is small, indexes are fragmented or not applicable and so on. To fully work out 1) is it bad 2) can it be fixed, one has to have more information, as Jared said earlier.

Hope this helps.



Post #1556156
Posted Friday, March 28, 2014 2:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 42,462, Visits: 35,522
Eirikur Eiriksson (3/28/2014)
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '


Apart from not being SARGable...


All the predicates in that query are SARGable. They're all direct comparisons of a column to an expression (a nonsensical one in one case)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1556161
Posted Friday, March 28, 2014 3:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 1,293, Visits: 3,716
GilaMonster (3/28/2014)
Eirikur Eiriksson (3/28/2014)
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '


Apart from not being SARGable...


All the predicates in that query are SARGable. They're all direct comparisons of a column to an expression (a nonsensical one in one case)


I may have wrongly jumped to a conclusion here, correct me if I'm wrong, but I think the statement is not SARGable because of Data Type Precedence (MSN: When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.)
My train of thought was that an "id" value would be numeric.
If I am wrong, then sorry
Post #1556168
Posted Friday, March 28, 2014 3:25 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 42,462, Visits: 35,522
INT has a higher precedence than string, the string will convert to int, so if that is an int column (which we don't know), then the query will be essentially

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= CONVERT_IMPLICIT(INT, '@salesId') and Product = 'Clothes '

Which is SARGable and going to fail with a conversion error.

If you don't believe me, try running this:

IF ('This is a string' = 1)
print 'True'




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1556170
Posted Friday, March 28, 2014 3:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 1,293, Visits: 3,716
GilaMonster (3/28/2014)
INT has a higher precedence than string, the string will convert to int, so if that is an int column (which we don't know), then the query will be essentially

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= CONVERT_IMPLICIT(INT, '@salesId') and Product = 'Clothes '

Which is SARGable and going to fail with a conversion error.

If you don't believe me, try running this:

IF ('This is a string' = 1)
print 'True'


Guess I'm guilty here neither do I know the data type of the column nor the variable (being passed as char), have to learn to think before talk....

Still leaves the original questions unanswered.

Thanks Gail for correcting my wrong her!
Post #1556175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse