Solved

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

Posted on 2014-10-20
5
150 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
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
10x.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now