USE master
GO
IF OBJECT_ID('sp_StatsIO') IS NULL EXEC ('CREATE PROCEDURE sp_StatsIO AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_StatsIO @Input VARCHAR(MAX)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('tempdb..#sp_StatsIO') IS NOT NULL
DROP TABLE #sp_StatsIO
IF OBJECT_ID('tempdb..#pivot') IS NOT NULL
DROP TABLE #pivot
SELECT t.FieldNum AS LineNumber,
a.FieldNum,
LTRIM(REPLACE(a.Field, CHAR(10), '')) AS Field,
CASE WHEN t.Field LIKE '%(% row(s) affected)%' THEN 1
ELSE 0
END AS RowCol
INTO #sp_StatsIO
FROM Util.dbo.ParseDelimited(REPLACE(@Input, '''', ''), '
') t
CROSS APPLY Util.dbo.ParseDelimited(LTRIM(REPLACE(t.Field, CHAR(10), '')), '.') a
WHERE t.Field LIKE '%(% row(s) affected)%'
OR t.Field LIKE '%Table %'
AND LTRIM(REPLACE(a.Field, CHAR(10), '')) <> ''
--)
;
WITH RowsAffected
AS (SELECT a.LineNumber,
ROW_NUMBER() OVER (ORDER BY a.LineNumber ASC) AS Stmt,
CAST(REPLACE(REPLACE(Field, ' row(s) affected)', ''), '(', '') AS BIGINT) AS [rows]
FROM #sp_StatsIO A
WHERE RowCol = 1),
SQL01
AS (SELECT ISNULL(r.Stmt, 1) AS Stmt,
r.[rows],
CASE WHEN a.FieldNum = 1 THEN SUBSTRING(a.Field, 7, 256)
ELSE a.Field
END AS Field,
a.LineNumber,
a.FieldNum
FROM #sp_StatsIO a
OUTER APPLY (SELECT TOP 1
*
FROM RowsAffected b
WHERE b.LineNumber > a.LineNumber
ORDER BY b.LineNumber ASC) r
WHERE RowCol = 0),
S02
AS (SELECT t1.Stmt,
t1.LineNumber AS Seq,
t1.[rows],
CASE WHEN t1.Field LIKE '#%'
AND LEN(t1.Field) = 128
THEN SUBSTRING(t1.Field, 1, 128 - 12 + 1 - PATINDEX('%[^_]%', SUBSTRING(REVERSE(t1.Field), 13, 128 - 12)))
ELSE t1.Field
END AS [table],
LTRIM(SUBSTRING(t3.Field, 1, LEN(t3.Field) - CHARINDEX(' ', REVERSE(t3.Field), 0))) AS TYPE,
CAST(REVERSE(SUBSTRING(REVERSE(t3.Field), 1, CHARINDEX(' ', REVERSE(t3.Field), 0) - 1)) AS BIGINT) AS VALUE
FROM SQL01 t1
INNER JOIN SQL01 t2 ON t1.LineNumber = t2.LineNumber
CROSS APPLY Util.dbo.ParseDelimited(t2.Field, ',') t3
WHERE t1.FieldNum = 1
AND t2.FieldNum = 2)
SELECT Stmt,
ROW_NUMBER() OVER (PARTITION BY Stmt ORDER BY Seq) AS Seq,
[rows],
[table],
[scan count],
[logical reads],
[physical reads],
[read-ahead reads],
[lob logical reads],
[lob physical reads],
[lob read-ahead reads],
SUM([logical reads]) OVER (PARTITION BY Stmt) AS [logical reads stmt]
INTO #pivot
FROM (SELECT Stmt, Seq, [rows], [table], TYPE, VALUE FROM S02) p PIVOT
( SUM(VALUE) FOR TYPE IN ([scan count], [logical reads], [physical reads], [read-ahead reads], [lob logical reads], [lob physical reads], [lob read-ahead reads]) ) AS pvt
DECLARE @logicalreads BIGINT,
@TableCount INT,
@TableList VARCHAR(MAX),
@StatsId INT
SELECT @logicalreads = SUM([logical reads]),
@TableCount = COUNT(DISTINCT [table]),
@TableList = Util.dbo.StringConcat(DISTINCT [table], ', ')
FROM #pivot
INSERT TEMPORARY.Stats.StatsIO
(UserName,
DateAdded,
TableCount,
TableList)
SELECT SUSER_NAME() AS UserName,
GETDATE() AS DateAdded,
@TableCount AS TableCount,
@TableList AS TableList
SET @StatsId = SCOPE_IDENTITY() ;
WITH S04
AS (SELECT '1 Batch Sum' AS [BreakDown],
COUNT(DISTINCT Stmt) AS Stmt,
COUNT(Seq) AS Seq,
(SELECT SUM ([rows]) FROM (SELECT [rows] FROM #pivot GROUP BY Stmt, [rows]) kk) AS [rows],
'' AS [table],
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],
100.0 AS [logical reads%],
NULL AS [logical reads stmt item%],
NULL AS [logical reads stmt%],
1 AS [BreakDownId],
1 AS [o2],
1 AS [o3]
FROM #pivot
UNION ALL
SELECT '2 -- Stmt Sum' AS [BreakDown],
Stmt,
COUNT(Seq) AS Seq,
MAX([rows]) AS [rows],
'' AS [table],
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],
CASE WHEN @logicalreads > 0 THEN SUM([logical reads]) * 100.0 / @logicalreads
ELSE NULL
END AS [logical reads%],
NULL AS [logical reads stmt item%],
NULL AS [logical reads stmt%],
3 AS [BreakDownId],
Stmt AS [o2],
1 AS [o3]
FROM #pivot
GROUP BY Stmt
UNION ALL
SELECT '3 Table Sum' AS [BreakDown],
COUNT(DISTINCT Stmt) AS Stmt,
COUNT(Seq) AS Seq,
SUM([rows]) AS [rows],
[table],
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],
CASE WHEN @logicalreads > 0 THEN SUM([logical reads]) * 100.0 / @logicalreads
ELSE NULL
END AS [logical reads%],
NULL AS [logical reads stmt item%],
NULL AS [logical reads stmt%],
5 AS [BreakDownId],
-1 * SUM([logical reads]) AS [o2],
1 AS [o3]
FROM #pivot
GROUP BY [table]
UNION ALL
SELECT '4 -- Stmt Det' AS [BreakDown],
Stmt,
Seq,
[rows],
[table],
[scan count],
[logical reads],
[physical reads],
[read-ahead reads],
[lob logical reads],
[lob physical reads],
[lob read-ahead reads],
CASE WHEN @logicalreads > 0 THEN [logical reads] * 100.0 / @logicalreads
ELSE NULL
END AS [logical reads%],
CASE WHEN [logical reads stmt] > 0 THEN [logical reads] * 100.0 / [logical reads stmt]
ELSE NULL
END AS [logical reads stmt item%],
CASE WHEN @logicalreads > 0 THEN [logical reads stmt] * 100.0 / @logicalreads
ELSE NULL
END AS [logical reads stmt%],
7 AS [BreakDownId],
Stmt AS [o2],
-1 * [logical reads] AS [o3]
FROM #pivot
UNION ALL
SELECT '--------------------' AS [BreakDown],
0 AS Stmt,
0 AS Seq,
NULL AS [rows],
'--------------------' AS [table],
NULL AS [scan count],
NULL AS [logical reads],
NULL AS [physical reads],
NULL AS [read-ahead reads],
NULL AS [lob logical reads],
NULL AS [lob physical reads],
NULL AS [lob read-ahead reads],
NULL AS [logical reads%],
NULL AS [logical reads stmt item%],
NULL AS [logical reads stmt%],
2 AS [BreakDownId],
1 AS [o2],
1 AS [o3]
UNION ALL
SELECT '--------------------' AS [BreakDown],
0 AS Stmt,
0 AS Seq,
NULL AS [rows],
'--------------------' AS [table],
NULL AS [scan count],
NULL AS [logical reads],
NULL AS [physical reads],
NULL AS [read-ahead reads],
NULL AS [lob logical reads],
NULL AS [lob physical reads],
NULL AS [lob read-ahead reads],
NULL AS [logical reads%],
NULL AS [logical reads stmt item%],
NULL AS [logical reads stmt%],
4 AS [BreakDownId],
1 AS [o2],
1 AS [o3]
UNION ALL
SELECT '--------------------' AS [BreakDown],
0 AS Stmt,
0 AS Seq,
NULL AS [rows],
'--------------------' AS [table],
NULL AS [scan count],
NULL AS [logical reads],
NULL AS [physical reads],
NULL AS [read-ahead reads],
NULL AS [lob logical reads],
NULL AS [lob physical reads],
NULL AS [lob read-ahead reads],
NULL AS [logical reads%],
NULL AS [logical reads stmt item%],
NULL AS [logical reads stmt%],
6 AS [BreakDownId],
1 AS [o2],
1 AS [o3])
INSERT INTO [Temporary].[Stats].[StatsIODetail]
([StatsId],
[RowId],
[BreakDownId],
[BreakDown],
[stmt],
[item],
[table],
[rows],
[scan count],
[logical reads],
[logical reads%],
[logical reads stmt%],
[logical reads stmt item%],
[physical reads],
[read-ahead reads],
[lob logical reads],
[lob physical reads],
[lob read-ahead reads])
SELECT @StatsId AS StatsId,
ROW_NUMBER() OVER (ORDER BY [BreakDownId], [o2], [o3], [table]) AS RowId,
[BreakDownId],
[BreakDown],
[stmt],
Seq AS [item],
[table],
[rows],
[scan count],
[logical reads],
[logical reads%],
[logical reads stmt%],
[logical reads stmt item%],
[physical reads],
[read-ahead reads],
[lob logical reads],
[lob physical reads],
[lob read-ahead reads]
FROM S04
--SELECT [BreakDown], stmt, Seq as [item], [table], [rows], [scan count], [logical reads], CAST([logical reads%] AS NUMERIC(10,2)) AS [logical reads%], CAST([logical reads stmt%] AS NUMERIC(10,2)) AS [logical reads stmt%], CAST([logical reads stmt item%] AS NUMERIC(10,2)) AS [logical reads stmt item%], [physical reads], [read-ahead reads], [lob logical reads], [lob physical reads], [lob read-ahead reads], @StatsId AS StatsId, [BreakDownId],
--ROW_NUMBER() OVER(ORDER BY [BreakDownId], [o2], [o3], [table]) AS RowId
--INTO Temporary.dbo.StatsIODetail
--FROM S04
SELECT [BreakDown],
[stmt],
[item],
[table],
[rows],
[scan count],
[logical reads],
[logical reads%],
[logical reads stmt%],
[logical reads stmt item%],
[physical reads],
[read-ahead reads],
[lob logical reads],
[lob physical reads],
[lob read-ahead reads],
[StatsId]
FROM [Temporary].[Stats].[StatsIODetail]
WHERE StatsId = @StatsId
GO
EXEC sys.sp_ms_marksystemobject sp_StatsIO
GO