?
Solved

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

Posted on 2014-10-20
5
Medium Priority
?
165 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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 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