September 23, 2014 at 7:37 am
I have a server with 8 SSD Intel DC3500 in 2 arrays and 2 * Intel Xeon CPU E5-2620 0 @ 2.00GHz, 192 GN RAM, Windows 2012 og SQL 2012
when I run this script performance testing script it takes 16 sec.
IF OBJECT_ID (N'dbo.dataGeneration', N'U') IS NOT NULL
DROP TABLE dataGeneration;
CREATE TABLE dataGeneration (id integer);
SET NOCOUNT ON
DECLARE @counter integer
SET @counter = 0
WHILE @counter < 100000
BEGIN
SET @counter = @counter + 1
INSERT INTO dataGeneration VALUES (@counter)
END
When I run the same script on my desktop computer HP Z210 Workstation with 2 HHD and one Intel SSD 530 16 GB RAM. windows 8, SQL 2012 11.0.2100.60. It only takes 8 sec.
No disk queue on either systems. The activivty monitor looks fine. I think..
Is there any know issues with my setup? I have run SQLIO which also looks fine.
September 23, 2014 at 8:44 am
kst 61502 (9/23/2014)
I have a server with 8 SSD Intel DC3500 in 2 arrays and 2 * Intel Xeon CPU E5-2620 0 @ 2.00GHz, 192 GN RAM, Windows 2012 og SQL 2012when I run this script performance testing script it takes 16 sec.
IF OBJECT_ID (N'dbo.dataGeneration', N'U') IS NOT NULL
DROP TABLE dataGeneration;
CREATE TABLE dataGeneration (id integer);
SET NOCOUNT ON
DECLARE @counter integer
SET @counter = 0
WHILE @counter < 100000
BEGIN
SET @counter = @counter + 1
INSERT INTO dataGeneration VALUES (@counter)
END
When I run the same script on my desktop computer HP Z210 Workstation with 2 HHD and one Intel SSD 530 16 GB RAM. windows 8, SQL 2012 11.0.2100.60. It only takes 8 sec.
No disk queue on either systems. The activivty monitor looks fine. I think..
Is there any know issues with my setup? I have run SQLIO which also looks fine.
1) that's a silly way to test perf - onesy inserts. Use a table of numbers to do it instead, or a function that generates numbers from 1 to N (itzik ben-gan has one you can find with a search)
2) You have no explicit begin tran/commit tran, so the log buffer flushes KILL you here. Do that and see if it helps
3) What are the database size/growth fractions for both databases you are using. Size a database properly then see if it matters.
4) what is between those server SSDs and the CPUs on the server?
5) run sp_whoisactive during both runs and see what pops out
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 24, 2014 at 1:38 am
Well the insert script maybe silly but its still show me that there is a performance issues some how. Our developer use some bulk insert at the server is way slower that the desktop.
The SSDs is sitting on a Adaptec ASR7805 RAID controller. BIOS is full powered, WIndows 2012 (performance powerplan)
SQLIO says:
sqlio v1.5.SG
using system counter for latency timings, 1948434 counts per second
2 threads writing for 60 secs to file D:\TestFile.DAT
using 64KB random IOs
enabling multiple I/Os per thread with 12 outstanding
buffering set to not use file nor disk caches (as is SQL Server)
using current size: 10240 MB for file: D:\TestFile.DAT
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 13233.82
MBs/sec: 827.11
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 1
Max_Latency(ms): 12
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 28 29 30 12 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
September 24, 2014 at 4:51 am
13K IOPS at 64KB seems pretty good to me.
TheSQLGuru (9/23/2014)
2) You have no explicit begin tran/commit tran, so the log buffer flushes KILL you here. Do that and see if it helps
To reinforce Kevin's point here:
Without explicit begin/commit = 12 seconds on my laptop with SSD.
With (surrounding the WHILE loop) = 0.75 seconds
You say the SSD's are in 2 arrays - what RAID level are they both at? How are Data, Log, and TempDB files arranged on the arrays?
RAID can slow writes, on an SSD RAID'd up server (admittedly under load):
Without Begin/Tran = 18 seconds
With = 2 seconds
October 9, 2014 at 2:21 am
Thanx for reply
I agree the script is stupid but i still dont understand why my workstation with one consumer SSD is faster than the setup below. SQL settings are default.
Can I ask you to run the script below in this post and see how long it takes.
My server setup:
OS : Windows Server 2012 R2 [6.3 Build 9600] (x64) SQL 2014 latest patch
Adaptec 7805
2 x 240 GB i RAID 1 (C: System, Tempdb) (SSD)
4 x 800 GB i RAID 10 (E: Database (SSD)
2 x 240 GB i RAID 1 (F: Data02 til Logs) (SSD)
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 685.120 MB/s
Sequential Write : 777.155 MB/s
Random Read 512KB : 546.643 MB/s
Random Write 512KB : 706.512 MB/s
Random Read 4KB (QD=1) : 19.343 MB/s [ 4722.4 IOPS]
Random Write 4KB (QD=1) : 42.098 MB/s [ 10277.9 IOPS]
Random Read 4KB (QD=32) : 393.102 MB/s [ 95972.2 IOPS]
Random Write 4KB (QD=32) : 270.824 MB/s [ 66119.1 IOPS]
Test : 4000 MB [E: 0.0% (0.2/1490.0 GB)] (x9)
Date : 2014/10/06 16:59:25
OS : Windows Server 2012 R2 [6.3 Build 9600] (x64)
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 404.348 MB/s
Sequential Write : 282.654 MB/s
Random Read 512KB : 344.938 MB/s
Random Write 512KB : 282.977 MB/s
Random Read 4KB (QD=1) : 23.242 MB/s [ 5674.3 IOPS]
Random Write 4KB (QD=1) : 42.080 MB/s [ 10273.5 IOPS]
Random Read 4KB (QD=32) : 262.751 MB/s [ 64148.3 IOPS]
Random Write 4KB (QD=32) : 188.044 MB/s [ 45909.3 IOPS]
Test : 4000 MB [F: 0.1% (0.1/223.4 GB)] (x9)
Date : 2014/10/06 16:29:33
OS : Windows Server 2012 R2 [6.3 Build 9600] (x64)
IF OBJECT_ID (N'dbo.dataGeneration', N'U') IS NOT NULL
DROP TABLE dataGeneration;
CREATE TABLE dataGeneration (id integer);
SET NOCOUNT ON
DECLARE @counter integer
SET @counter = 0
WHILE @counter < 100000
BEGIN
SET @counter = @counter + 1
INSERT INTO dataGeneration VALUES (@counter)
END
Karsten
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply