Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered Index Scan


Clustered Index Scan

Author
Message
ramana3327
ramana3327
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 2064
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
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3682
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36162 Visits: 18751
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
My Blog: www.voiceofthedba.com
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6762 Visits: 17733
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? Rolleyes

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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47279 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6762 Visits: 17733
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;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47279 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6762 Visits: 17733
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:-P neither do I know the data type of the column nor the variable (being passed as char), have to learn to think before talk....w00t

Still leaves the original questions unanswered.

Thanks Gail for correcting my wrong her!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search