A short but good discussion about the RML comparison DIFF calculations.
From: Robert Dorr
Sent: Wednesday, January 26, 2011 10:21 AM
Subject: RE: MSDN Blogs: Contact request: RML Tools: Estimated Comparison Differences
Thanks for the question and feedback.
For example ProjectedCPUDiff is one of the columns in tblComparisonBatchPartialAggs
· The hash id generated by ReadTrace is the same in the Baseline and Comparison database for the same query text pattern.
· Look at the Baseline for the number of executions and total CPU burn and calculate the average per execution
· Look at the Comparison total CPU burn for the same query pattern
· Total Comparison Burn – (Avg Baseline burn * Comparison Executes)
You can see the actual formulas at work in the T-SQL procedure ReadTrace.usp_CreateComparisonData
set [ProjectedCPUDiff] = [c.TotalCPU] - (([b.TotalCPU]/[b.CompletedEvents]) * [c.CompletedEvents]),
[ProjectedReadsDiff] = [c.TotalReads] - (([b.TotalReads]/[b.CompletedEvents]) * [c.CompletedEvents]),
[ProjectedWritesDiff] = [c.TotalWrites] - (([b.TotalWrites]/[b.CompletedEvents]) * [c.CompletedEvents]),
[ProjectedDurationDiff]= [c.TotalDuration] - (([b.TotalDuration]/[b.CompletedEvents]) * [c.CompletedEvents])
where [b.HashID] is not null
and [c.HashID] is not null
Sent: Tuesday, January 25, 2011 6:01 PM
To: PSS SQL Bloggers
Subject: MSDN Blogs: Contact request: RML Tools: Estimated Comparison Differences
Subject: RML Tools: Estimated Comparison Differences
Hello, I have recently started to explore the RML tools for generating workloads and found it very straightforward to use due to the help documentation. However, one thing that is not clear to me is how the estimated values are obtained. For example, how are the values calculated for the "Estimated Comparison Differences" in the report table and how are these calculated: "Estimated CPU, Duration. Reads and Writes Diffs" Knowing this will help me derive meaningful conclusions from the report generated. Thank you. Simon
Bob Dorr - Principal SQL Server Escalation Engineer