RE: FusionIO – SQLServerCentral

SSC-Insane

Points: 20039

March 7, 2012 at 9:43 am

If you're willing, I'd very much like to get a copy of the full results of Microsoft's SQLIO for the following test scenario (or a more comprehensive one, if you prefer).

Batch File with 20 minute sections; 16 hours total to run. At a guess, 10 minute (600 second) sections should also provide fairly consistent results, but I have seen inconsistencies with smaller amounts. I'll copy this and have YourFileTwo, YourFileThree, etc. to kick off a batch to run for a couple days on one drive setup after another (i.e. to compare FusionIO to local disk to local SSD to various SAN configs, each with their own drive letter/mount point).

Pipe the output to a log file when you run the batch file from the command prompt, like this:

YourBatchFile.bat >YourBatchFile_Date.log

Batch file:

rem 2 Outstanding IOs

sqlio -kW -s1200 -frandom -o2 -b8 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -frandom -o2 -b64 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o2 -b8 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o2 -b64 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o2 -b256 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o2 -b1024 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -frandom -o2 -b8 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -frandom -o2 -b64 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o2 -b8 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o2 -b64 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o2 -b256 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o2 -b1024 -LS -FYourFile timeout /T 1400

rem 8 Outstanding IOs

sqlio -kW -s1200 -frandom -o8 -b8 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -frandom -o8 -b64 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o8 -b8 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o8 -b64 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o8 -b256 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o8 -b1024 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -frandom -o8 -b8 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -frandom -o8 -b64 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o8 -b8 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o8 -b64 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o8 -b256 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o8 -b1024 -LS -FYourFile timeout /T 1400

rem 16 Outstanding IOs

sqlio -kW -s1200 -frandom -o16 -b8 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -frandom -o16 -b64 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o16 -b8 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o16 -b64 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o16 -b256 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o16 -b1024 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -frandom -o16 -b8 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -frandom -o16 -b64 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o16 -b8 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o16 -b64 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o16 -b256 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o16 -b1024 -LS -FYourFile timeout /T 1400

rem 32 Outstanding IOs

sqlio -kW -s1200 -frandom -o32 -b8 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -frandom -o32 -b64 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o32 -b8 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o32 -b64 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o32 -b256 -LS -FYourFile timeout /T 1400

sqlio -kW -s1200 -fsequential -o32 -b1024 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -frandom -o32 -b8 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -frandom -o32 -b64 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o32 -b8 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o32 -b64 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o32 -b256 -LS -FYourFile timeout /T 1400

sqlio -kR -s1200 -fsequential -o32 -b1024 -LS -FYourFile timeout /T 1400

Where YourFile is something like the following, but with the sizes adjusted to fill the entire FusionIO drive up to at least 90% of its total capacity.

#PathFilename ThreadsForThatFile Mask(use0x0) FileSizeInMB

d:\sqlio1.dat 1 0x0 153600

d:\sqlio2.dat 1 0x0 153600

d:\sqlio3.dat 1 0x0 153600

The results can be imported to SQL Server with something like the following very rough code, which is extremely valuable when you add in multiple SQLIO sessions, and when you fill out all the extra data fields for later reference:

USE tempdb; -- in case there is no YourDB DB

USE YourDB;

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- SQLIO_Staging_Stateless is a stateless staging table, wipe it out on each sqlio log file load

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[SQLIO_Staging_Stateless]') AND OBJECTPROPERTY(object_id, N'IsUserTable') = 1)

CREATE TABLE [dbo].[SQLIO_Staging_Stateless](

[RowID] [int] IDENTITY(1,1) NOT NULL,

[ResultText] [varchar](max) NULL,

CONSTRAINT [PK_SQLIO_Staging_Stateless] PRIMARY KEY CLUSTERED

(

[RowID] ASC

)

GO

-- SQLIO_Results is a STATEFUL table, full of prior results!!

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[SQLIO_Results]') AND OBJECTPROPERTY(object_id, N'IsUserTable') = 1)

CREATE TABLE [dbo].[SQLIO_Results](

[TestPassID] [int] IDENTITY(1,1) NOT NULL,

[ServerName] [varchar](255) NOT NULL,

[StorageName] [varchar](255) NOT NULL,

[ParameterFileName] [varchar](255) NOT NULL,

[DriveQty] [int] NOT NULL,

[DriveRPM] [int] NOT NULL,

[EachDriveSizeGB] [bigint] NOT NULL,

[DriveConnection] [VARCHAR] (50) NOT NULL,

[DriveRaidLevel] [char](2) NOT NULL,

[DriveRaidCfg] [VARCHAR] (255) NOT NULL,

[TestDate] [datetime] NOT NULL,

[SANmodel] [varchar](50) NOT NULL,

[SANfirmware] [varchar](50) NULL,

[HBAfirmware] [varchar](50) NULL,

[HBAdriver] [varchar](50) NULL,

[HBAconnection] [VARCHAR] (50) NULL,

[PartitionOffset] [int] NULL,

[Filesystem] [varchar](50) NULL,

[FSClusterSizeBytes] [int] NULL,

[SQLIOVersion] [varchar](20) NULL,

[Threads] [int] NULL,

[ReadOrWrite] [char](1) NULL,

[DurationSeconds] [int] NULL,

[IOSizeKB] [int] NULL,

[IOsOutstanding] [int] NULL,

[Buffering] [varchar](50) NULL,

[RandomOrSequential] [char] (1) NOT NULL,

[EachFileSizeMB] [int] NULL,

[NumberOfFiles] [int] NULL,

[WhereOnDrive] [VARCHAR](50) NULL,

[IOsPerSec] [decimal](18, 0) NULL,

[MBsPerSec] [decimal](18, 0) NULL,

[MinLatency] [int] NULL,

[AvgLatency] [int] NULL,

[MaxLatency] [int] NULL,

CONSTRAINT [PK_SQLIO_Results] PRIMARY KEY CLUSTERED

(

[TestPassID] ASC

)

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[YourNamingConvention_SQLIO_Results]') AND OBJECTPROPERTY(object_id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[YourNamingConvention_SQLIO_Results]

GO

CREATE PROCEDURE [dbo].[YourNamingConvention_SQLIO_Results]

@ServerName VARCHAR(255),

@StorageName VARCHAR(255),

@DriveQty INT = -1, -- -1 for UNKNOWN

@DriveRPM INT = -1, -- 0 for SSD, -1 for UNKNOWN

@EachDriveSizeGB BIGINT = -1, -- -1 for UNKNOWN

@DriveConnection VARCHAR(50), -- FC, SAS6Gbps, SATA, ATA, IDE, etc.

@DriveRaidLevel CHAR(2), -- VARCHAR to accomodate 01 vs 1, NA for not applicable

@DriveRaidCfg VARCHAR(255),

@TestDate DATETIME,

@SANmodel VARCHAR(50),

@SANfirmware VARCHAR(50),

@HBAfirmware VARCHAR(50),

@HBAdriver VARCHAR(50),

@HBAconnection VARCHAR(50), -- FC_4Gbps, iSCSI_10Gbps, FCoE_1Gbps, etc.

@PartitionOffset INT = -1, -- -1 for UNKNOWN

@Filesystem VARCHAR(50),

@FSClusterSizeBytes INT = -1, -- -1 for UNKNOWN

@EachFileSizeMB INT = -1, -- -1 for UNKNOWN or differing sizes

@NumberOfFiles INT = -1, -- -1 for UNKNOWN

@WhereOnDrive VARCHAR(50) -- 'BEGINNING', 'ENDING', 'MIDDLE', 'FRAGMENTED' etc.

AS

-- Originally From http://sqlserverpedia.com/wiki/SAN_YourDB_Tuning_with_SQLIO#Importing_SQLIO_Results_into_SQL_Server

-- Substantially redesigned to work off of WHERE statements instead of row offsets; the new technique succeeds regardless of how many test data files are in use.

/* SSIS manual one-time import: Right-click DB, Tasks->Import Data,

Flat File Source

Select the sqlio output file

Advanced, the data type for our one and only column, text stream

Next

SQL Server Native Client destination

Pick your DB

Next

Source Tables and Views

Destination [dbo].[SQLIO_Staging_Stateless]

Edit Mappings, Destination ResultText

Next

Finish

*/

/* Example

EXEC [dbo].[YourNamingConvention_SQLIO_Results]

@ServerName = 'YourServerName',

@StorageName = 'YourBigSASController',

@DriveQty = 6,

@DriveRPM = 0,

@EachDriveSizeGB = 333,

@DriveConnection = 'SATA 3Gbps',

@DriveRaidLevel = '5',

@DriveRaidCfg = '1x5+1 Stripe 64KB',

@TestDate = '20120307',

@SANmodel = 'n/a',

@SANfirmware = 'n/a',

@HBAfirmware = '66.33.0',

@HBAdriver = '2.11.17.64 storport 3.8.1312.82832',

@HBAconnection = 'SAS 3Gbps',

@PartitionOffset = 1024,

@Filesystem = 'NTFS',

@FSClusterSizeBytes = 64,

@EachFileSizeMB = 153600,

@NumberOfFiles = 3,

@WhereOnDrive = 'BEGINNING'

-- SELECT * FROM dbo.SQLIO_Results ORDER BY IOsPerSec DESC

-- SELECT * FROM dbo.SQLIO_Results WHERE ServerName = 'YourNewServer' AND TestDate = '20101010'

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'r' AND IOSizeKB = 8 AND IOsOutstanding = 8 AND RandomOrSequential = 'r' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'r' AND IOSizeKB = 8 AND IOsOutstanding = 8 AND RandomOrSequential = 's' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'r' AND IOSizeKB = 64 AND IOsOutstanding = 8 AND RandomOrSequential = 'r' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'r' AND IOSizeKB = 64 AND IOsOutstanding = 8 AND RandomOrSequential = 's' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'r' AND IOSizeKB = 256 AND IOsOutstanding = 8 AND RandomOrSequential = 's' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'w' AND IOSizeKB = 8 AND IOsOutstanding = 8 AND RandomOrSequential = 'r' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'w' AND IOSizeKB = 8 AND IOsOutstanding = 8 AND RandomOrSequential = 's' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'w' AND IOSizeKB = 64 AND IOsOutstanding = 8 AND RandomOrSequential = 'r' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'w' AND IOSizeKB = 64 AND IOsOutstanding = 8 AND RandomOrSequential = 's' ORDER BY IOsPerSec DESC

SELECT * FROM dbo.SQLIO_Results WHERE ReadOrWrite = 'w' AND IOSizeKB = 256 AND IOsOutstanding = 8 AND RandomOrSequential = 's' ORDER BY IOsPerSec DESC

SELECT RandomOrSequential AS RndOrSeq

, IOSizeKB

, ReadOrWrite

, IOsOutstanding

, IOsPerSec

, MBsPerSec

, ServerName

, StorageName

, DriveQty

, DriveRPM

, DriveRAIDLevel AS RAID

, DriveRaidCfg AS RAIDCfg

, EachDriveSizeGB AS DrvSizeGB

, ParameterFileName

, TestDate

, DurationSeconds

, MinLatency

, AvgLatency

, MaxLatency

, Buffering

, Threads

FROM YourDB.dbo.SQLIO_Results

ORDER BY RandomOrSequential

, IOSizeKB

, ReadOrWrite

, IOsOutstanding

, MBsPerSec DESC

*/

SET nocount off

IF @TestDate IS NULL

SET @TestDate = Getdate()

/* Add new SQLIO_Results records from SQLIO_Staging_Stateless */

INSERT INTO dbo.SQLIO_Results

(

-- IDENTITY [TestPassID]

[ServerName]

,[StorageName]

,[ParameterFileName]

,[DriveQty]

,[DriveRPM]

,[EachDriveSizeGB]

,[DriveConnection]

,[DriveRaidLevel]

,[DriveRaidCfg]

,[TestDate]

,[SANmodel]

,[SANfirmware]

,[HBAfirmware]

,[HBAdriver]

,[HBAconnection]

,[PartitionOffset]

,[Filesystem]

,[FSClusterSizeBytes]

,[SQLIOVersion]

,[Threads]

,[ReadOrWrite]

,[DurationSeconds]

,[IOSizeKB]

,[IOsOutstanding]

--,[Buffering]

,[RandomOrSequential]

,[EachFileSizeMB]

,[NumberOfFiles]

,[WhereOnDrive]

,[IOsPerSec]

,[MBsPerSec]

,[MinLatency]

,[AvgLatency]

,[MaxLatency]

)

SELECT

@ServerName

,@StorageName

,dvParmFile.ParmFile

,@DriveQty

,@DriveRPM

,@EachDriveSizeGB

,@DriveConnection

,@DriveRaidLevel

,@DriveRaidCfg

,@TestDate

,@SANmodel

,@SANfirmware

,@HBAfirmware

,@HBAdriver

,@HBAconnection

,@PartitionOffset

,@Filesystem

,@FSClusterSizeBytes

,(

SELECT RTRIM(LTRIM(RIGHT(InnerLevel1si.ResultText,LEN(InnerLevel1si.ResultText)-CHARINDEX('v',InnerLevel1si.ResultText))))

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MAX(InnerLevel2si.RowID) AS MaxRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE 'sqlio v%'

AND InnerLevel2si.RowID < si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MaxRowID = InnerLevel1si.RowID

) AS SQLIOVersion

,(

SELECT LEFT(InnerLevel1si.ResultText,CHARINDEX(' threads',InnerLevel1si.ResultText)-1)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE '%threads % for % secs%'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS Threads

,(

SELECT SUBSTRING(InnerLevel1si.ResultText,CHARINDEX(' threads ',InnerLevel1si.ResultText)+9,1)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE '%threads % for % secs%'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS ReadOrWrite

,(

SELECT SUBSTRING(InnerLevel1si.ResultText,CHARINDEX(' for ',InnerLevel1si.ResultText)+5,CHARINDEX(' secs ',InnerLevel1si.ResultText)-CHARINDEX(' for ',InnerLevel1si.ResultText)-5)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE '%threads % for % secs%'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS DurationSeconds

,(

SELECT SUBSTRING(InnerLevel1si.ResultText,CHARINDEX('using ',InnerLevel1si.ResultText)+6,CHARINDEX('KB ',InnerLevel1si.ResultText)-CHARINDEX('using ',InnerLevel1si.ResultText)-6)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE '%using % IOs'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS IOsizeKB

,(

SELECT SUBSTRING(InnerLevel1si.ResultText,CHARINDEX('with ',InnerLevel1si.ResultText)+5,CHARINDEX(' outstanding',InnerLevel1si.ResultText)-CHARINDEX('with ',InnerLevel1si.ResultText)-5)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE '%enabling multiple I/Os per thread with % outstanding'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS IOsOutstanding

,(

SELECT SUBSTRING(InnerLevel1si.ResultText,CHARINDEX('KB ',InnerLevel1si.ResultText)+3,1)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE '%using % IOs'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS RandOrSeq

,@EachFileSizeMB

,@NumberOfFiles

,@WhereOnDrive

,(

SELECT CAST(RIGHT(InnerLevel1si.ResultText,LEN(InnerLevel1si.ResultText)-CHARINDEX(':',InnerLevel1si.ResultText)) AS FLOAT)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE 'IOs/sec%'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS IOsPerSec

,(

SELECT CAST(RIGHT(InnerLevel1si.ResultText,LEN(InnerLevel1si.ResultText)-CHARINDEX(':',InnerLevel1si.ResultText)) AS FLOAT)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE 'MBs/sec%'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS MBsPerSec

,(

SELECT CAST(RIGHT(InnerLevel1si.ResultText,LEN(InnerLevel1si.ResultText)-CHARINDEX(':',InnerLevel1si.ResultText)) AS FLOAT)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE 'Min_Latency(ms):%'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS MinLatency

,(

SELECT CAST(RIGHT(InnerLevel1si.ResultText,LEN(InnerLevel1si.ResultText)-CHARINDEX(':',InnerLevel1si.ResultText)) AS FLOAT)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE 'Avg_Latency(ms):%'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS AvgLatency

,(

SELECT CAST(RIGHT(InnerLevel1si.ResultText,LEN(InnerLevel1si.ResultText)-CHARINDEX(':',InnerLevel1si.ResultText)) AS FLOAT)

FROM SQLIO_Staging_Stateless InnerLevel1si

INNER JOIN

(

SELECT MIN(InnerLevel2si.RowID) AS MinRowID

FROM SQLIO_Staging_Stateless InnerLevel2si

WHERE InnerLevel2si.ResultText LIKE 'Max_Latency(ms):%'

AND InnerLevel2si.RowID > si.RowID -- correlated subquery; poor performance, but at least the results are correct

) dvInner2

ON dvInner2.MinRowID = InnerLevel1si.RowID

) AS MaxLatency

FROM SQLIO_Staging_Stateless si

INNER JOIN

(

SELECT InnerLevel1si.RowID, RTRIM(LTRIM(RIGHT(InnerLevel1si.ResultText,LEN(InnerLevel1si.ResultText)-CHARINDEX(':',InnerLevel1si.ResultText)))) AS ParmFile

FROM SQLIO_Staging_Stateless InnerLevel1si

WHERE InnerLevel1si.ResultText LIKE 'parameter file used:%'

) dvParmFile

ON dvParmFile.RowID = si.RowID

ORDER BY si.RowID

/* Empty out the ETL staging table */

TRUNCATE TABLE dbo.SQLIO_Staging_Stateless

SET nocount off

GO