How to properly interpret Execution Plans in SQL Server 2008

Hello all,

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:

Key Lookups
Index Scans


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.

LVL 1
DB-ahaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil RussellTechnical Development LeadCommented:
In an Ideal world you would have an index that matched every field in each step of the exceution plan. But indexes cost! You have to weigh up the additional cost of maintaining the index vs the time saved*number of times used of the query step.
0
DB-ahaAuthor Commented:
In my current scenario, the most important thing is to have these stored procedures load in under 6 seconds, which some do.
0
Neil RussellTechnical Development LeadCommented:
Then Index away :D

But of course realise that the ORDER of the fields in your index matters as well. Also indexes are not the be all and end all of execution speed.
How many steps are there in the execution plan? It may be worth opening a wuestion for each plan and pasting it in here for individual question help.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott PletcherSenior DBACommented:
SQL gives you a relative cost for each step in the plan.  Naturally concentrate on the higher-cost steps.

Pay particular attention to "table scans" on large tables.  Or repeated lookups to a (large) table, such as a nested join with a large table(s).


>> 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. <<

Yeah, you need a DBA for that.  (Thank goodness! :-) )
0
DB-ahaAuthor Commented:
I am a Junior Database Developer trying to work my way up the ranks. This has been a blocking point for me for some time now.
0
Scott PletcherSenior DBACommented:
I'M NOT OBJECTING.

I agree with asker -- but i think this q is just too big and broad to cover in a forum like this.


>> (I) 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. <<


In (much) less than one day per query.

That's a very big request.  I don't think that can be done in primarily textual messages back and forth.

You would need to have someone sit down with you and train you.  And some queries could still require a day to tune (although it would be very rare for it to take that long, except for a complex query).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DB-ahaAuthor Commented:
This is clear. It is an ongoing practice and every situation is unique. Thanks for your response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.