Unsolved

This post is more than 5 years old

23 Posts

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

  1. 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

  1. 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

9 Legend

 • 

20.4K Posts

November 3rd, 2012 15:00

windows drives were formatted with 64kb unit allocation size ?

4 Operator

 • 

5.7K Posts

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.

4 Operator

 • 

5.7K Posts

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.

9 Legend

 • 

20.4K Posts

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.

23 Posts

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.

4 Operator

 • 

8.6K Posts

November 5th, 2012 02:00

You need to allow for FAST Cache warmup time

23 Posts

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.

9 Legend

 • 

20.4K Posts

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.

23 Posts

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.

4 Operator

 • 

5.7K Posts

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.

4 Operator

 • 

4.5K Posts

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

No Events found!

Top