Is there an absolute value of cost of T-SQL in Execution Plan?

Dear experts,

I have a complex SQL 2008 R2 Database that should be tuned. I try to add the indexes and with help of e Actual execution plan to check the result. But the problem is that, when I add a index, the execution plan is changed, for my case the KeyLookup value are reduced from 33 % to 19 %, but a new operation appear - with show that take other 20 %.  So I replace an operation with 33% for 2 operations with total cost of 40% - but this  new 40 % is a percentage from WHAT value  – what I need is a measure of total cost of whole T-SQL in some absolute value – like time for execution, CPU usage or other. Is there any value in Execution plan that I can use for that?
dvplayltdAsked:
Who is Participating?
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.

lcohanDatabase AnalystCommented:
If they do not contain confidential info can you post the SQL Plan?
From my experience index seek is ideal but even a index scan may be  way better than a (usually clustered) key lookup done because of a missing index. The operations - most likely the new merge/hash joins that replaced the 33% key lookups would be faster even though they may take now 40% or even more of the overall execution plan.

I would also use the execution time to compare before/after added index and use the
SET STATISTICS TIME ON
SET STATISTICS IO ON

to see the actual execution times and IO
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
dvplayltdAuthor Commented:
Hi lcohan

Thanks for your help. I can post the real execution plan, but it is too complex and my question is in general how to measure the result.

So when I put these commands in my stored procedure I'll get the execution time in miliseconds, right? This will be the answer I'm looking for.
0
dvplayltdAuthor Commented:
Hi lcohan

I see the messages of CPU useage in miliseconds, that is the answer I'm looking for.

Let me ask a last question before I give you the points. I have a test server, but with lot of data as will be in real customer and working in 24/7. For my convenience I backup the data from that server, then restored it on my local SQL Server on my DEV computer. Did I get real result with testing on my local SQL Server? Statictics used from SQL to optimize execution is lost in backup/restore proccess or not???

My idea is to make any test on my local and when I see result better than 10% in speed, then to make final changes. What you think?
0
lcohanDatabase AnalystCommented:
I would not 100% trust the results from another computer with different specs and settings as the execution plans are impacted a lot by the amount of RAM/CPU and overall SQL Edition, and SQL configurations.

For queries that do not DELETE/UPDATE data the best is to double check the query plan directly on the server as much as possible.

For queries that DO the DELETE/UPDATE against data with EXTREME caution - try to see at least the Estimated execution plan if possible.
0
dvplayltdAuthor Commented:
10x.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.