Solved

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

Posted on 2014-10-20
5
156 Views
Last Modified: 2014-10-20
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?
0
Comment
Question by:dvplayltd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 40392131
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
 

Author Comment

by:dvplayltd
ID: 40392214
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
 

Author Comment

by:dvplayltd
ID: 40392345
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
 
LVL 40

Expert Comment

by:lcohan
ID: 40392361
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
 

Author Closing Comment

by:dvplayltd
ID: 40392505
10x.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question