March 5, 2012 at 2:47 am
I'm currently doing a trial of some FusionIO cards. From my initial tests it all looks as good as I'd hoped with any IO intensive stuff being monstered (a 600GB restore for example taking 5 mins compared to our usual 3.5 hours).
What I am curious about is who else has been using these and if so, what has the experience been like ?
Thanks
Ryan
March 6, 2012 at 7:51 am
ryan.offord (3/5/2012)
I'm currently doing a trial of some FusionIO cards. From my initial tests it all looks as good as I'd hoped with any IO intensive stuff being monstered (a 600GB restore for example taking 5 mins compared to our usual 3.5 hours).What I am curious about is who else has been using these and if so, what has the experience been like ?
Thanks
Ryan
I have been espousing the use of FusionIO cards to clients since long before they were a house-hold name (and publicly traded company)! I have numerous clients on them and know of many others using them that I wasn't directly involved with. I have heard of some early-gen issues with reliability, but assuming you mirror them or use them in non-critical situations with a spare on hand that shouldn't be an issue. The IO performance is definitely all that and a bag-of-chips!! There are some very-high-end edge cases where your system can start to exhibit other issues such as deadlocking due to the exceptionally fast IO, but we are talking edge cases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2012 at 8:18 am
I have seen some who have implemented FusionIO in hopes of resolving all performance problems. Even though the performance is substantially better, some things will remain an issue (like deadlocks).
There will still need to tune and maintain.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
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
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
March 7, 2012 at 10:02 am
I'm using approx 250TB (30-40 servers) of fusion io at the moment and the performance as noted is great. Reliability not quite so. We've had occasional blue screens, database file errors and complete server failures. We've now got them mirrored and this has reduced a lot of the issues.
You've got bear in mind this is an exceptional number of fusion io cards so its not really a scare story. In all this totals around 3-5 servers across the estate so you will get some issues purely down to probability. So you can confirm that they arent as reliable as normal storage but it is improving as time goes.
March 7, 2012 at 10:03 am
Also worth noting that these servers werent the primary oltp servers. These are mostly scaled out servers through replication or logshipping. Most of them are load balanced so the issues are managable
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply