Friday, January 10, 2014

Importing SQLIO Data

In a previous post I shared with you how you might use an SSRS report to analyze your SQLIO results.  What I didn't share was how you might get that data into SQL Server to begin with, so I will share how I go about that.

[Update: You can get the RDL here]

The first thing you'll want to do is set up your SQLIO database, along with the related objects that you will need to import the results from the text file(s).  I have provided the necessary scripts below.  Upon creating those objects, there are a series of simple scripts you have to run to import the data.  Note: I did not write these scripts myself, but I did alter them a bit to include some information I felt necessary to do proper analysis:

USE SQLIO
GO

--STEP 1: import the data by changing to the appropriate txt file below.
TRUNCATE TABLE dbo.SQLIO_Import_Step1 ;

BULK INSERT dbo.SQLIO_Import_Step1
  FROM 'C:\Program Files (x86)\SQLIO\SQLIO_Results\results-timestamp.txt' ;

TRUNCATE TABLE dbo.SQLIO_Import ;

INSERT  INTO dbo.SQLIO_Import
        ( ResultText )
        SELECT  ISNULL(ResultText, '')
        FROM    dbo.SQLIO_Import_Step1 ;
     
--STEP 2: ETL the data into the live table for reporting purposes.    
EXECUTE [dbo].[USP_Import_SQLIO_TestPass] '[DATABASE INSTANCE]', 'D:', 2, 15000, 'RAID 10', '2014/1/10', 'EMC CX3 Model 20', '', 64, 'NTFS', '65536'

Tables needed:
CREATE TABLE [dbo].[SQLIO_Import]
    (
     [RowID] [int] IDENTITY(1,1) NOT NULL
    ,[ParameterRowID] [int] NULL
    ,[ResultText] [varchar](MAXNULL
    ,CONSTRAINT [PK_SQLIO_Import] PRIMARY KEY CLUSTERED ( [RowID] ASC )
    )

CREATE TABLE [dbo].[SQLIO_Import_Step1](
[ResultText] [varchar](MAXNULL
)

CREATE TABLE [dbo].[SQLIO_TestPass]
    (
     [TestPassID] [int] IDENTITY(1,1) NOT NULL
    ,[ServerName] [nvarchar](50) NOT NULL
    ,[LogicalDrive] [varchar](10) NOT NULL
    ,[DriveQty] [int] NOT NULL
    ,[DriveRPM] [int] NOT NULL
    ,[DriveRaidLevel] [nvarchar](10) NOT NULL
    ,[TestDate] [datetime] NOT NULL
    ,[SANmodel] [nvarchar](50) NOT NULL
    ,[SANfirmware] [nvarchar](50) NULL
    ,[PartitionOffset] [int] NULL
    ,[Filesystem] [nvarchar](50) NULL
    ,[FSClusterSizeBytes] [int] NULL
    ,[SQLIO_Version] [nvarchar](20) NULL
    ,[Threads] [int] NULL
    ,[ReadOrWrite] [nchar](1) NULL
    ,[DurationSeconds] [int] NULL
    ,[SectorSizeKB] [int] NULL
    ,[IOpattern] [nvarchar](50) NULL
    ,[IOsOutstanding] [int] NULL
    ,[Buffering] [nvarchar](50) NULL
    ,[FileSizeMB] [int] NULL
    ,[IOs_Sec] [decimal](18,0) NULL
    ,[MBs_Sec] [decimal](18,0) NULL
    ,[LatencyMS_Min] [int] NULL
    ,[LatencyMS_Avg] [int] NULL
    ,[LatencyMS_Max] [int] NULL
    ,CONSTRAINT [PK_SQLIO_TestPass] PRIMARY KEY CLUSTERED ( [TestPassID] ASC )
    )

Stored Procedure:
CREATE PROCEDURE [dbo].[USP_Import_SQLIO_TestPass]
    @ServerName NVARCHAR(50)
   ,@LogicalDrive VARCHAR(10)
   ,@DriveQty INT
   ,@DriveRPM INT
   ,@DriveRaidLevel NVARCHAR(10)
   ,@TestDate DATETIME
   ,@SANmodel NVARCHAR(50)
   ,@SANfirmware NVARCHAR(50)
   ,@PartitionOffset INT
   ,@Filesystem NVARCHAR(50)
   ,@FSClusterSizeBytes INT
AS
    SET nocount OFF

    IF @TestDate IS NULL
        SET @TestDate = GETDATE()

  /* Add a blank record to the end so the last test result is captured */
    INSERT  INTO dbo.SQLIO_Import
            ( ParameterRowID,ResultText )
    VALUES  ( 0,'' );
                             
  /* Update the ParameterRowID field for easier querying */
    UPDATE  dbo.sqlio_import
    SET     parameterrowid = ( SELECT TOP 1
                                        rowid
                               FROM     dbo.sqlio_import parm
                               WHERE    parm.resulttext LIKE '%\%'
                                        AND parm.rowid <= upd.rowid
                               ORDER BY rowid DESC
                             )
    FROM    dbo.sqlio_import upd
       
  /* Add new SQLIO_TestPass records from SQLIO_Import */
    INSERT  INTO dbo.sqlio_testpass
            (servername
            ,logicaldrive
            ,driveqty
            ,driverpm
            ,driveraidlevel
            ,testdate
            ,sanmodel
            ,sanfirmware
            ,partitionoffset
            ,filesystem
            ,fsclustersizebytes
            ,sqlio_version
            ,threads
            ,readorwrite
            ,durationseconds
            ,sectorsizekb
            ,iopattern
            ,iosoutstanding
            ,buffering
            ,filesizemb
            ,ios_sec
            ,mbs_sec
            ,latencyms_min
            ,latencyms_avg
            ,latencyms_max
            )
            SELECT  @ServerName
                   ,@LogicalDrive
                   ,@DriveQty
                   ,@DriveRPM
                   ,@DriveRaidLevel
                   ,@TestDate
                   ,@SANmodel
                   ,@SANfirmware
                   ,@PartitionOffset
                   ,@Filesystem
                   ,@FSClusterSizeBytes
                   ,( SELECT    REPLACE(resulttext,'sqlio ','')
                      FROM      dbo.sqlio_import impsqlio_version
                      WHERE     imp.rowid + 1 = impsqlio_version.rowid
                    ) AS sqlio_version
                   ,( SELECT    LEFT(resulttext,( CHARINDEX(' threads',resulttext) ))
                      FROM      dbo.sqlio_import impthreads
                      WHERE     imp.rowid + 3 = impthreads.rowid
                    ) AS threads
                   ,( SELECT    UPPER(SUBSTRING(resulttext,( CHARINDEX('threads ',resulttext) ) + 8,1))
                      FROM      dbo.sqlio_import impreadorwrite
                      WHERE     imp.rowid + 3 = impreadorwrite.rowid
                    ) AS readorwrite
                   ,( SELECT    SUBSTRING(resulttext,( CHARINDEX(' for',resulttext) ) + 4,
                                          ( CHARINDEX(' secs ',resulttext) ) - ( CHARINDEX(' for',resulttext) ) - 4)
                      FROM      dbo.sqlio_import impdurationseconds
                      WHERE     imp.rowid + 3 = impdurationseconds.rowid
                    ) AS durationseconds
                   ,( SELECT    SUBSTRING(resulttext,7,( CHARINDEX('KB',resulttext) ) - 7)
                      FROM      dbo.sqlio_import impsectorsizekb
                      WHERE     imp.rowid + 4 = impsectorsizekb.rowid
                    ) AS sectorsizekb
                   ,( SELECT    SUBSTRING(resulttext,( CHARINDEX('KB ',resulttext) ) + 3,
                                          ( CHARINDEX(' IOs',resulttext) ) - ( CHARINDEX('KB ',resulttext) ) - 3)
                      FROM      dbo.sqlio_import impiopattern
                      WHERE     imp.rowid + 4 = impiopattern.rowid
                    ) AS iopattern
                   ,( SELECT        SUBSTRING(resulttext,( CHARINDEX('with ',resulttext) ) + 5,
                                          ( CHARINDEX(' outstanding',resulttext) ) - ( CHARINDEX('with ',resulttext) )
                                          - 5)
                      FROM      dbo.sqlio_import impiosoutstanding
                      WHERE     imp.rowid + 5 = impiosoutstanding.rowid
                    ) AS iosoutstanding
                   ,( SELECT    REPLACE(CAST(resulttext AS NVARCHAR(50)),'buffering set to ','')
                      FROM      dbo.sqlio_import impbuffering
                      WHERE     imp.rowid + 6 = impbuffering.rowid
                    ) AS buffering
                   ,( SELECT    SUBSTRING(resulttext,( CHARINDEX('size: ',resulttext) ) + 6,
                                          ( CHARINDEX(' for ',resulttext) ) - ( CHARINDEX('size: ',resulttext) ) - 9)
                      FROM      dbo.sqlio_import impfilesizemb
                      WHERE     imp.rowid + 7 = impfilesizemb.rowid
                    ) AS filesizemb
                   ,( SELECT    RIGHT(resulttext,( LEN(resulttext) - 10 ))
                      FROM      dbo.sqlio_import impios_sec
                      WHERE     imp.rowid + 11 = impios_sec.rowid
                    ) AS ios_sec
                   ,( SELECT    RIGHT(resulttext,( LEN(resulttext) - 10 ))
                      FROM      dbo.sqlio_import impmbs_sec
                      WHERE     imp.rowid + 12 = impmbs_sec.rowid
                    ) AS mbs_sec
                   ,( SELECT    RIGHT(resulttext,( LEN(resulttext) - 17 ))
                      FROM      dbo.sqlio_import implatencyms_min
                      WHERE     imp.rowid + 14 = implatencyms_min.rowid
                    ) AS latencyms_min
                   ,( SELECT    RIGHT(resulttext,( LEN(resulttext) - 17 ))
                      FROM            dbo.sqlio_import implatencyms_avg
                      WHERE     imp.rowid + 15 = implatencyms_avg.rowid
                    ) AS latencyms_avg
                   ,( SELECT    RIGHT(resulttext,( LEN(resulttext) - 17 ))
                      FROM      dbo.sqlio_import implatencyms_max
                      WHERE     imp.rowid + 16 = implatencyms_max.rowid
                    ) AS latencyms_max
            FROM    dbo.sqlio_import imp
                    INNER JOIN dbo.sqlio_import impfulltest ON imp.rowid + 20 = impfulltest.rowid
                                                               AND impfulltest.resulttext = ''
            WHERE   imp.rowid = imp.parameterrowid
            ORDER BY imp.parameterrowid
         
  /* Empty out the ETL table */
    DELETE  dbo.sqlio_import