I spend most of my work days creating stored procedures at work. One of the biggest problems when I signed on was that many of the stored procedures that already existed and new ones I created were suffering from slow loading times due to improper index creation or lack of indexes altogether.
In searching the net, I have not been able to find anything that speaks in clear non technical terms how to read each item in an execution plan to be able to properly add the right fields to an existing index or to create an index altogether.
Some of the items I encounter in execution plans that are common are:
Now from what I have learned on the job, when I hover over each of the items, I get a pop up with information. That information would include:
Object (usually a table.column being called)
Seek Predicates (These items go in the key lookup areas first in order as they appear)
Predicates (follow in order after the seek predicate list in the key lookups)
Output List (go in the included column list)
Is this all correct so far?
So typically, what I would do is if there is already an index that is already being used in the execution plan, I would add the fields that are shown in an object being scanned to the existing index if it is part of the same object, OR I could just amd have at times, just created a new index that has these new values added.
Also at times, I have used the tuning adviser to be able to get suggestions on performance tuning but at the same time, the recommendations almost never fully satisfy the load times and only some improvements are made. I also tried the free version of SQL Sentry to see results on queries I run but it has been of little help other than combining items to make the readability of the execution plan easier.
I think that I am missing information on how to do this properly because it takes me days sometimes to get a stored procedure to load fast and that goes with a lot of trial and error before I get lucky.
I have about 50 of these to go through and would hate to spend a day or more on each for something that others seem to understand and can remedy quickly. I am basically looking for someone who knows how to do this well and can help explain to me in understandable means how to take an execution plan and properly interpret what needs to be fixed, how to do it and how ultimately to get top performance out of my queries by doing so. IF samples are required, I can provide some to get through the exercise. Thanks in advance.