Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jillsy
Starting Member
20 Posts |
 Posted - 2006-02-02 : 07:18:58
|
| Hello, I have been looking at the execution plan for a procedure call and the select, compute scalar, stream aggregates, constant scan, nested loops, asserts are all at 0% cost, the PK costs are 2% apart from a rogue 7% and a few 20%, tables scans are all at 23%. The query cost realtive to the batch is 100%. What does this all mean? I have put non-clustered indexes on all the table attributes that are involved in the select statements but this has made no difference, i am guessing this is because my tables are not heavily populated and i may have seen a difference if i had thousands of entries in the tables the select statements acted on, is this assumption correct? Does anyone else bother using the execution plan to tweak there DB or is it a negligible tool?Jill |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-02-02 : 13:22:12
|
| Execution plan is very useful. Check out the link above, but sounds like you may need to take another look at those indexes, the statistics (although you said the tables aren't heavily populated), or consider index hints since you're getting table scans in your execuction plan. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-03 : 06:03:31
|
I never think that the percentage doing-this and the percentage doing-that is very helpful in improving them.I like to look at the plan in plain text, and look for things like Table Scans where I reckon an Index Seek should be used.I also like to look at the number of Logical [disk] Operations, and then see if adding an Index or rearranging the query reduces them. Its also very revealing when most of the time the logical operations is in single digits and then you come across one that is in the hundreds, thousands, or more!-- Comment in the SHOWPLAN to see the Query Plan, -- OR the STATISTICS to see the logical stats-- SET SHOWPLAN_TEXT ONGO-- SET STATISTICS IO ON; SET STATISTICS TIME ON-- ... put query here - e.g.:SELECT * FROM Northwind.dbo.ProductsSET STATISTICS IO OFF; SET STATISTICS TIME OFFGOSET SET SHOWPLAN_TEXT OFFGO See http://www.sql-server-performance.com/statistics_io_time.asp for more detailsKristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-02-03 : 14:00:01
|
| Jill,The answer to your primary question is "YES many of us use the execution plan to tweak our databases." The answer to your other question about table scans is that sometimes the databse will determine what the percentage of rows are that would be returned and if it deems that you would end up spending more time looking up rows after using an index than it would take to just roll through the table ... it will just roll through the table. (This is the same basic rule for small tables or large tables.) Often times this comes in to play with fields that have a very narrow range of potential values. For instance an index on Male or Female, wouldn't be to useful if you assume that 1/2 the rows are 1 or the other. One of the things that you can consider is that if your query ends up pulling 3 fields from TABLE A and your index is on 2 of those fields for the where clause for example, you could add the 3rd field to the index and voila it would use the index for sure because it wouldn't need to lookup the values in the table after the index has been used.The other thing to consider is what the field order in the index is. If your command is "Select FieldA from Table where FieldB = 5 and FieldC = 6" and your index is on "FiledA, FieldB, FieldC" then the fact that the index exists for the three fields really won't help. But if the index is "FieldB, FieldC, FieldA" then you'd be in business. Hope it helps,Dalton |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-04 : 06:07:46
|
| "But if the index is "FieldB, FieldC, FieldA" then you'd be in business"... and you should generally make the index either "FieldB, FieldC, FieldA" or "FieldC, FieldB, FieldA" depending on whether Field B [first example] or Field C [second example] is more "selective" - i.e. has more distinct values / fewer duplicate values.Kristen |
|
|
|
|
|
|
|
|
execution plan