Solid State Disks and SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Wesley Brown » Solid State Disks and SQL Server

61 posts, Page 1 of 7

1 2 3 4 5 »»»

Solid State Disks and SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

Wesley Brown

Posted Wednesday, April 07, 2010 9:12 PM

SSChasing Mays

Group: Moderators
Last Login: Tuesday, May 07, 2013 3:54 PM
Points: 608, Visits: 379

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

Post #899232

Brad Cobb

Posted Wednesday, April 07, 2010 11:09 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, March 12, 2012 5:10 PM
Points: 11, 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.

Post #899280

Matt Whitfield

Posted Thursday, April 08, 2010 12:44 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:29 AM
Points: 509, Visits: 718

Nice article sir, thorough analysis at the I/O level, and well explained. Smile

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone

Post #899304

Kim Lesden

Posted Thursday, April 08, 2010 1:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 13, 2011 1:10 PM
Points: 1, 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.

Post #899319

iposner

Posted Thursday, April 08, 2010 2:00 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, October 08, 2012 3:14 PM
Points: 19, Visits: 67

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.

Post #899339

Ken Gaul

Posted Thursday, April 08, 2010 2:32 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:10 AM
Points: 216, Visits: 193

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

Post #899348

iposner

Posted Thursday, April 08, 2010 2:35 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, October 08, 2012 3:14 PM
Points: 19, Visits: 67

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.

Post #899354

Ken Gaul

Posted Thursday, April 08, 2010 2:50 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:10 AM
Points: 216, Visits: 193

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 Smile

Post #899357

iposner

Posted Thursday, April 08, 2010 3:25 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, October 08, 2012 3:14 PM
Points: 19, Visits: 67

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.

Post #899373

SQLBOT

Posted Thursday, April 08, 2010 6:34 AM

SSChasing Mays

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626, Visits: 835

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

Post #899479

« Prev Topic | Next Topic »

61 posts, Page 1 of 7

1 2 3 4 5 »»»

Permissions