SQL Server Performance Forum – Threads Archive

Script to Test Disk IO?

I am getting ready to "fight" with our SAN administrators as to whether we set up RAID-10 or RAID-5 for the database servers. You see, they already set up a 30-drive RAID-5 and are going to be giving pieces of it to servers on the IBM VMWare machines that are being set up. I am going to be setting up consolidated SQL servers and want to get as much performance out of them as possible so I can consolidate as many as possible into one VM, to save on licensing and help reduce maintenance. Right now I have about 70 SQL instances in total. I’ll be splitting them into a combination of DEV, TEST, and Production consolidated VMs. Anyway, I need to come up with a proposal for the production VM as to how I want the SAN set up. Obviously Disk IO is my biggest concern. Everything I read says a combination of RAID-1 and RAID-10 is ideal, they will disagree. I’d like to set up a test environment to test their RAID-5 versus a logically split out RAID-1 and RAID-10 configuration and then run a process that puts it through its paces to see which one is faster. I’m hoping there exist or someone would share a script (or a few) that would help me test the system to see just how well it will perform in each scenario. Any help greatly appreciated. Thanks,
Mike

comments: as dba, you must control your own disks, and not share disks with any one so get off the SAN and get direct attach storage for your DB’s don’t think about running performance critical db’s on vm try the following, I is just a seed table used to populate the test able CREATE TABLE I (I int NOT NULL , CONSTRAINT PK_I PRIMARY KEY CLUSTERED (I) ) GO SET NOCOUNT ON DECLARE @I int SELECT @I = 1 BEGIN TRANSACTION WHILE @I <= 100000 BEGIN INSERT I (I) VALUES ( @I) SET @I = @I+1 END COMMIT TRANSACTION CHECKPOINT GO CREATE TABLE C2 ( ID1 int NOT NULL, ID2 int NOT NULL, ID3 int NOT NULL, ID4 int NOT NULL, ID5 int NOT NULL, ID6 int NOT NULL, ID7 int NOT NULL, ID8 int NOT NULL, ID9 int NOT NULL, ID10 int NOT NULL, ID11 int NOT NULL, bID1 bigint NOT NULL, bID2 bigint NOT NULL, rMoney money NOT NULL, rDate datetime NOT NULL, rReal real NOT NULL, rDecimal decimal (9,4) NOT NULL, sID smallint NOT NULL, sChar char(700) NOT NULL, CONSTRAINT [PK_C2] PRIMARY KEY CLUSTERED ( [ID1] ) WITH FILLFACTOR = 100 ) GO below is the test table population script. it is important that you set @BEnd & @BTotal to the estimated total size of your databases in MB, if you expect 100GB make it 102400 SET NOCOUNT ON DECLARE @BStart int,@BEnd int,@BTotal int,@BSize int,@BRow int ,@T int,@I int,@C int,@P int,@M int ,@D2 int, @D3 int, @D4 int, @D5 int, @D6 int, @D7 int, @D8 int, @D9 int, @D10 int SELECT @M = 128 — 128 Pages per MByte SELECT @P = 10 — 10 rows per page, based on row size and fill factor SELECT @BStart=1, @BEnd=32768, @BTotal=32768, @[email protected]*@M, @C=10 SELECT @[email protected]*@BSize SELECT @D2=(@T-1)/@C+1, @D3=(@T-1)/20+1, @D4=(@T-1)/30+1, @D5=(@T-1)/50+1, @D6=(@T-1)/100+1 , @D7=(@T-1)/300+1, @D8=(@T-1)/1000+1 , @D9=(@T-1)/3000+1, @D10=(@T-1)/10000+1 WHILE @BStart <= @BEnd BEGIN BEGIN TRAN SELECT @I=(@BStart-1)*@BSize, @BRow = @BStart*@BSize INSERT C2(ID1,ID2,ID3,ID4,ID5,ID6,ID7,ID8,ID9,ID10,ID11,bID1,bID2,rMoney,rDate,rReal,rDecimal,sID,sChar) SELECT [email protected],([email protected])%@D2+1,([email protected])%@D3+1,([email protected])%@D4+1,([email protected])%@D5+1,([email protected])%@D6+1 , ([email protected])%@D7+1,([email protected])%@D8+1,([email protected])%@D9+1,([email protected])%@D10+1, [email protected] , 2*@T*RAND(CHECKSUM(NEWID())), @T*RAND(CHECKSUM(NEWID())),10000*RAND(CHECKSUM(NEWID())) , DATEADD(hour,150000*RAND(CHECKSUM(NEWID())),’1990-01-01′) , 10000*RAND(CHECKSUM(NEWID())), 10000*RAND(CHECKSUM(NEWID())), ([email protected])%320+1, CONVERT(char(100),NEWID()) FROM I WHERE I <= @BSize SET @BStart = @BStart+1 COMMIT TRAN IF (@BStart%10 -1=0) PRINT ‘C2 Step ‘ + CONVERT(varchar,GETDATE(),121) + ‘, ‘ + CONVERT(varchar,@BStart-1) + ‘, row ‘ + CONVERT(varchar,@BRow) + ‘, Trancount ‘ + CONVERT(varchar(10),@@TRANCOUNT) END GO CREATE INDEX IX_ID4 ON C2 (ID4,bID1) PRINT ‘I4 ‘ + CONVERT(varchar,GETDATE(),121) GO CREATE INDEX IX_ID6 ON C2 (ID6,bID1) PRINT ‘I6 ‘ + CONVERT(varchar,GETDATE(),121) GO CREATE INDEX IX_ID8 ON C2 (ID8,bID1) PRINT ‘I8 ‘ + CONVERT(varchar,GETDATE(),121) GO below are the 3 test queries, set to 30sec, (set the run time in milli-sec, currently at 30000) for low, medium and high queue depth read IO – disk read test SET NOCOUNT ON DECLARE @N int,@M int,@Group int,@Cnt int,@bID bigint,@DT1 datetime,@ms real,@CPU bigint,@IO bigint SELECT @M=MAX(ID4), @bID=COUNT(ID4) FROM C2 WITH (INDEX(IX_ID4)) SELECT @N=0, @bID=0, @Cnt=0, @DT1=getdate(), @[email protected]@CPU_BUSY, @[email protected]@IO_BUSY WHILE DATEDIFF(ms,@DT1,getdate()) < 30000 BEGIN SELECT @[email protected]*rand()+1, @[email protected]+1 SELECT @bID=SUM(bID2), @[email protected]+COUNT(*) FROM C2 WITH(NOLOCK) WHERE [email protected] END SELECT @ms=DATEDIFF(ms,@DT1,getdate()) SELECT @N [Calls], @ms [ms], CONVERT(real,@N)/@ms [Calls/sec], @Cnt/CONVERT(real,@N) [Rows/call] , @Cnt/@ms [K Rows/sec], (@@[email protected])*@@TIMETICKS/1000 [CPU], (@@[email protected])*@@TIMETICKS/1000 [IO] GO SET NOCOUNT ON DECLARE @N int,@M int,@Group int,@Cnt int,@bID bigint,@DT1 datetime,@ms real,@CPU bigint,@IO bigint SELECT @M=MAX(ID6), @bID=COUNT(ID6) FROM C2 WITH (INDEX(IX_ID6)) SELECT @N=0, @bID=0, @Cnt=0, @DT1=getdate(), @[email protected]@CPU_BUSY, @[email protected]@IO_BUSY WHILE DATEDIFF(ms,@DT1,getdate()) < 30000 BEGIN SELECT @[email protected]*rand()+1, @[email protected]+1 SELECT @bID=SUM(bID2), @[email protected]+COUNT(*) FROM C2 WITH(NOLOCK) WHERE [email protected] END SELECT @ms=DATEDIFF(ms,@DT1,getdate()) SELECT @N [Calls], @ms [ms], CONVERT(real,@N)/@ms [Calls/sec], @Cnt/CONVERT(real,@N) [Rows/call] , @Cnt/@ms [K Rows/sec], (@@[email protected])*@@TIMETICKS/1000 [CPU], (@@[email protected])*@@TIMETICKS/1000 [IO] GO SET NOCOUNT ON DECLARE @N int,@M int,@Group int,@Cnt int,@bID bigint,@DT1 datetime,@ms real,@CPU bigint,@IO bigint SELECT @M=MAX(ID<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @bID=COUNT(ID<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> FROM C2 WITH (INDEX(IX_ID<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) SELECT @N=0, @bID=0, @Cnt=0, @DT1=getdate(), @[email protected]@CPU_BUSY, @[email protected]@IO_BUSY WHILE DATEDIFF(ms,@DT1,getdate()) < 30000 BEGIN SELECT @[email protected]*rand()+1, @[email protected]+1 SELECT @bID=SUM(bID2), @[email protected]+COUNT(*) FROM C2 WITH(NOLOCK) WHERE [email protected] END SELECT @ms=DATEDIFF(ms,@DT1,getdate()) SELECT @N [Calls], @ms [ms], CONVERT(real,@N)/@ms [Calls/sec], @Cnt/CONVERT(real,@N) [Rows/call] , @Cnt/@ms [K Rows/sec], (@@[email protected])*@@TIMETICKS/1000 [CPU], (@@[email protected])*@@TIMETICKS/1000 [IO] GO table scan test: SET NOCOUNT ON DECLARE @N int,@M int,@Group int,@Cnt int,@bID bigint,@DT1 datetime,@ms real,@CPU bigint,@IO bigint SELECT @N=0, @bID=0, @Cnt=0, @DT1=getdate(), @[email protected]@CPU_BUSY, @[email protected]@IO_BUSY SELECT @[email protected]*rand()+1, @[email protected]+1 SELECT @bID=SUM(bID2), @[email protected]+COUNT(*) FROM C2 WITH(NOLOCK) WHERE [email protected] SELECT @ms=DATEDIFF(ms,@DT1,getdate()) SELECT @N [Calls], @ms [ms], CONVERT(real,@N)/@ms [Calls/sec], @Cnt/CONVERT(real,@N) [Rows/call] , @Cnt/@ms [K Rows/sec], (@@[email protected])*@@TIMETICKS/1000 [CPU], (@@[email protected])*@@TIMETICKS/1000 [IO] GO i will provide write queries after you provide some feedback

Joe: Thanks a ton! Well…Unfortunately I have no choice in the matter with regards to VM. At least unless I can prove that our VM environment can’t handle it, which I don’t think I’ll be able to do beyond a reasonable doubt. The VM environment was basically thrust on all of us by upper management; each VM host is an 8-way dual core IBM w/64G of RAM. So, I have to work with it, for better or worse. The troubling thing in my opinion is that it’ll be extremely difficult for me to troubleshoot resource issues if the VM Host is hit hard. Ultimately, though, if I can prove that the DB will harm other VMs on that host I’ll likely get dedicated hardware. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> My goal in this testing is really two fold…First of all, I want to prove that RAID-10 is better than RAID-5. Secondly, I’d love for the results to absolutely hammer the IO for the entire host VM to the SAN in general. This may or may not occur. We shall see. I plan to hit it hard, though. We have some SQL systems that generate a ton of disk IO, so me doing this testing isn’t unrealistic. In all honestly, in the back of my mind I’m working towards regaining full control of the hardware, which was the setup at my previous employer. I definitely don’t want to take over all responsibilities, but I think our DBA group should have final say-so with regards to all configurations and monitoring, whereas now the Server group has taken control of almost everything. We’ll see what happens I guess. Anyway, I’d definitely love to have the write queries to use as well. I’ll probably have an environment to start pounding on in a week or so. I’ll be putting in a requisition to set up the VMs with the drives the way I want soon and expect things to get interesting at that point. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Gotta love IT politics and power plays. Thanks and I’ll report back as I move forward. Mike

I’m not that much into hardware configuration, but maybe this one will give you some arguments against RAID 5:http://www.baarf.com And then there are:
http://www.microsoft.com/downloads/…53-893a-4aaf-b4a6-9a8bb9669a8b&DisplayLang=en
http://support.microsoft.com/default.aspx?scid=kb;EN-US;231619 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Frank: Excellent stuff there. I had forgotten about SQLIOStress even though I have seen it before. The BAARF thing is awesome. Great stuff. Thanks!
Mike

my inclination is to stay away from the RAID 5-10 argument i prefer a set of tests. The following events should not disrupt transactions:
1. nolock table scan (700MB/sec)
2. checkpoint (a massive dump of all dirty pages into the write queue)
3. t-log backup (mixing read & writes disrupting the pure sequential op)

Joe, thank you very much for providing your script. I modified it to capture test results run repeatedly (let’s say 10 times vs. a 40 GB C2 database) to get an average. Situation: we plan to upgrade one of our larger client’s 40 GB database server from SQL 2000 to SQL 2005, but we want to see if the existing hardware is up to the task. I am trying to understand the significance of the captured columnar values per your when I run it with SQL 2000 and SQL 2005 for a sort of apples-to-apples comparison. Here are some average run values over 10 executions: Type ElapsedTimeCallsRunMillisecRowsPerCallKBRowsPerSecCpuTimeIOTime C2Large Average 00:01:23 19 30,839 1000 0.62 29,735 490 C2Medium Average00:01:27 151 30,101 100 0.50 28,833 414 C2Small Average00:01:06 415 30,033 30 0.41 28,817 398 C2Scan Average00:08:55 1 535,173 0 0.00 94,846 31,456 It took over 3 hours to build C2 with no load on system ( IBM Server with 4 Hyperthreaded XEON 2.8 GHz, 8 GB RAM, RAID 5 10K rpm drives). I plan to run it on the same system with Windows 2003 SP1 SQL 2005 SP1 w/RAID 10 segregated into logical partitions by spindle for logs(1), data (2) and tempdb(1). Any help you can provide would be appreciated, Thank you, jim P.S. here is the supporting table & code: – – Here is the structure of C2Stats table that captures your select statements: – USE [StressTest] GO /****** Object: Table [dbo].[C2Stats] Script Date: 10/02/2006 16:54:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[C2Stats]( [C2StatsID] [int] IDENTITY(1,1) NOT NULL, [Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RunTime] [datetime] NOT NULL CONSTRAINT [DfC2StatsRunTime] DEFAULT (getdate()), [Calls] [int] NULL, [MS] [real] NULL, [CallsPerSec] [real] NULL, [RowsPerCall] [real] NULL, [KRowsPerSec] [real] NULL, [CPUTime] [bigint] NULL, [IOTime] [bigint] NULL, [GroupID] [int] NOT NULL CONSTRAINT [DF_C2Stats_Group] DEFAULT (0), CONSTRAINT [PkC2Stats] PRIMARY KEY CLUSTERED ( [C2StatsID] ASC )WITH FILLFACTOR = 99 ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF – – Here is the stored procedure for running tests — it generates 3 rows per group (start, your data, end) – USE [StressTest] GO /****** Object: StoredProcedure [dbo].[sqlrunstresstest] Script Date: 10/02/2006 16:55:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[sqlrunstresstest] as set nocount on – – Small disk read test – declare @groupid int select @groupid = max( groupid ) + 1 from dbo.c2stats with (nolock) insert into stresstest.dbo.c2stats ( type, runtime, groupid ) values ( ‘C2Small’, getdate(), @groupid ) declare @n int,@m int,@group int,@cnt int,@bid bigint,@dt1 datetime,@ms real,@cpu bigint,@io bigint select @m=max(id4), @bid=count(id4) from c2 with (index(ix_id4)) select @n=0, @bid=0, @cnt=0, @dt1=getdate(), @[email protected]@cpu_busy, @[email protected]@io_busy while datediff(ms,@dt1,getdate()) < 30000 begin [email protected][email protected]*rand()+1, @[email protected]+1 select @bid=sum(bid2), @[email protected]+count(*) from stresstest.dbo.c2 with (nolock) where [email protected] end select @ms=datediff(ms,@dt1,getdate()) insert into stresstest.dbo.c2stats ( type, calls, ms, callspersec, rowspercall, krowspersec, cputime, iotime, groupid ) select ‘C2Small’, @n [calls], @ms [ms], convert(real,@n)/@ms [calls/sec], @cnt/convert(real,@n) [rows/call], @cnt/@ms [k rows/sec], (@@[email protected])*@@timeticks/1000 [cpu], (@@[email protected])*@@timeticks/1000 [io], @groupid insert into stresstest.dbo.c2stats ( type, runtime, groupid ) values ( ‘C2Small’, getdate(), @groupid ) – – Medium disk read test – select @groupid = max( groupid ) + 1 from dbo.c2stats with (nolock) insert into stresstest.dbo.c2stats ( type, runtime, groupid ) values ( ‘C2Medium’, getdate(), @groupid ) select @m=max(id6), @bid=count(id6) from c2 with (index(ix_id6)) select @n=0, @bid=0, @cnt=0, @dt1=getdate(), @[email protected]@cpu_busy, @[email protected]@io_busy while datediff(ms,@dt1,getdate()) < 30000 begin [email protected][email protected]*rand()+1, @[email protected]+1 [email protected]=sum(bid2), @[email protected]+count(*) from c2 with(nolock) where [email protected] end select @ms=datediff(ms,@dt1,getdate()) insert into stresstest.dbo.c2stats ( type, calls, ms, callspersec, rowspercall, krowspersec, cputime, iotime, groupid ) select ‘C2Medium’, @n [calls], @ms [ms], convert(real,@n)/@ms [calls/sec], @cnt/convert(real,@n) [rows/call], @cnt/@ms [k rows/sec], (@@[email protected])*@@timeticks/1000 [cpu], (@@[email protected])*@@timeticks/1000 [io], @groupid insert into stresstest.dbo.c2stats ( type, runtime, groupid ) values ( ‘C2Medium’, getdate(), @groupid ) – – Large disk read test – select @groupid = max( groupid ) + 1 from dbo.c2stats with (nolock) insert into stresstest.dbo. c2stats ( type, runtime, groupid ) values ( ‘C2Large’, getdate(), @groupid ) select @m=max(id<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @bid=count(id<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> from c2 with (index(ix_id<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) select @n=0, @bid=0, @cnt=0, @dt1=getdate(), @[email protected]@cpu_busy, @[email protected]@io_busy while datediff(ms,@dt1,getdate()) < 30000 begin select @[email protected]*rand()+1, @[email protected]+1 select @bid=sum(bid2), @[email protected]+count(*) from c2 with(nolock) where [email protected] end select @ms=datediff(ms,@dt1,getdate()) insert into stresstest.dbo.c2stats ( type, calls, ms, callspersec, rowspercall, krowspersec, cputime, iotime, groupid ) select ‘C2Large’, @n [calls], @ms [ms], convert(real,@n)/@ms [calls/sec], @cnt/convert(real,@n) [rows/call], @cnt/@ms [k rows/sec], (@@[email protected])*@@timeticks/1000 [cpu], (@@[email protected])*@@timeticks/1000 [io], @groupid insert into stresstest.dbo.c2stats ( type, runtime, groupid ) values ( ‘C2Large’, getdate(), @groupid ) – – table scan test – select @groupid = max( groupid ) + 1 from dbo.c2stats with (nolock) insert into stresstest.dbo.c2stats ( type, runtime, groupid ) values ( ‘C2Scan’, getdate(), @groupid ) select @n=0, @bid=0, @cnt=0, @dt1=getdate(), @[email protected]@cpu_busy, @[email protected]@io_busy select @[email protected]*rand()+1, @[email protected]+1 select @bid=sum(bid2), @[email protected]+count(*) from c2 with(nolock) where [email protected] select @ms=datediff(ms,@dt1,getdate()) insert into stresstest.dbo.c2stats ( type, calls, ms, callspersec, rowspercall, krowspersec, cputime, iotime, groupid ) select ‘C2Scan’, @n [calls], @ms [ms], convert(real,@n)/@ms [calls/sec], @cnt/convert(real,@n) [rows/call], @cnt/@ms [k rows/sec], (@@[email protected])*@@timeticks/1000 [cpu], (@@[email protected])*@@timeticks/1000 [io], @groupid insert into stresstest.dbo.c2stats ( type, runtime, groupid ) values ( ‘C2Scan’, getdate(), @groupid ) set nocount off – – Here is the view of the results: – USE [StressTest] GO /****** Object: View [dbo].[C2StatInfo] Script Date: 10/02/2006 16:56:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [dbo].[C2StatInfo] as select d.groupid, d.Type, t.elapsedtime, d.runtime, d.calls, d.ms, d.callspersec, d.rowspercall, d.krowspersec, d.cputime, d.iotime from (select groupid, Type, runtime, calls, ms, callspersec, rowspercall, krowspersec, cputime, iotime from dbo.C2Stats where calls is not null ) as d inner join (select groupid , max( runtime ) – min( runtime ) as elapsedtime from dbo.C2Stats where calls is null group by groupid ) as t on d.groupid = t.groupid – – The query – use stresstest go select Type, runtime as RunDate, ElapsedTime, Calls, ms as RunMillisec, RowsPerCall, krowspersec as KBRowsPerSec, CPUTime, IOTime from dbo.C2StatInfo order by groupid;

try the new scripts below to capture more important info on disk
i consider 4 rack of disk (10-15 disks per rack) the min for a 4 proc system, regardless of capacity considerations http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16995

]]>