RML Read Trace Summary

use sqlnexus
go
SELECT ub.OrigText AS OrigQuery,
ub.NormText AS NormQuery
,bh1.HashID
, bh1.AvgDuration_MS
, bh1.AvgCPU_MS
, bh1.AvgReads
, bh1.AvgWrites
, bh1.Calls
, bh1.SumCPU
, bh1.SumDuration
, bh1.SumReads
, bh1.SumWrites

  ,  100. * SUM(calls) / SUM(SUM(calls)) OVER() AS PctofTotalCalls,
ROW_NUMBER() OVER(ORDER BY SUM(calls) DESC) AS TotalCalls_rank


 ,  100. * SUM(calls*AvgDuration_MS) / SUM(SUM(calls*AvgDuration_MS)) OVER() AS PctofTotalDur,
  ROW_NUMBER() OVER(ORDER BY SUM(calls*AvgDuration_MS) DESC) AS TotalDuration_rank
 
  ,  100. * SUM(calls*avgreads) / SUM(SUM(calls*avgreads)) OVER() AS PctofTotalReads,
  ROW_NUMBER() OVER(ORDER BY SUM(calls*avgreads) DESC) AS TotalReads_rank
 
,  100. * SUM(calls*AvgWrites) / SUM(SUM(calls*AvgWrites)) OVER() AS PctofTotalWrites,
  ROW_NUMBER() OVER(ORDER BY SUM(calls*AvgWrites) DESC) AS TotalWrites_rank
 
  ,  100. * SUM(calls*AvgCPU_MS) / SUM(SUM(calls*AvgCPU_MS)) OVER() AS PctofTotalCpu,
  ROW_NUMBER() OVER(ORDER BY SUM(calls*AvgCPU_MS) DESC) AS TotalCPU_rank

--into SqlNexus_Awe0227

FROM (
SELECT ub.HashID, AVG(b.Duration)/1000.00 AS AvgDuration_MS, AVG(b.Reads) AS AvgReads, AVG(b.Writes) AS AvgWrites, AVG(b.CPU) AS AvgCPU_MS, COUNT(1) AS Calls, Sum(b.Duration) AS SumDuration, sum(b.Reads) AS SumReads, Sum(b.Writes) AS SumWrites, Sum(b.CPU) AS SumCPU
FROM ReadTrace.tblUniqueBatches AS ub
INNER JOIN ReadTrace.tblBatches AS b
ON ub.HashID = b.HashID
--where DBID in (599)
GROUP BY ub.HashID
) AS bh1
INNER JOIN ReadTrace.tblUniqueBatches AS ub
ON bh1.HashID = ub.HashID
group by ub.OrigText
,ub.NormText
,bh1.HashID
, bh1.AvgDuration_MS
,bh1.AvgCPU_MS
, bh1.AvgReads
, bh1.AvgWrites
, bh1.Calls
, bh1.SumCPU
, bh1.SumDuration
, bh1.SumReads
, bh1.SumWrites

order by SumDuration desc