SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Solid State Disks and SQL Server


Solid State Disks and SQL Server

Author
Message
Wesley Brown
Wesley Brown
Ten Centuries
Ten Centuries (1.2K reputation)

Group: Moderators
Points: 1153 Visits: 440
Comments posted to this topic are about the item Solid State Disks and SQL Server

http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Brad Cobb
Brad Cobb
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 9
I'm surprised your article didn't mention anything about first testing whether your database IO performance is the bottleneck in your environment. We tested a Fusion IO card, and whilst had similar IO results, the speed improvement in the application was virtually zero.

I'd suggest to anyone looking at SSD drives for databases to first do performance monitoring on their existing hardware and find out if you're adding unnecessary risk and memory wastage for something you don't require.
Matt Whitfield
Matt Whitfield
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1243 Visits: 719
Nice article sir, thorough analysis at the I/O level, and well explained. :-)

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Kim Lesden
Kim Lesden
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 11
The fastest way to improve database performance is to put the tempdb on a ram disk (free download at dataram.com). That works even if you have to reduce the ram available to the database itself. The tempdb is created from scratch every time you start up the SQL server (put the temp files in the root directory of the ram drive). I had a 4gb ram 2xopteron 2,2ghz hp dl385 g1 server with 6x72gb 15k u320 in a raid 10 running windows 2008 x64 an SQL server standard x64. I gave the SQL server 2,5gb ram and put the tempdb on a 512mb ramdrive leaving 1 gb for windows (+ pagefile). This performed very well for a 22gb database. in production I have more ram (25gb) but still take some from the SQL server and use it for a ramdrive. I strongly recommend using quest spotlight on SQL server for further optimization. I bought it before investing in new hardware but there is also a 30 day trial version.
iposner
iposner
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 127
I'm surprised you didn't set the sector size to 8KB. That's the page size for SQL Server and would have been a better choice than 4KB.

On my production servers, I usually format those volumes not requiring compression (i.e. non-backup) that are used exclusively for SQL Server to 64KB. This keeps the MFT nice and small, ensures minimum fragmentation and fits well with SQL Server's extent size of 64KB: Allocating an extent maps directly to a disk sector.
Ken Gaul
Ken Gaul
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 194
We use fusionIO for tempdb too, beautiful! But why all the messing around with PCI expansion etc, if you need more space than you can squeeze into a box why not get a solid state SAN, then all you have to do is find enough space for HBA cards to give you enough bandwidth.
Now I don't have one of these so can't recommend them or comment on their suitability for SQL server but they do look the biz and I'd love to give one a test drive.

http://www.ramsan.com
iposner
iposner
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 127
A solid state SAN won't come close - maximum bandwidth per slot is 4Gbps (500MB/s). Plus you have the added latency from the fabric switches, etc. But the real SAN-SSD killer is cost - it's too prohibitive for a dedicated IO subsystem.
Ken Gaul
Ken Gaul
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 194
What do you mean per slot? Per port yes, but you can get dual ported HBAs which will double that, so 2 dual ported cards give you 2GB/s. And that is if you stay on fibre. What about infini-band that is 10 or 20 Gb/s isn't it?
Well I've never poked around the price of these things but if you need 4TB of storage at that level I would expect the hoops you would have to go through to get enough fusion IO cards in single system would probably make it fairly cost effective and certianly simpler and easier to maintain.
I guess you also get into the traditional arguments of SAN vs DAS storage efficiencies and sharability etc.

I'd still like one for christmas :-)
iposner
iposner
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 127
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.
SQLBOT
SQLBOT
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1188 Visits: 836
great article. Thanks for sharing!

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search