Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL in a VM performance question

Posted on 2016-10-10
18
Medium Priority
?
208 Views
Last Modified: 2016-10-13
I am currently running a physical server for my SQL 2012 R2 box. I'm planning to visualize the box to my HP SAN.
I bought a new enclosure just for this SQL VM.  I've partitioned the enclosure with 3 different partitions, one for the OS, one for the data and one for the Logs.  The OS and Logs volume are raid 1. The Data volume is raid 6.  
I ran a performance test using the Crystal Disk Mark software: http://crystalmark.info/software/CrystalDiskMark/index-e.html

I ran the tests on my physical server and also on the raid 6 and raid 1 volumes as well.
I'm trying to figure out if there's a major difference between the raid 6 and raid 1, as I would like to keep my data with raid 6, so I don't have to use raid 10, or I will lose to much storage.

Looks to me like the physical server is faster than the VM, but there isn't much difference between raid 6 and raid 1.  
Is this correct?

Will it be adequate to use raid 6 for my data volume, instead of using raid 10?, as raid 1 didn't make a big difference?

SQL_VM_performance.png
0
Comment
Question by:afacts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +5
18 Comments
 
LVL 13

Assisted Solution

by:Máté Farkas
Máté Farkas earned 164 total points
ID: 41837967
Messurements on VM is not relevant.
Just take into account the values on real machine.
Make more messurements on RAID 6 and RAID 1 with Reading and Wrinting performance.
RAID 6 has faster reading, slower writing but it uses the disk space most efficiently.
RAID 1 is fast for reading and wringing but its too redundant and uses more disk space.
0
 
LVL 22

Expert Comment

by:robocat
ID: 41837969
The columns of intrest are 4K and 4K Q32T1.  These corresponds to the random IO that is typical for SQL server.
Sequential IO is not important, because SQL server rarely does sequential IO (except perhaps for backups/database verification).

I would say that the VM in combination with your SAN can handle about 2x to 4x the IO the physical box can. This is not unusual as a SAN often has more spindles and cache than a physical server with internal storage.

If your SQL server has a high load, you may see a speed boost when moving to this setup.  RAID6 has an impact on 4K write speed compared to RAID 1, but again this will not impact you as this is still faster than your physical box.
0
 
LVL 42

Assisted Solution

by:kevinhsieh
kevinhsieh earned 164 total points
ID: 41837977
What kind of HP SAN? Different types of SAN can have radically different performance characteristics for random reads, random writes, and latency.

Having more RAM available to SQL can mitigate performance challenges with your storage.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 35

Expert Comment

by:ste5an
ID: 41838104
Use Diskspd to benchmark your different IO scenarios.
0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 164 total points
ID: 41838108
Did you align the disks on blocks of 64K? Or multiple of 64 (128, 256,...)?
This should be a good improvement for SQL Server since each data extent page is 64K size, so if you align the disk block with SQL Server extent page size then you'll have a better performance.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 41838154
it depends - -in your case RAID 6 could be good.. Are you using SAN, EMC storage ?

check
Disk Partition Alignment Best Practices for SQL Server
https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

MICROSOFT SQL SERVER BEST PRACTICES
AND DESIGN GUIDELINES FOR EMC
STORAGE
https://www.emc.com/collateral/white-papers/h12341-sqlserver-bp-wp.pdf

Architecting Microsoft SQL Server
on VMware vSphere
http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 41838499
It looks like the sequential san speeds are limited by gigabit Ethernet (120MB/s), is that how it's connected? Like was said in the first comment, you only care about the 4K numbers not the sequential anyway so you will be fine.
0
 
LVL 35

Assisted Solution

by:ste5an
ste5an earned 164 total points
ID: 41838615
While the 4K numbers are important, the do not tell you the entire truth. Cause SQL Server data is organized in 8K pages in 64K extents.

Thus alignment matters as Vitor already wrote. But this is also the reason to use the appropriate tool to create the benchmark.
0
 
LVL 22

Assisted Solution

by:robocat
robocat earned 164 total points
ID: 41838773
A 8K blocksize random IO benchmark test could give more accurate data.

However, the test results already posted give a good enough idea to answer the original question:

Yes, RAID6 on this SAN box will be good enough for your purpose. RAID6 write speeds are a bit slower for random IO than RAID1, but still a lot faster than what you have now.

So if performance is OK now on the physical server, RAID6 for your data volume will certainly be sufficient, if not faster.
0
 

Author Comment

by:afacts
ID: 41839248
Thanks everyone for the input.
I have an HP SAN 2332i
The enclosure I'm using has 450Gb SAS drives, 15K rpm

Some of you mentioned that the SAN will be faster than the physical box, but I wonder how that's possible when the numbers show opposite?  I guess I'm not reading the numbers correctly?

The raid 6 volume has 8 drives in the array.
I'm also running HP servers, proliant, DL360 G7 and G8 servers.  I'm planning on only running my SQL sever on one physical servery, so I can give it like 100GB or so, as I have 128Gb on the physical server.

I'm only going to run my SQL server on this enclosure.
I created 1 volume for the OS, 2 drives, raid 1
I created 1 volume for the data, 8 drives, raid 6
I created 1 volume for logs, 2 drives, raid 1

I am using ISCSI to connect my SAN and servers.  I am using dual NICs, so I think I'm getting up to 2Gb/s.

I tried using dskpsd, but it gave me errors, so I couldn't get it to run.

I'm not sure how to align the disks with block sizes of 64K.
0
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 164 total points
ID: 41839262
To get more than 1gbps (which you are not) you have to have a san/hosts that support it and configure multipath iscsi and possibly a lag in the switch.

I'm not sure if this is possible with your gear, but having two NICs on the host and san doesn't give you more than 1gbps to your storage out of the box.
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1016 total points
ID: 41839308
all depends of what you plan to do with Sql server-- in some cases having physical Server can be the best solution...

for example: If there will be "archive"  read-only DBs with 1-2 users per week it is 1 c story
OR
if you plan to have 1000 concurrent users it is an another planning
---
About Q:
if you do not plan to use SAN storage:
plan to set if possible another at least RAID 6 ( MS recommends RAID 10) for TempDB data- log
Separate drives for user DBs Data - log files ...
System DB and Sql binaries preferably set no on C drive (RAID 1 +hot spare if you can)
you may need to keep in mind System (page File)  extra drive (RAID 1) RAMx 1.5 (size)
if you plan to run Tier 1 - with 24x7 availability you may need to invest in some 2nd "DR"- HA server
you may need to have server backup, sql DBs.. etc, etc.

It all depend on what sql server and DBs usage you plan to have..

As per above post - Sometimes more CPUs and RAM can have a big impact with Storage IO combined on sql code performance  
NIC team - it a good idea -- just if you can have 10GB with 10GB switches..
there is much more for the best sql server setup
As I said it is up to you -- you know what sql server you need and with  available budget you for sure will have the best server for your project..

more
https://www.experts-exchange.com/questions/28679405/disk-sizing-for-build-ms-sql-server-databases-server.html


check HP sql sizer tools if they still have (OLAP, DW)
0
 
LVL 42

Expert Comment

by:kevinhsieh
ID: 41839351
If you care at all about the performance, use SSD. PERIOD. END OF STORY. A single SSD will outperform 100 15K SAS drives in RAID 0, and be cheaper.
0
 

Author Comment

by:afacts
ID: 41839355
I know, and I agree, but my HP SAN does NOT support SSD's.
The SAN is about 5 to 6 years old, if not older.
0
 
LVL 42

Expert Comment

by:kevinhsieh
ID: 41839387
If your SAN is so old, why are you looking to use it for this application? Are you looking to possibly improve availability via hypervisor clustering?

Just putting SSD into the physical server now will speed it up tremendously, and would keep you at the same availability levels you have now. You can then decide whether or not to virtualize it on the same hardware or different hardware.
0
 

Author Comment

by:afacts
ID: 41839396
Yes, I am using hyper-V, in a cluster environment.
A new equiavelant SAN is about 15 to 25k, so I think we'll try to get another 3-4 years out of our current SAN before replacing it.
0
 
LVL 22

Expert Comment

by:robocat
ID: 41841092
>Some of you mentioned that the SAN will be faster than the physical box, but I wonder how that's possible when the numbers show opposite?  I guess I'm not reading the numbers correctly?


As I mentioned in my first post, the columns of intrest are 4K and 4K Q32T1, which is equivalent to IOPS. Databases need a lot of IOPS. Ignore the SEQ columns because databases usually don't do much sequential IO. Look at my first post for details.

The 4K IOPS RAID6 numbers are higher for the SAN than they are for physical server, so no need to worry about RAID6.

Just one important question you need to ask yourself: is performance adequate on the current physical server? If the answer is yes then there's no need to spend money for a new SAN or 10Gbit ethernet because the SAN box you have will be faster than the physical storage. You don't mention any existing performance issues so sometimes good is good enough.
0
 

Author Closing Comment

by:afacts
ID: 41842658
Thanks Everyone for your help and input.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question