Assuming the query execution is slow , I have collected the actual execution plan. What should I look for in the actual execution plan . What are the top 10 things I need to watch out for?
I know this is a broad and generic question but I am looking for anyone who is experienced in query tuning to answer this question.
Thanks in Advance.
actually a great question, and it makes me wish i had saved some images to backup some examples. seeing is understanding in things like this.
I'll start adding things, and i know my peers will pitch in with more.
in no particular order, here's some i can think of
» in an actual execution plan, for any node, if the actual number of rows is orders of magnitude higher than the estimated number of rows, the statistics on the table being used in that node are out of date.
»Table Scan : if it exists,the table is a heap, and could benefit from adding a clustered index. clustered is always better.
»key lookup: an index was used to find a reference to get an additional column value: adding or modifying an existing index to INCLUDE that column could help.
» the obvious, in your face missing index statement needs to be reviewed; it might be correct, or it might be that an existing index could be tweaked to resolve the same query.
»if a scalar function is being used at all int he query, since they scale poorly on large numbers of rows.
»if a cursor is being used at all, it's most likely doing RBAR when a set based solution could do the same work orders of magnitude faster.
»Index Scan: if an index scan was used, theres probably not an index that helps the query well enough to do an index seek
»the output list for a node: if it's using an index, maybe adding an index with to match the WHERE, and which has the INCLUDE columns found in the list might help
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!