|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
ALTER PROCEDURE [dbo].[ShredIOStats] (@StatTxt VARCHAR(MAX)) AS /****************************************************************************** Take the IO statistics mess, and return the information in a grid. (You will need to double-up all of the single quotes from the IO results.) ******************************************************************************* MODIFICATION LOG ******************************************************************************* 14-MAY-2012 WGS Initial Creation. ******************************************************************************/ SELECT caGrp.Grp, TableName = MAX(caTbl.TableName), ScanCount = MAX(CASE WHEN s2.ItemNumber = 1 THEN caCount.Value ELSE NULL END), LogicalReads = MAX(CASE WHEN s2.ItemNumber = 2 THEN caCount.Value ELSE NULL END), PhysicalReads = MAX(CASE WHEN s2.ItemNumber = 3 THEN caCount.Value ELSE NULL END), ReadAheadReads = MAX(CASE WHEN s2.ItemNumber = 4 THEN caCount.Value ELSE NULL END), LobLogicalReads = MAX(CASE WHEN s2.ItemNumber = 5 THEN caCount.Value ELSE NULL END), LobPhysicalReads = MAX(CASE WHEN s2.ItemNumber = 6 THEN caCount.Value ELSE NULL END), LobReadAheadReads = MAX(CASE WHEN s2.ItemNumber = 7 THEN caCount.Value ELSE NULL END) FROM dbo.Split(@StatTxt, '.') s1 CROSS APPLY dbo.Split(s1.Item, ',') s2 CROSS APPLY (SELECT posTbl = CASE WHEN s1.ItemNumber % 2 = 0 THEN 0 ELSE CHARINDEX('''', s1.Item) END) caTbl1 CROSS APPLY (SELECT posTbl = CASE WHEN s1.ItemNumber % 2 = 0 THEN 0 ELSE CHARINDEX('''', s1.Item, caTbl1.posTbl+1) END) caTbl2 CROSS APPLY (SELECT TableName = CASE WHEN caTbl1.posTbl = 0 THEN NULL ELSE SUBSTRING(s1.Item, caTbl1.posTbl+1, caTbl2.posTbl - caTbl1.posTbl - 1) END) caTbl CROSS APPLY (SELECT caTbl1.posTbl+1, caTbl2.posTbl - caTbl1.posTbl -1) caPos(StartPos, Length) CROSS APPLY (SELECT Value = CASE WHEN s1.ItemNumber % 2 = 1 THEN NULL ELSE CONVERT(INT, SUBSTRING(s2.Item, PATINDEX('%[0-9]%', s2.Item), 50)) END) caCount CROSS APPLY (SELECT Grp = CASE WHEN s1.ItemNumber % 2 = 1 THEN s1.ItemNumber ELSE s1.ItemNumber - 1 END) caGrp GROUP BY caGrp.Grp HAVING MAX(caTbl.TableName) IS NOT NULL ORDER BY caGrp.Grp; |
(2017-2022)
- Comments (0)
- Trackbacks (0)
No one has commented yet.
No trackbacks yet.
