Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Solid State Disks and SQL Server Expand / Collapse
Author
Message
Posted Thursday, April 08, 2010 7:28 AM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Tuesday, February 18, 2014 7:51 PM
Points: 609, Visits: 406
brad.c-774062 Since we do alot of large random IO there was no way a standard disk array could keep up with the SSD. We monitor every aspect of our IO system down to the database file level. I agree, without testing how do you know if your particular workload will see an improvement.

Kim Lesden with a tempdb of 120GB a ramdisk wasn't an option.

ian-707364 I'm not sure what you mean by setting the sector size to 8KB sector sizes on a disk are ether 512 bytes or 4KB on most modern disk systems. If you are talking about formatting the file system I always set them to 64KB block size.

Ken Gaul as others have pointed out it is all about cost. a SSD SAN would be 10x the cost of a simmilarly sized Fusion-IO solution with breakout boxes.

Thanks for the feedback!

-wes


http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #899551
Posted Thursday, April 08, 2010 7:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
There is no other way to do this other than copy all the data to a new database that is created on the new 4KB sector size.


What's a reference for this?


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #899583
Posted Thursday, April 08, 2010 8:14 AM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Tuesday, February 18, 2014 7:51 PM
Points: 609, Visits: 406
Jonathan,

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLIOBasicsCh2.doc

A SQL Server database can be restored or attached on a system that has a smaller sector size. To help guarantee data integrity, the larger sector size should be evenly divisible by the smaller sector size. For example a 4 KB source restored/attached to a 1 KB or 512-byte destination is an evenly divisible size. Restoring or attaching to a smaller sector size such as 1536 bytes does not fill the “evenly divisible” requirement and immediately requires sector rewrite operations to occur.

SQL Server is not designed to dynamically upgrade the database to the larger sector sizes. SQL Server disallows restoring or attaching a database on a system with a larger sector size; it generates an error message and prevents the restore or attach operation. Enabling a database to function by using a smaller formatted sector size than the actual sector size violates the WAL protocol because the sector size variation guarantees the log records will not be correctly aligned with the physical sector size and log records will be rewritten.
At the time of publication, some current subsystems report sector sizes larger than 512 bytes but most do not. The larger sector sizes involve newer technology and require API changes at the operating system level. Future changes in Microsoft® Windows and SQL Server will correctly support the larger sector sizes and allow for dynamic adjustment of sector sizes.

You can go from 4k to 512 but not the other way. This also specifically effects data logs.


http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #899600
Posted Thursday, April 08, 2010 8:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
I think Brent Ozar tested the HP version of this on tempdb a few weeks back. HP rebrands these, but i think they only sell the blade server version under the HP brand. and they are very expensive.

few years ago i experimented with RAID10 on a new BI server we built. i created a few RAID10 volumes. performance was good, but not so much better. and i constantly ran out of space and people who controlled the budget for this server constantly complained about buying new drives. I finally blew it away in favor of a few RAID5 and RAID1 volumes. If i ever do it again i would opt for multiple RAID1 volumes and split the data/index and log files.

i would like to see a test comparing SSD's with a MSA 70 full of RAID1 volumes instead of RAID10.

One nice thing about SSD is that they use a lot less power. i compared a consumer SSD drive to a consumer HD and it used something like 1/10 the power. Intel just started producing flash memory on their new 25nm process and the first products should come out in the 4th quarter. later this year and early next year we should see a huge drop in SSD prices. Toshiba is also experimenting with sub 25nm manufacturing, but their largest customer is Apple. Over the next few years I think there will be a huge ROI case to be made in favor of SSD on the electricity savings alone.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #899616
Posted Thursday, April 08, 2010 8:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:47 AM
Points: 844, Visits: 2,329
Thank you for such an informative post. I've been involved in initial benchmarking of more standard enterprise SATA SSD's on a local RAID controller, and while the results are impressive, they're very limited to random workloads, on a very limited test set, they were limited to primarily 8KB and 64KB random reads and 8KB random writes (64KB random writes were merely double an equivalent 600GB 15K RAID 1 pair).

Question: Did you test degraded mode performance by, for instance, pulling one Fusion-IO card out (simulating total failure)?
Post #899619
Posted Thursday, April 08, 2010 8:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
ian-707364 (4/8/2010)
Look at the price of a regular SAN with regular disks. It still can't compete on price! I did a price/performance comparison of a single MSA70 (25 disks) to an EVA3000 - it was 8x cheaper per MB/s. And that's a really cheap EVA. As you move to bigger SANs, the cost per MB/s gets higher still - they're not designed for high performance service levels to an individual server.


i don't know about EVA, with with higher end EMC SAN's you get some nice software, and the ability to replicate data on a disk level to a DR location. and the build quality and support is top notch. if you have a bad part, you won't know until you get a call from EMC telling you to be onsite for a delivery in a few hours.

I deal with HP support for servers and a backup tape library. overall it's pretty good, but sometimes i have to waste time taking a server down to run diagnostics, switch out memory to different slots, CPU's to different slots, etc.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #899621
Posted Thursday, April 08, 2010 8:38 AM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Tuesday, February 18, 2014 7:51 PM
Points: 609, Visits: 406
Alen,

i would like to see a test comparing SSD's with a MSA 70 full of RAID1 volumes instead of RAID10.

If I get the time and can free up an MSA I'll see if I can cut up our DB and give it a try.

On the EMC/SAN thing. You do get lots of perks but you do pay for them. If you are looking for speed and are pricing $/IO or $/GB the Fusion-IO expander chasis starts looking real good.

Nadrek,
I didn't simulate a failure scenario since we weren't able to completely finish our testing cycle.


http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #899632
Posted Thursday, April 08, 2010 8:40 AM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Tuesday, February 18, 2014 7:51 PM
Points: 609, Visits: 406
Also, we will be posting up a revised version of the article minus the typo's, grammatical errors and formatting issues.

http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #899636
Posted Thursday, April 08, 2010 8:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
I was talking about the cluster size for formatting. However, as I understand it, currently most drives physical cluster size are 512 bytes with some new drives at 4096 supported in the latest version of Windows.

Surely the Fusion IO cluster size refers to the raw IO block size to be written to SSD as an atomic unit? For SQL Server this should be 8192. Or am I missing something?
Post #899644
Posted Thursday, April 08, 2010 8:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
a little off-topic but maybe someone can answer this for me

in windows 2003 and later on basic disks i run diskpart and i can create the 64k offset for performance reasons. with dynamic and GPT disks it never worked for me. i looked in the help section but never found much useful info on the difference between the three, except that basic disks are there for backward compatibility. or why the 64k offset would not create on the dynamic of GPT


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #899652
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse