August 25, 2011 at 4:48 am
Hi,
I have a doubt regarding index scan operator in the execution plan. Let us assume that the query uses lot of joins and filters in the where clause and I am just provided an execution plan.
Now I see the costly index scan operator. When I move my mouse pointer over it, I see that it shows:
- Output list
-Seek predicates
Is it possible for me to decide for a new index by seeing the columns in one of the above two?
Sorry if I am sounding so stupid but if you can clear my doubt, it will be great for me to move ahead.
Regards
Chandan
August 25, 2011 at 4:51 am
You sure about that scan?
An index scan won't have a seek predicates property, only index seeks can have seek predicates.
Seek predicates are what the seek looks for in the b-tree, output list is the columns being returned. All those columns must already be in that index, or they wouldn't be able to appear in the properties.
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
August 25, 2011 at 5:04 am
GilaMonster (8/25/2011)
You sure about that scan?An index scan won't have a seek predicates property, only index seeks can have seek predicates.
Seek predicates are what the seek looks for in the b-tree, output list is the columns being returned. All those columns must already be in that index, or they wouldn't be able to appear in the properties.
I am sorry for poor phrasing of my question.
-1) I am seeing an index scan operator- so by looking at it, can i determine which columns to choose while making a new index.
2) When there is a seek operator, i can see predicates. Apart from that there is also a lookup. how to determine which column to add to the existing index on which seek is done so that i can avoid lookup on a billion row table.
Thanks
Chandan
August 25, 2011 at 5:16 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
August 25, 2011 at 5:28 am
GilaMonster (8/25/2011)
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
you might have seen a similar execution plan 2-3 days back. But I request you to drill down to query 29 in the plan (52% cost relative to batch) where scan operator is being used on table 'orders'. I am working on my own database version of this db and this table has no non clustered index.
So by looking at the plan below, can you suggest some index in orders table.
Thanks
Chandan
August 25, 2011 at 5:32 am
Can you post the query please
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
August 25, 2011 at 5:43 am
GilaMonster (8/25/2011)
Can you post the query please
I am posting the part of the query which is causing the issue. Sorry but this is too big to read.
If you go through it, you will find that it reads data from a view called vw_D1OrderHeader which is a combination of 4-5 tables including table 'orders'. rest all tables are seeked upon but the orders table is the biggest and is scanned on its clustered index. I am attaching the part of the query as well as definition of the view(just in case). I have also verified that all local variables used are of same datatype in orders table so that there is no impliciti conversion leading to scan.
the query 29 plan shows orders is scanned twice and if i can reduce it to something better, i can bring down the number of IO operations.
Regards
Chandan
August 25, 2011 at 6:02 am
Hi ,
you should find this post interesting
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
August 25, 2011 at 6:03 am
There are three major issues that I see:
1) it looks like a catch-all query to cover partial results from a LEFT OUTER JOIN and an INNER JOIN. I would recommend to split into the two logical queries.
2) there's a table variable with several million rows. This should be a indexed temp table and the number of rows should be verified to be accurate (might be an accidential cross join somewhere)
3) OR a.CustName LIKE '%' + @ipSearchText + '%' OR a.ShipTo LIKE '%'+@ipSearchText+'%' OR a.CarrierCode LIKE '%'+@ipSearchText+'%' in the WHERE clause does not really help to avoid the table scan.
I would follow the good old divide'n'conquer approach and cut the moster apart using several steps. If the 'LIKE %' clause can be applied at the very end (e.g. since it doesn't provide a high selectivity), I would not include in the first query.
That's just a personal opinion though. Whatever Gail suggest takes higher precedence. By magnitudes. 😉
August 25, 2011 at 6:15 am
Dave Ballantyne (8/25/2011)
Hi ,you should find this post interesting
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Thanks. This page is bookmarked in my browser and will read it again today. For smaller experieced DBA like me who started as an accidental DBA doing back restores and now looking into codes by non-Steve jobs developers makes life a bit tough but from you guys I am learning faster than ever.
August 25, 2011 at 6:22 am
LutzM (8/25/2011)
There are three major issues that I see:1) it looks like a catch-all query to cover partial results from a LEFT OUTER JOIN and an INNER JOIN. I would recommend to split into the two logical queries.
2) there's a table variable with several million rows. This should be a indexed temp table and the number of rows should be verified to be accurate (might be an accidential cross join somewhere)
3) OR a.CustName LIKE '%' + @ipSearchText + '%' OR a.ShipTo LIKE '%'+@ipSearchText+'%' OR a.CarrierCode LIKE '%'+@ipSearchText+'%' in the WHERE clause does not really help to avoid the table scan.
I would follow the good old divide'n'conquer approach and cut the moster apart using several steps. If the 'LIKE %' clause can be applied at the very end (e.g. since it doesn't provide a high selectivity), I would not include in the first query.
That's just a personal opinion though. Whatever Gail suggest takes higher precedence. By magnitudes. 😉
you are right about a table variable holding many rows. I will try to switch that into a temp table with some index on it.
I agree that like '%' like causes scan no matter what but the text they search is dynamic so I am bound to use it. However as you said that it offers no selectivity, can we move it to the end of the where clause because I think doing this we are just trying to change the selectivity and not the output. Output will be the same irrespective of order of filters.
August 25, 2011 at 6:24 am
I am waiting for the monster to come and give her advice. I will combine all everything coming from all 3 of you and try doing something good for me.
Even if i spend some money to get trained, I am sure the local community here cannot give me some gems like you guys do.thank you all for teaching me something live.
August 25, 2011 at 6:26 am
chandan_jha18 (8/25/2011)
However as you said that it offers no selectivity, can we move it to the end of the where clause because I think doing this we are just trying to change the selectivity and not the output. Output will be the same irrespective of order of filters.
Order of filters in the where clause is completely irrelevant, SQL's not going to give you better performance because you move a filter further along the where clause.
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
August 25, 2011 at 6:30 am
GilaMonster (8/25/2011)
chandan_jha18 (8/25/2011)
However as you said that it offers no selectivity, can we move it to the end of the where clause because I think doing this we are just trying to change the selectivity and not the output. Output will be the same irrespective of order of filters.Order of filters in the where clause is completely irrelevant, SQL's not going to give you better performance because you move a filter further along the where clause.
Oh. Then i wont consider it moving at the bottom of where clause. Did you get a chance to look at he schema and have any suggestion. I am not in a hurry:-P Whenever you can find something here, please pitch in.
thanks monster!
August 25, 2011 at 6:37 am
chandan_jha18 (8/25/2011)
...you are right about a table variable holding many rows. I will try to switch that into a temp table with some index on it.
I agree that like '%' like causes scan no matter what but the text they search is dynamic so I am bound to use it. However as you said that it offers no selectivity, can we move it to the end of the where clause because I think doing this we are just trying to change the selectivity and not the output. Output will be the same irrespective of order of filters.
Seems like a misunderstanding:
When I wrote
If the 'LIKE %' clause can be applied at the very end (e.g. since it doesn't provide a high selectivity), I would not include in the first query.
I was referring to the divide'n'conquer approach, meaning to perform this select statement at the very end of a list of statements, not at the end of a WHERE clause in a complex statement.
Sorry for the confusion.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply