August 11, 2010 at 7:41 am
Hi guys,
I am running a query, and find out that a simple where condition can change the whole execution plan, which is not good and takes long time.
The scenario is :
select * from table1
left join openquery(DB2, 'select * from view1') table2 on table1.id = table2.id
where table1.age= xx and table1.year = xx
this is a simple query with a linked server, I use openquery to fetch linked server's view, the view returns at least 10k+ rows with a big schema.
The question is:
If I change where condition, say table1.age = 10, which will return 1000 rows, which uses hash merge join and returns in a second.
If I change where condition, say table1.age = 20, which will return 400 rows, which uses nested loop join and returns over 1 minute.
I am not sure why this happens, and how could I optimize it, 400 rows cause 1+ minute and 1000 rows cause a second..make any sense?
Thanks.
August 11, 2010 at 10:35 am
Hi,
Question for you, are you using this query in a stored procedure? If so you might have issue with parameter sniffing; which can cause SQL Server to generate inefficient execution plan. Another question for you is Table1, what kind of indexes and statistics it currently holds? If it has proper stats/indexes, when were they last updated?
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 11, 2010 at 10:41 am
Hi Mohit,
No, I am not running this query in a stored procedure, just regular select query.
I have primary key index and nonclustered index on age for example, and it's been updated just before I run the query.
I don't know why the single where condition changes the join execution plan, and can we change the execution plan by our control?
Thanks.
Mohit K. Gupta (8/11/2010)
Hi,Question for you, are you using this query in a stored procedure? If so you might have issue with parameter sniffing; which can cause SQL Server to generate inefficient execution plan. Another question for you is Table1, what kind of indexes and statistics it currently holds? If it has proper stats/indexes, when were they last updated?
August 11, 2010 at 11:08 am
princa (8/11/2010)
The question is:If I change where condition, say table1.age = 10, which will return 1000 rows, which uses hash merge join and returns in a second.
If I change where condition, say table1.age = 20, which will return 400 rows, which uses nested loop join and returns over 1 minute.
I am not sure why this happens, and how could I optimize it, 400 rows cause 1+ minute and 1000 rows cause a second..make any sense?
First, the optimizer changing the plan with that much of a change in the output is not surprising to me. I would really suggest checking out Grant Fritchey's book on Execution Plans. You can find it on the Simple Talk website or just search Google. It's still free I think.
Secondly, you could pass in the join hint, i.e. LEFT HASH JOIN and that may help but without seeing base tables and indexing it will be impossible to know from my standpoint. 🙂 Please note that this may be more costly as well so, test....
As Mohit stated, indexing properly and statistics will play a big part here so, make sure that you have those in place.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 11, 2010 at 11:24 am
First, the optimizer changing the plan with that much of a change in the output is not surprising to me. I would really suggest checking out Grant Fritchey's book on Execution Plans. You can find it on the Simple Talk website or just search Google. It's still free I think.
Secondly, you could pass in the join hint, i.e. LEFT HASH JOIN and that may help but without seeing base tables and indexing it will be impossible to know from my standpoint. 🙂 Please note that this may be more costly as well so, test....
As Mohit stated, indexing properly and statistics will play a big part here so, make sure that you have those in place.
David, You rock!
I will definitely check that book later on.
I used 'left hash join' in the query which I assume that force execution plan to use hash match (left outer join), and it works fine now.
Table2 actually is a huge view which does not have index in my case.
So how could we solve view with index or sort problem. (this is absolutely another question, just curious)
Thanks David!
August 11, 2010 at 11:32 am
Well, glad that worked - somewhat. Now for my disclaimer - when having to use join hints, index hints, etc it tells me that the optimizer is not able to choose the BEST plan due to the information that it has, typically cardinality estimates, either due to statistics, indexes, wildcards being passed in etc. So, when I have to use a hint like that to get a solution that performs well I will spend a LOT of time working to find out why the optimizer is doing that and then work to find a solution through indexing or query rewrites, etc.
As for going against the view, it would still be hitting the base table indexing and you should be able to adjust that appropriately to meet the needs of the query. Having the linked server in the mix here is going to make it very difficult though so that will require extra work.
I think you will find the book that grant wrote quite helpful for this effort and for all others that you have. Really understanding the execution plan can revolutionize your abilities as a DBA. I know that it has helped me a ton.
Enjoy!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 11, 2010 at 11:43 am
David Benoit (8/11/2010)
Well, glad that worked - somewhat. Now for my disclaimer - when having to use join hints, index hints, etc it tells me that the optimizer is not able to choose the BEST plan due to the information that it has, typically cardinality estimates, either due to statistics, indexes, wildcards being passed in etc. So, when I have to use a hint like that to get a solution that performs well I will spend a LOT of time working to find out why the optimizer is doing that and then work to find a solution through indexing or query rewrites, etc.As for going against the view, it would still be hitting the base table indexing and you should be able to adjust that appropriately to meet the needs of the query. Having the linked server in the mix here is going to make it very difficult though so that will require extra work.
I think you will find the book that grant wrote quite helpful for this effort and for all others that you have. Really understanding the execution plan can revolutionize your abilities as a DBA. I know that it has helped me a ton.
Enjoy!
Thanks a bunch David!!
I agree with what you said, I will try to spend sometime on the initial query and find out the problems.
As you said that view will depend on the base tables' indexing, so since my view is a huge one and join so many tables, I might need to check their indexes and statistics and etc.
But what I am not understanding is my select query left join a view and in overall where condition, I just change a number of that condition, and this condition is not relative with view at all, it's the base table that joins with view. I think it's due to the sql optimizer that will estimate the potential query result and make a plan. This kind of issue I have never met before....so you helped me a lot!! thanks again!
August 11, 2010 at 11:46 am
First, the optimizer changing the plan with that much of a change in the output is not surprising to me. I would really suggest checking out Grant Fritchey's book on Execution Plans. You can find it on the Simple Talk website or just search Google. It's still free I think.
.
BTW, does this book help ? SQL Server 2008 query performance tuning distilled [electronic resource] / Grant Fritchey, Sajal Dam.
I can only find this book on my online-source, which I can borrow and read.
I find the one you said on Amazon. but I need to purchase that. 😀
August 11, 2010 at 11:55 am
Not sure about the Performance Tuning book. I'm sure it is good though. The e-book that I was referring to can be found HERE on Simple Talk[/url].
It really provides some great foundational knowledge and best of all it is free. Being that the other book is not free I'm sure it has even more detail and some great advice overall.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 11, 2010 at 12:04 pm
David Benoit (8/11/2010)
Not sure about the Performance Tuning book. I'm sure it is good though. The e-book that I was referring to can be found HERE on Simple Talk[/url].It really provides some great foundational knowledge and best of all it is free. Being that the other book is not free I'm sure it has even more detail and some great advice overall.
Thanks a lot David. I am able to download them and will spend sometime to read and may have questions just hit you. 😛
August 11, 2010 at 12:14 pm
princa (8/11/2010)
Thanks a lot David. I am able to download them and will spend sometime to read and may have questions just hit you. 😛
Glad you could get them and yes, please throw questions back on here. I know I like to learn from questions of others. 😀
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 11, 2010 at 1:54 pm
Sorry didn't reply been out of office, thanks for picking up my slack David :D.
I have never found really a book that has all the answers for performance tuning, I think mostly because I find performance tuning requires you to understand many aspects of SQL Server. The book David recommended is a must read, I think when book came out I read it at least 4 or 5 times. His examples are the key there, really try them out; Grant went through alot to dig those examples out.
In addition of you are serious to learning about performance tuning and such, I recommend Inside Microsoft SQL Server 2005: Query Tuning and Optimization (Link).
Cheers ^.^.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 11, 2010 at 2:43 pm
Thanks a lot Mohit, though you were out of office and you didn't miss anything and I really appreciate for the help and suggestions!!!!!
Just a non-technical question to you Mohit and David, since you guys are pretty good with DB,
I am just a beginner in DB, working as a web developer but work with SQL server DB together, do all the DBA stuff, sometimes with SSIS, SSRS, never touched SSAS yet. I passed 70-431 and now get ready for 70-448 which is SQL Server 2008 BI. I just want to move forward to let's say BI jobs, which will work with vb.net, C#, and DB. Do you guys have any suggestions on this? what's the better way to be a BI? 😀
we can message in Forum not necessary chat on this topic. 😀
Thanks.
August 11, 2010 at 2:48 pm
I'll have to pass on really answering the details of your question. 🙂
My personal experience has been that there is so much to learn from the DB admin and performance tuning perspective that I have little time, and desire, to really branch off into other things. I really enjoy the DBA focus so, my answers would be totally slanted toward that.
I will say though that the best thing that you could do would be to find a place where the technology you want to really learn is being used and jump in. Even if you have to start out on the junior end of things the experience of being around the production use of that technology will allow for the quickest and the deepest growth.
All opinion though...
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 12, 2010 at 8:51 am
I agree with David, but I am going to say a Quote a buddy told me once. SQL Server has gotten so big, you can no longer say you are an expert in everything SQL Server. It is just not possible, I focus on DB Mirroring, Clustering and Performance Tuning; even that I find lots to keep up with.
See as a DBA just knowing the DBMS is not enough we have to understand all the sub-systems DBMS interact with. So our job doesn't just stop at SQL. I need to know windows, the storage systems, the networks, how everything works together.
So if you want to be BI expert GREAT; but knowing other aspects of DB work doesn't hurt.
Again as David said, it is an opinion. Take care :D.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply