Unsolved
This post is more than 5 years old
23 Posts
0
1162
November 3rd, 2012 14:00
Poor SQL Performance?
Setup is as follows
VNX5300
2 x 9+1, 600GB 15K SAS configured in RAID Groups
1 x 9+1 600GB 15K SAS + 6+2 2TB SATA in Storage Pool
2 x 200GB SSD configured as Fast Cache
3 ESX hosts connected via 4GB FC (SAN is currently on 4GB FC as well)
SQL VM running Windows 2008 R2
4 vCPU
16GB RAM
40GB OS partition
3 x RDMs for User DBs, Logs and System DBs
Pathing Policy set to Round Robin
This server isn't in production yet but we ran SQLIO on it to benchmark it and we're fairly disappointed with the results. I would have thought with the EFDs the performance would be flying but apparently not. Any thoughts? Our DBA ran the SQLIO so I need to double check his configuration but here's the output from some of SQLIO.
Oxpdvsql02 Datadrive - Random
C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s900 -o8 -frandom -b64 -BH -LS -Fpa
- am.txt
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
parameter file used: param.txt
file g:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 900 secs to file g:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
size of file g:\testfile.dat needs to be: 83886080000 bytes
current file size: 0 bytes
need to expand by: 83886080000 bytes
expanding g:\testfile.dat ... done.
using specified size: 80000 MB for file: g:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 1297.80
MBs/sec: 81.11
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 11
Max_Latency(ms): 559
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+
%: 0 0 2 1 0 0 1 2 5 8 12 14 15 12 9 6 4 2 1 1 0 0 0 0 1
Oxpdvsql02 Log files -sequential
C:\Program Files (x86)\SQLIO>sqlio -kW -t2 -s900 -o8 -fsequential -b64 -BH -LS
- Fparam.txt
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
parameter file used: param.txt
file H:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 900 secs to file H:\testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
size of file H:\testfile.dat needs to be: 83886080000 bytes
current file size: 0 bytes
need to expand by: 83886080000 bytes
expanding H:\testfile.dat ... done.
using specified size: 80000 MB for file: H:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 3473.37
MBs/sec: 217.08
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 4
Max_Latency(ms): 92
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+
%: 0 6 46 12 8 6 5 4 3 2 2 1 1 1 1 0 0 0 0 0 0 0 0 0 1
Oxpdvsql02 Tempdb - random
C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s900 -o8 -frandom -b64 -BH -LS -Fparam.txt
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
parameter file used: param.txt
file i:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 900 secs to file i:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
size of file i:\testfile.dat needs to be: 83886080000 bytes
current file size: 0 bytes
need to expand by: 83886080000 bytes
expanding i:\testfile.dat ... done.
using specified size: 80000 MB for file: i:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 1281.39
MBs/sec: 80.08
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 11
Max_Latency(ms): 449
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+
%: 0 0 2 1 0 0 1 2 5 8 11 14 15 13 10 7 5 3 2 1 1 0 0 0 1



dynamox
9 Legend
•
20.4K Posts
0
November 3rd, 2012 15:00
windows drives were formatted with 64kb unit allocation size ?
RRR
4 Operator
•
5.7K Posts
0
November 4th, 2012 14:00
Would having a default 4 kB be that much worse ? I agree that you’d want to use a 64 kB allocation size.
RRR
4 Operator
•
5.7K Posts
0
November 4th, 2012 14:00
Based on the 64 kB IO size I'd expect way less than 3500 IOps for each of the the flash devices, so I think what you're seeing isn't that bad, but I'd like to see what others think about this.
dynamox
9 Legend
•
20.4K Posts
0
November 4th, 2012 14:00
I read on MSDN that Microsoft recommends 64kb for data volumes, something about the page size that SQL uses to read/write the data.
OPP_LTD
23 Posts
0
November 5th, 2012 01:00
It wasn't formatted to 64kb sizes but I've now done this. I'll re-run the tests again but I was expecting higher performance especially with the fast cache.
Rainer_EMC
4 Operator
•
8.6K Posts
0
November 5th, 2012 02:00
You need to allow for FAST Cache warmup time
OPP_LTD
23 Posts
0
November 5th, 2012 02:00
Yeah I thought so too. One thing we do notice is that we can get fairly higher IOPS but the mb/s hardly ever goes above 100mb/s.
dynamox
9 Legend
•
20.4K Posts
0
November 5th, 2012 03:00
you get pretty decent IOPS during your sequential test, flash drives are really great for the random workloads ..sequential workload they might be slightly better than SAS drives.
OPP_LTD
23 Posts
0
November 5th, 2012 08:00
Interestingly I've been looking at our fast cache performance and according to the statistics its doing very little. for our main Storage Pool it's 0 for all the stats and for the RAID groups it's <20 for everything. I see more misses than hits.
RRR
4 Operator
•
5.7K Posts
0
November 7th, 2012 00:00
This can mean that your locality is low and data cannot be reused a lot since it's not in cache. But the warm up is also something to consider. How long does your test run?
Normally FAST Cache is great for adding extra performance to SQL.
kelleg
4 Operator
•
4.5K Posts
0
January 22nd, 2013 09:00
When using SQL or other databases, it's considered best practice to segregate the LOGs and TEMP to a Raid 10 raid group (best) or R10 Pool and not use FAST cache and the DB to be in a R5 riad group or Pool and use FAST cache.
Was your question answered correctly? If so, please remember to mark your question Answered when you get the correct answer and award points to the person providing the answer.
Remembering to mark the question Answered helps others with similar problems and awarding points helps motivate people to take the time answer you questions. Forgetting this may affect responsiveness.
glen