March 18, 2015 at 3:24 pm
Greetings,
I have been using actual execution plans & Database Engine Tuning Advisor to analyze performance. My questions pertain to interpreting exactly what Database Engine Tuning Advisor is telling me. When I examine the tuning report "Statement Detail Report", what exactly does the "statement cost" value measure? Is that a measure of CPU usage/time, etc.?
I am examining the measures given for various function & procedure calls, as compared to those same function calls refactored to fit a new database design. The new database design is more generic, but the original functions seem to be much more efficient. Refactoring them to use the new database structure, requires more joins, & no matter how I index or refactor the new functions, it seems like I can't come close to equaling performance of the original functions. Just wondering how much that matters. For instance, for one function, the (original) cost is approx. 0.05 but the revised-for-new-design function costs approx. 0.35 - 7x! Most of our original functions measure out in the less than 0.1 cost range, so it's only when it gets larger than that, that I take notice.
Maybe in the scheme of things, these are insignificant differences & "costs", that's why I'm asking what "statement cost" is telling me, & to what degree "costs" matter. Only a few of our functions or procedures cost 0.5 or greater (a few even "cost" in the 1.0-5.0 range; as I can, I'm investigating the more "costly" ones for potential index or refactoring improvements.) Since it's a web application, if there are things I can do to tune database components, I want to do so.
Thanks,
Randy
March 18, 2015 at 6:57 pm
rwitt 95744 (3/18/2015)
Greetings,I have been using actual execution plans & Database Engine Tuning Advisor to analyze performance. My questions pertain to interpreting exactly what Database Engine Tuning Advisor is telling me. When I examine the tuning report "Statement Detail Report", what exactly does the "statement cost" value measure? Is that a measure of CPU usage/time, etc.?
I am examining the measures given for various function & procedure calls, as compared to those same function calls refactored to fit a new database design. The new database design is more generic, but the original functions seem to be much more efficient. Refactoring them to use the new database structure, requires more joins, & no matter how I index or refactor the new functions, it seems like I can't come close to equaling performance of the original functions. Just wondering how much that matters. For instance, for one function, the (original) cost is approx. 0.05 but the revised-for-new-design function costs approx. 0.35 - 7x! Most of our original functions measure out in the less than 0.1 cost range, so it's only when it gets larger than that, that I take notice.
Maybe in the scheme of things, these are insignificant differences & "costs", that's why I'm asking what "statement cost" is telling me, & to what degree "costs" matter. Only a few of our functions or procedures cost 0.5 or greater (a few even "cost" in the 1.0-5.0 range; as I can, I'm investigating the more "costly" ones for potential index or refactoring improvements.) Since it's a web application, if there are things I can do to tune database components, I want to do so.
Thanks,
Randy
1) DTA is crap for tuning. Do NOT EVER take it's recommendations for indexing and just slap them into your system. It LOVES covering indexes and will not examine what you have already.
2) Cost of a query is just a unitless mathematical number that represents what the optimizer estimates for the plan. Every action performed has a cost, and the number of rows or iterations times that cost is the total for that section of the plan.
3) I hope by "functions" you mean stored procedures and not Scalar or Multi-statement Table Valued Functions!!! Those are TOTALLY HORRIBLE to use!!! If you have them, removing them becomes your top priority. See my chapter entitled Death by UDF in the SQL Server MVP 2 Deep Dives book. Oh, they aren't accurately represented by the optimizer either.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 18, 2015 at 9:10 pm
rwitt 95744 (3/18/2015)
Greetings,I have been using actual execution plans & Database Engine Tuning Advisor to analyze performance. My questions pertain to interpreting exactly what Database Engine Tuning Advisor is telling me. When I examine the tuning report "Statement Detail Report", what exactly does the "statement cost" value measure? Is that a measure of CPU usage/time, etc.?
I am examining the measures given for various function & procedure calls, as compared to those same function calls refactored to fit a new database design. The new database design is more generic, but the original functions seem to be much more efficient. Refactoring them to use the new database structure, requires more joins, & no matter how I index or refactor the new functions, it seems like I can't come close to equaling performance of the original functions. Just wondering how much that matters. For instance, for one function, the (original) cost is approx. 0.05 but the revised-for-new-design function costs approx. 0.35 - 7x! Most of our original functions measure out in the less than 0.1 cost range, so it's only when it gets larger than that, that I take notice.
Maybe in the scheme of things, these are insignificant differences & "costs", that's why I'm asking what "statement cost" is telling me, & to what degree "costs" matter. Only a few of our functions or procedures cost 0.5 or greater (a few even "cost" in the 1.0-5.0 range; as I can, I'm investigating the more "costly" ones for potential index or refactoring improvements.) Since it's a web application, if there are things I can do to tune database components, I want to do so.
Thanks,
Randy
The costs are all estimates even for the actual execution plan. Even % of batch is an estimate. Cost on the execution plans is not a reliable way to determine the winner of the proverbial footrace. It should only be used as a guide to where possible problems may lurk. Here's a prime example that I wrote several years ago for Grant Fritchey.
/****************************************************************************************
Hi Grant... I found that "worst" example of an execution plan example I was talking
about... take a look-see...
Both methods create 5 years worth of dates and dump them into a variable to take display
timings out of the picture. Run the code more than once... compare what the actual
execution plan says about percent of batch and then look at the messages window and see
the absolute worst case mistake that an execution plan can make... 100% backwards
compared to what actually happens.
--Jeff Moden
****************************************************************************************/
SET NOCOUNT ON
--=======================================================================================
-- Recursive method shown by (Name with-held)
--=======================================================================================
PRINT '========== Recursive method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @DateVal DATETIME
SET @DateVal = '2008-01-01'
;with mycte as
(
select @DateVal AS DateVal
union all
select DateVal + 1
from mycte
where DateVal + 1 < DATEADD(yy, 5, @DateVal)
)
select @Bitbucket = d.dateval
from mycte d
OPTION (MAXRECURSION 0)
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',90)
GO
--=======================================================================================
-- Tally table method by Jeff Moden
--=======================================================================================
PRINT '========== Tally table method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @StartDate AS DATETIME
SET @StartDate = '2008-01-01'
SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,5,@StartDate)))
@Bitbucket = @StartDate-1+t.N
FROM dbo.Tally t
ORDER BY N
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',90)
GO
If you run the code with the actual execution plan on, you see that the % of batch for the recursive CTE (written by someone else) comes in at 0% of batch and the Tally Table method comes in at 100% of batch. (You'll need to build a Tally Table. You can get the code for that from the article at http://www.sqlservercentral.com/articles/T-SQL/62867/).
The statistics tell a heck of a lot different story, though. In fact, almost the exact opposite of what the % of batch (which is based on the estimated cost of each query) is true.
========== Recursive method ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 2, logical reads 10964, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 19 ms.
==========================================================================================
========== Tally table method ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Tally'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
==========================================================================================
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2015 at 9:42 pm
Hi, Kevin.
Thanks for your response.
By functions, I mean table-valued functions (NOT multi-statement table valued functions.) We have a very small number of scalar functions & a very small number of multi-statement table-valued functions. But, 95% or more of our functions are the simple table-valued functions.
With execution plan & DTA index recommendations, as you mention, it's a starting point for examination, not something taken blindly. I will typically examine the potential effect of each recommended index, look at how complicated it is, if the column order makes sense, if it makes sense in that it could potentially improve multiple database components' performance, etc. Sometimes I will take a reasonable index as is, sometimes I will take some of the most relevant columns & check if that much makes enough of an improvement, most of the recommendations don't make enough of an impact to do anything with, or contain so many columns I'd pretty much be indexing the entire table structure.
I have found that sometimes DTA won't analyze something, but it will if I add a dummy statement that absolutely needs tuning, in the script I'm testing. Have you found a reliable way of determining performance impact, without using DTA & actual execution plans? Do you use statistics? I've used those sometimes, but I've needed to perform tasks many times b/c the results can vary significantly per execution.
Best wishes,
Randy
March 19, 2015 at 3:47 am
rwitt 95744 (3/18/2015)
Hi, Kevin.Thanks for your response.
By functions, I mean table-valued functions (NOT multi-statement table valued functions.) We have a very small number of scalar functions & a very small number of multi-statement table-valued functions. But, 95% or more of our functions are the simple table-valued functions.
With execution plan & DTA index recommendations, as you mention, it's a starting point for examination, not something taken blindly. I will typically examine the potential effect of each recommended index, look at how complicated it is, if the column order makes sense, if it makes sense in that it could potentially improve multiple database components' performance, etc. Sometimes I will take a reasonable index as is, sometimes I will take some of the most relevant columns & check if that much makes enough of an improvement, most of the recommendations don't make enough of an impact to do anything with, or contain so many columns I'd pretty much be indexing the entire table structure.
I have found that sometimes DTA won't analyze something, but it will if I add a dummy statement that absolutely needs tuning, in the script I'm testing. Have you found a reliable way of determining performance impact, without using DTA & actual execution plans? Do you use statistics? I've used those sometimes, but I've needed to perform tasks many times b/c the results can vary significantly per execution.
Best wishes,
Randy
I use execution stats often, because you want to see "TOTAL" effort of code (by reads, CPU, duration, whatever is important for you), not just one or a few executions worth. For that I like Glenn Berry's SQL Server Diagnostics Scripts if you are looking at what is in the cache currently.
Even better for my tuning efforts (which I have been doing on SQL Server for almost 20 years, mostly as a consultant) is Qure, an AWESOME free product from DBSophic.com.
I do use the missing indexes subsystem as a starting point as well, and you can find scripts online that will generate basic CREATE INDEX statements for you from it (note they do NOT provide any of the VERY important options for indexing). But I never, ever, EVER use DTA. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2015 at 11:40 pm
TheSQLGuru (3/18/2015)
1) DTA is crap for tuning. Do NOT EVER take it's recommendations for indexing and just slap them into your system.
One good thing that came from it for us, I spent a lot of time trying to build indexes to prevent a deadlock from occuring; I was following good logic and using a replay trace to confirm. I just couldn't get rid of it.
I handed off to my co-worker who just ran my replay through DTA for a day which came back with very slightly exact covering indexes which completely eliminated the deadlocks. Colour me surprised, in a way it was smarter than me. Shrug.
March 24, 2015 at 12:16 pm
Query cost is a baseline, where a SQL Server lab in Redmond was used to baseline the cost thousands of years ago.
What I do is use the numbers in this way.
one seek returning one page without lookup costs = 0.003.
Therefore, if a query costs 1000 units, then it is 333000 times more expensive than an index seek without lookup.
This gives you are real idea of how costly queries are. 0.003 being the best it can ever be.
I agree with SQLGuru.
I have just gone through an exercise of dropping a total of 40GB of DTA indexes from 5 DB's which used to have a total data size of 140GB.
People who didn't know better ran DTA and just plugged all the garbage into live, together with 165 hypothetical indexes and Statistics.
What should have happened is that the DBA should have factorized the indexes and come up with one or 2 per table which satisfies most of the queries with the lowest amount of the sum of (WeightedFactor1*scans+lookups+inserts+updates+deletes+WeightedFactor2*(row locks + page locks))
If DTA helped you in this case, keep it secret.
Rather speak of DTA as 'He who shall not be named'.
March 24, 2015 at 3:27 pm
I used to use the DTA back some years back but, for me, have gotten more bang for my buck by running DMV queries to find my longest running queries and must frequently run queries and starting there. At a few places I have worked we'll have daily, weekly and monthly reports that identify these.
We have a very small number of scalar functions & a very small number of multi-statement table-valued functions.
Which is better than having many but if they are used in a lot of queries then you have a lot of queries that are slower than they should be. Either way, I have found replacing Scalar and multi-statement table-valued functions with inline table valued functions to be low-hanging fruit in the world of performance tuning. Two of the more life-changing SQL techniques that I have learned on SQL Server Central is:
1) The Tally Table (as Jeff already discussed)
and...
2) The inline scalar valued function (see this article[/url], also by Jeff).
I have had a lot of success turning scalar valued functions into inline table valued functions. Some queries that ran for minutes or even hours would run in seconds or milliseconds. It's a tricky concept for some but it's worth the effort. Then changing multi-line table valued functions to inline TVFs - that's just a no brainer. There's an art to it depending on how complex the code is that you're converting but it's worth the trouble. Sometimes it's as simple as just removing the temp table and changing the return statement (many developers do multi-statement functions because they don't know any better). Other times it's a little more complex; either way it's not rocket science and changing mTVFs to iTVFs ALWAYS improves performance.
-- Itzik Ben-Gan 2001
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply