• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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?
0
dvplayltd
Asked:
dvplayltd
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now