USE master
GO
IF OBJECT_ID('dbo.sp_StatsIOComp') IS NULL EXEC('CREATE PROCEDURE dbo.sp_StatsIOComp AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_StatsIOComp
@StatsIdList VARCHAR(MAX) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @StatsIDTable TABLE(OrderID TINYINT, StatsId INT)
DECLARE @Rowcount INT
INSERT @StatsIDTable(OrderID, StatsId)
SELECT FieldNum, CAST(Field AS INT) AS StatsId
FROM Util.dbo.ParseDelimited(@StatsIdList, ',')
WHERE Field IS NOT NULL
SET @Rowcount = @@ROWCOUNT
IF @Rowcount = 0
BEGIN
INSERT @StatsIDTable(OrderID, StatsId)
SELECT ROW_NUMBER() OVER (ORDER BY StatsId) as OrderId, StatsId
FROM(SELECT TOP 2 StatsId FROM [Temporary].[Stats].[StatsIO] ORDER BY StatsId DESC) kk
END
ELSE IF @Rowcount = 1
BEGIN
INSERT @StatsIDTable(OrderID, StatsId)
SELECT
0 AS OrderID,
MAX(b.StatsId) as StatsId
FROM [Temporary].[Stats].[StatsIO] a INNER JOIN @StatsIDTable s ON a.StatsId = s.StatsId
INNER JOIN [Temporary].[Stats].[StatsIO] b ON b.StatsId < a.StatsId AND a.UserName = b.UserName
HAVING MAX(b.StatsId) IS NOT NULL
IF @@ROWCOUNT = 0
BEGIN
INSERT @StatsIDTable(OrderID, StatsId)
SELECT
0 AS OrderID,
MAX(b.StatsId) as StatsId
FROM [Temporary].[Stats].[StatsIO] a INNER JOIN @StatsIDTable s ON a.StatsId = s.StatsId
INNER JOIN [Temporary].[Stats].[StatsIO] b ON b.StatsId < a.StatsId
HAVING MAX(b.StatsId) IS NOT NULL
END
END
-- BATCH
SELECT i.[StatsId]
,'Batch' as [BreakDown]
,si.[stmt]
,CAST(CASE WHEN tot.[stmt] > 0 THEN si.[stmt] * 100.0 / tot.[stmt] ELSE NULL END AS NUMERIC(10,2)) AS [stmt %]
,si.[item]
,CAST(CASE WHEN tot.[item] > 0 THEN si.[item] * 100.0 / tot.[item] ELSE NULL END AS NUMERIC(10,2)) AS [item %]
--,si.[table]
,si.[rows]
,CAST(CASE WHEN tot.[rows] > 0 THEN si.[rows] * 100.0 / tot.[rows] ELSE NULL END AS NUMERIC(10,2)) AS [rows %]
,si.[scan count]
,CAST(CASE WHEN tot.[scan count] > 0 THEN si.[scan count] * 100.0 / tot.[scan count] ELSE NULL END AS NUMERIC(10,2)) AS [scan count %]
,si.[logical reads]
,CAST(CASE WHEN tot.[logical reads] > 0 THEN si.[logical reads] * 100.0 / tot.[logical reads] ELSE NULL END AS NUMERIC(10,2)) AS [logical reads %]
--,si.[logical reads%]
--,si.[logical reads stmt%]
--,si.[logical reads stmt item%]
,si.[physical reads]
,CAST(CASE WHEN tot.[physical reads] > 0 THEN si.[physical reads] * 100.0 / tot.[physical reads] ELSE NULL END AS NUMERIC(10,2)) AS [physical reads %]
,si.[read-ahead reads]
,CAST(CASE WHEN tot.[read-ahead reads] > 0 THEN si.[read-ahead reads] * 100.0 / tot.[read-ahead reads] ELSE NULL END AS NUMERIC(10,2)) AS [read-ahead reads %]
,si.[lob logical reads]
,CAST(CASE WHEN tot.[lob logical reads] > 0 THEN si.[lob logical reads] * 100.0 / tot.[lob logical reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob logical reads %]
,si.[lob physical reads]
,CAST(CASE WHEN tot.[lob physical reads] > 0 THEN si.[lob physical reads] * 100.0 / tot.[lob physical reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob physical reads %]
,si.[lob read-ahead reads]
,CAST(CASE WHEN tot.[lob read-ahead reads] > 0 THEN si.[lob read-ahead reads] * 100.0 / tot.[lob read-ahead reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob read-ahead reads %]
,id.UserName
,id.DateAdded
,id.TableCount
,id.TableList
FROM @StatsIDTable i LEFT OUTER JOIN [Temporary].[Stats].[StatsIODetail] si ON i.StatsId = si.StatsId AND si.BreakDownId = 1
LEFT OUTER JOIN [Temporary].[Stats].[StatsIO] id ON i.StatsId = id.StatsId
OUTER APPLY (SELECT
SUM([stmt]) AS [stmt]
,SUM([item]) AS [item]
,SUM([rows]) AS [rows]
,SUM([scan count]) AS [scan count]
,SUM([logical reads]) AS [logical reads]
,SUM([physical reads]) AS [physical reads]
,SUM([read-ahead reads]) AS [read-ahead reads]
,SUM([lob logical reads]) AS [lob logical reads]
,SUM([lob physical reads]) AS [lob physical reads]
,SUM([lob read-ahead reads]) AS [lob read-ahead reads]
FROM [Temporary].[Stats].[StatsIODetail]
WHERE StatsId IN(SELECT StatsId FROM @StatsIDTable)
AND BreakDownId = 1) tot
ORDER BY i.OrderID
-- TABLE
SELECT i.[StatsId]
,'Table' AS [BreakDown]
--,st.tableid
,st.[table]
,si.[stmt]
,CAST(CASE WHEN tot.[stmt] > 0 THEN si.[stmt] * 100.0 / tot.[stmt] ELSE NULL END AS NUMERIC(10,2)) AS [stmt %]
,si.[item]
,CAST(CASE WHEN tot.[item] > 0 THEN si.[item] * 100.0 / tot.[item] ELSE NULL END AS NUMERIC(10,2)) AS [item %]
,si.[rows]
,CAST(CASE WHEN tot.[rows] > 0 THEN si.[rows] * 100.0 / tot.[rows] ELSE NULL END AS NUMERIC(10,2)) AS [rows %]
,si.[scan count]
,CAST(CASE WHEN tot.[scan count] > 0 THEN si.[scan count] * 100.0 / tot.[scan count] ELSE NULL END AS NUMERIC(10,2)) AS [scan count %]
,si.[logical reads]
,CAST(CASE WHEN tot.[logical reads] > 0 THEN si.[logical reads] * 100.0 / tot.[logical reads] ELSE NULL END AS NUMERIC(10,2)) AS [logical reads %]
,si.[logical reads%] AS [logical reads of batch%]
--,si.[logical reads stmt%]
--,si.[logical reads stmt item%]
,si.[physical reads]
,CAST(CASE WHEN tot.[physical reads] > 0 THEN si.[physical reads] * 100.0 / tot.[physical reads] ELSE NULL END AS NUMERIC(10,2)) AS [physical reads %]
,si.[read-ahead reads]
,CAST(CASE WHEN tot.[read-ahead reads] > 0 THEN si.[read-ahead reads] * 100.0 / tot.[read-ahead reads] ELSE NULL END AS NUMERIC(10,2)) AS [read-ahead reads %]
,si.[lob logical reads]
,CAST(CASE WHEN tot.[lob logical reads] > 0 THEN si.[lob logical reads] * 100.0 / tot.[lob logical reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob logical reads %]
,si.[lob physical reads]
,CAST(CASE WHEN tot.[lob physical reads] > 0 THEN si.[lob physical reads] * 100.0 / tot.[lob physical reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob physical reads %]
,si.[lob read-ahead reads]
,CAST(CASE WHEN tot.[lob read-ahead reads] > 0 THEN si.[lob read-ahead reads] * 100.0 / tot.[lob read-ahead reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob read-ahead reads %]
FROM @StatsIDTable i
CROSS APPLY (
SELECT
[table],
ROW_NUMBER() OVER(ORDER BY [logical reads] DESC) AS tableid
FROM(SELECT
id.[table],
SUM([logical reads]) AS [logical reads]
FROM [Temporary].[Stats].[StatsIODetail] id
WHERE id.StatsId IN(SELECT StatsId FROM @StatsIDTable)
AND id.BreakDownId = 5
GROUP BY id.[table])kk)st
LEFT OUTER JOIN [Temporary].[Stats].[StatsIODetail] si ON i.StatsId = si.StatsId AND si.BreakDownId = 5 AND st.[table] = si.[table]
OUTER APPLY (SELECT
SUM(id.[stmt]) AS [stmt]
,SUM(id.[item]) AS [item]
,SUM(id.[rows]) AS [rows]
,SUM(id.[scan count]) AS [scan count]
,SUM(id.[logical reads]) AS [logical reads]
,SUM(id.[physical reads]) AS [physical reads]
,SUM(id.[read-ahead reads]) AS [read-ahead reads]
,SUM(id.[lob logical reads]) AS [lob logical reads]
,SUM(id.[lob physical reads]) AS [lob physical reads]
,SUM(id.[lob read-ahead reads]) AS [lob read-ahead reads]
FROM [Temporary].[Stats].[StatsIODetail] id
WHERE id.StatsId IN(SELECT StatsId FROM @StatsIDTable)
AND id.BreakDownId = 5
AND id.[table] = st.[table]) tot
ORDER BY st.tableid, i.OrderID
--STATEMENT
SELECT i.[StatsId]
,'Statement' AS [BreakDown]
,st.[stmt]
--,CAST(CASE WHEN tot.[stmt] > 0 THEN si.[stmt] * 100.0 / tot.[stmt] ELSE NULL END AS NUMERIC(10,2)) AS [stmt %]
,si.[item]
,CAST(CASE WHEN tot.[item] > 0 THEN si.[item] * 100.0 / tot.[item] ELSE NULL END AS NUMERIC(10,2)) AS [item %]
--,si.[table]
,si.[rows]
,CAST(CASE WHEN tot.[rows] > 0 THEN si.[rows] * 100.0 / tot.[rows] ELSE NULL END AS NUMERIC(10,2)) AS [rows %]
,si.[scan count]
,CAST(CASE WHEN tot.[scan count] > 0 THEN si.[scan count] * 100.0 / tot.[scan count] ELSE NULL END AS NUMERIC(10,2)) AS [scan count %]
,si.[logical reads]
,CAST(CASE WHEN tot.[logical reads] > 0 THEN si.[logical reads] * 100.0 / tot.[logical reads] ELSE NULL END AS NUMERIC(10,2)) AS [logical reads %]
,si.[logical reads%] as [logical reads of batch%]
--,si.[logical reads stmt%]
--,si.[logical reads stmt item%]
,si.[physical reads]
,CAST(CASE WHEN tot.[physical reads] > 0 THEN si.[physical reads] * 100.0 / tot.[physical reads] ELSE NULL END AS NUMERIC(10,2)) AS [physical reads %]
,si.[read-ahead reads]
,CAST(CASE WHEN tot.[read-ahead reads] > 0 THEN si.[read-ahead reads] * 100.0 / tot.[read-ahead reads] ELSE NULL END AS NUMERIC(10,2)) AS [read-ahead reads %]
,si.[lob logical reads]
,CAST(CASE WHEN tot.[lob logical reads] > 0 THEN si.[lob logical reads] * 100.0 / tot.[lob logical reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob logical reads %]
,si.[lob physical reads]
,CAST(CASE WHEN tot.[lob physical reads] > 0 THEN si.[lob physical reads] * 100.0 / tot.[lob physical reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob physical reads %]
,si.[lob read-ahead reads]
,CAST(CASE WHEN tot.[lob read-ahead reads] > 0 THEN si.[lob read-ahead reads] * 100.0 / tot.[lob read-ahead reads] ELSE NULL END AS NUMERIC(10,2)) AS [lob read-ahead reads %]
FROM @StatsIDTable i
CROSS APPLY (SELECT
DISTINCT id.[stmt]
FROM [Temporary].[Stats].[StatsIODetail] id
WHERE id.StatsId IN(SELECT StatsId FROM @StatsIDTable)
AND id.BreakDownId = 3) st
LEFT OUTER JOIN [Temporary].[Stats].[StatsIODetail] si ON i.StatsId = si.StatsId AND si.BreakDownId = 3 AND st.[stmt] = si.[stmt]
OUTER APPLY (SELECT
--SUM(id.[stmt]) AS [stmt]
SUM(id.[item]) AS [item]
,SUM(id.[rows]) AS [rows]
,SUM(id.[scan count]) AS [scan count]
,SUM(id.[logical reads]) AS [logical reads]
,SUM(id.[physical reads]) AS [physical reads]
,SUM(id.[read-ahead reads]) AS [read-ahead reads]
,SUM(id.[lob logical reads]) AS [lob logical reads]
,SUM(id.[lob physical reads]) AS [lob physical reads]
,SUM(id.[lob read-ahead reads]) AS [lob read-ahead reads]
FROM [Temporary].[Stats].[StatsIODetail] id
WHERE id.StatsId IN(SELECT StatsId FROM @StatsIDTable)
AND id.BreakDownId = 3
AND id.stmt = st.stmt) tot
ORDER BY st.stmt, i.OrderID
--STATEMENT DETAIL
SELECT id.[StatsId]
,'Statement Detail' AS [BreakDown]
,sd.[stmt]
,sd.[item]
,sd.[table]
,sd.[rows]
,sd.[scan count]
,sd.[logical reads]
,sd.[logical reads%]
,sd.[logical reads stmt%]
,sd.[logical reads stmt item%]
,sd.[physical reads]
,sd.[read-ahead reads]
,sd.[lob logical reads]
,sd.[lob physical reads]
,sd.[lob read-ahead reads]
FROM @StatsIDTable id LEFT OUTER JOIN [Temporary].[Stats].[StatsIODetail] sd ON sd.StatsId = id.StatsId AND sd.BreakDownId = 7
ORDER BY id.OrderId, sd.[RowId]
GO
IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID('dbo.sp_StatsIOComp')
AND sysstat & 0xf = 4)
GRANT EXEC ON dbo.sp_StatsIOComp TO PUBLIC
GO
EXEC sys.sp_ms_marksystemobject sp_StatsIOComp
GO