Talk:SAN Performance Tuning with SQLIO

From SQLServerPedia

Jump to: navigation, search

This isn't only useful for SAN testing, it is helpful for configuring DAS as well. Another parameter to think about adding to your logged data is the stripe size that was configured. (I put it in the SAN firmware field since I was testing DAS, and wasn't changing the firmware version on the RAID controller.)

Contents

Last Test Test Result Not Imported

After running this in SQL 2008 I've discovered that the stored proc doesn't pick up the last run's data.

Changing the INNER JOIN's condition from INNER JOIN dbo.sqlio_import impfulltest ON imp.rowid + 20 = impfulltest.rowid AND impfulltest.resulttext = " to INNER JOIN dbo.sqlio_import impfulltest ON imp.rowid + 19 = impfulltest.rowid fixes the problem. (A non-coding workaround is to append a blank record with the maximum ParameterRowId to dbo.sqlio_import before running the proc.) Another option is to add an INSERT statement at the top of the SP to create the blank line that is expected. (I modified the code to include the insert.)

Easier Way To Import

Rather than go through the Import wizard for each of your tests runs you could use BULK IMPORT to make it easier: 

--You only need to create the table once
CREATE TABLE [dbo].[SQLIO_Import_Step1](
	[ResultText] [varchar](max) NULL);
GO

TRUNCATE TABLE dbo.SQLIO_Import_Step1;

BULK INSERT dbo.SQLIO_Import_Step1
  FROM 'C:\Program Files (x86)\SQLIO\Results.txt';

TRUNCATE TABLE dbo.SQLIO_Import;

INSERT INTO dbo.SQLIO_Import
        (ResultText)
SELECT
	ISNULL(ResultText, '')
FROM dbo.SQLIO_Import_Step1;

Then you can execute the SP. (This could end up scrambling the order of the lines, but from my testing it didn't.)

The script.

Okay, The script was modified by SQLGuyChuck last December 19, 2009 (I'm assuming Chuck Lathrope). In it he gives an overhaul to the script:

  • Using 6 testfiles instead of 1.
  • redirecting output to hardcoded h: (as opposed to the method described in the video).
  • renaming SQLIO_1pass.bat to SANTest.bat
  • Where does "timeout /T 10" come from. It doesn't seem to be an SQLIO parameter or a dos command.

These improvements are fine maybe, but there's no, text or comments that explain the purpose of the changes. The video and everything around it become out of sync. The mismatch, among other things, makes me (and probably others) a little hesitant and wary about the info. I'm not sure why 6 files are better than one. What do the test results mean?

Personally I'm looking into the history of this article and plan to use the original script.

Parsing Error in SQL 2008 R2 CTP

Had to put a GO before the Stored Proc creation.

Retrieved from "http://sqlserverpedia.com/wiki/Talk:SAN_Performance_Tuning_with_SQLIO"