| Author |
Topic |
|
ruan4u
Posting Yak Master
132 Posts |
 Posted - 2004-12-07 : 09:26:04
|
| How to read and interpret the SQL query execution plan. i can fix issues whereever it uses full table scan. But there are cases where the right indexes are hit.but it shows 20%.What am i supposed to look and is there anything apart from this to optimize my query? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-07 : 09:36:14
|
well index seeks are better than index scans...look for that. i wouldn't rely much on the percentage numbers. when you see a whole batch has 100% and some join has 250% i gave up on lowering the percentage. instead i tried to have everywhere the same percentage. ie. all of the joins that have more than 20% musn't have more that 30% and stuff like that... i'm probably not explaining it to good...  Go with the flow & have fun! Else fight the flow  |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 09:40:25
|
| The percentages are just that, percentages, and even those are estimates. If you have a one-step query that uses the best index seek available, it will have a percentage of 100%. Don't get hung up on a percentage.Look at the total query time, look for scans, look for very complicated joins that don't use indexes. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-12-07 : 09:44:16
|
| see for ex.. i have query and it uses Index seek, but it shows cost as 100%. Now when i highlght to get more info, it saysEstimated I/O=0.000316# of executes =1est CPU=0.000041and est cost=.0064(100%) :(now iam confused that if I/O is less and CPU is less and it took one execute, how is the cost 100% and is it bad? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 09:46:56
|
| Please read what I just posted. You're doing exactly what I said not to do: worrying about percentages. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-12-07 : 09:48:52
|
| srry, i read ur post after i posted.The query analzer has a time which keeps ticking to show how long its been since executing a query. if thats what iam supposed to look,then it says 1 sec. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 09:52:50
|
| OK. If you only have 1 step, and that step is an index seek, then this query is as efficient as it can be. If you don't see any of the warning signs I mentioned then your query is probably fine. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-12-07 : 09:57:25
|
| ok,but in some of my other queries i have like nested loops with some higher percentage.ignoring the % what does that nested loop mean.Also things like Hash match/Inner join..how should i interpret them for query optimization |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 10:03:15
|
| The best thing to do is to look in Books Online under "Execution Plan Options". There is an excellent table of all the query plan operators, and you can click on them to get a description of what they do. There is also a section in BOL for "Optimizing Database Performance", and within that "Query Tuning". Read all of those thoroughly, you'll get a much better understanding of how to understand plans and how to improve them. |
|
|
|
|
execution plan