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 «««34567»»

Solid State Disks and SQL Server Expand / Collapse
Author
Message
Posted Sunday, April 11, 2010 11:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:07 PM
Points: 9,926, Visits: 11,183
Wesley Brown (4/9/2010)
If you are using Standard Edition like I am you don't get the full benefit of SQL Servers read-ahead feature.

Well that is true to some extent - but SQL Server still tries to issue large I/O requests wherever it can, (for scatter/gather as well as sequential operations).

For read-ahead, each large read request might be as small as 32 pages (256KB) or as large as 128 pages (1MB) on Standard Edition. Enterprise Edition raises the limit to 1,024 pages (8MB). Read-ahead is used extensively for data and index access - and even on some RID/KEY lookups and loop join operations - look for the WITH (UN)ORDERED PREFETCH iterator tag in the query plan.

Many other operations in SQL Server generate large I/O too: log file initialization (512KB), bulk insert (multiples of 128KB), backup (1MB), restore (64KB), DBCC (varies)...

I'm not trying to be smart, or contradict you here (far from it) but large I/Os are pretty common in SQL Server, and it would have been nice to see some test results at those sizes, just to give the spinning magnets a fair go

Enjoyed the article very much thanks!

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #901252
Posted Sunday, April 11, 2010 8:25 PM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Wednesday, July 23, 2014 1:21 PM
Points: 609, Visits: 407
Paul White NZ (4/11/2010)
Wesley Brown (4/9/2010)
If you are using Standard Edition like I am you don't get the full benefit of SQL Servers read-ahead feature.

Well that is true to some extent - but SQL Server still tries to issue large I/O requests wherever it can, (for scatter/gather as well as sequential operations).

For read-ahead, each large read request might be as small as 32 pages (256KB) or as large as 128 pages (1MB) on Standard Edition. Enterprise Edition raises the limit to 1,024 pages (8MB). Read-ahead is used extensively for data and index access - and even on some RID/KEY lookups and loop join operations - look for the WITH (UN)ORDERED PREFETCH iterator tag in the query plan.

Many other operations in SQL Server generate large I/O too: log file initialization (512KB), bulk insert (multiples of 128KB), backup (1MB), restore (64KB), DBCC (varies)...

I'm not trying to be smart, or contradict you here (far from it) but large I/Os are pretty common in SQL Server, and it would have been nice to see some test results at those sizes, just to give the spinning magnets a fair go

Enjoyed the article very much thanks!

Paul


Hey Paul!

No, you aren't being "smart" at all! I gave a little one liner and you expounded on that. I agree 100% if you can do sequential reads spinning disks still have a lot to offer. The root of the problem is most people ether don't or can't separate IO patterns on their disks arrays. Moving to SSD's negates some of that handicap.

The other part of my short statement was to say not everyone runs Enterprise Edition of SQL Server, I should have been clearer.

Thanks again!


http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #901330
Posted Wednesday, April 14, 2010 9:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 4,368, Visits: 6,208
brett.hawton (4/9/2010)
Great article!
Just some personal observations:
1. On larger queries SQL Server read-ahead kicks in and it reads 512KB blocks at a time. In my observations disks are much better at long sequential reads like this. Yes they still dont get close to matching the performance of SSD's but their performance does improve markedly due to the fact that all modern disks use sector skewing allowing the head to move (during a sequential read) from one track to the next without incurring any significant latency (the sectors are offset such that the time it takes the head to move 1 track it finds the next sector directly under the head).

Brett Hawton
Idera Product Architect


Your statement presumes that data is on disk in a sequential manner. I have never had a client that did anything to be rigorous about making sure their data was sequentially laid out on disk. There are many things that play into this where you can go wrong, everything from file growth increments through SQL Server settings (such as startup flag to allow 2MB allocation size) to data loading methodologies (think Fast Track here) to index maintenance. I would venture to say that less than 5% of all data on SQL Server is sequentially laid out on physical media. Some clients do actually have index maintenance set up (although usually incorrectly), and that is about it. SSDs completely eliminate the need for this...


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #903292
Posted Friday, March 9, 2012 3:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:46 AM
Points: 1,070, Visits: 907
Blast from the past eh?


Post #1264204
Posted Friday, March 9, 2012 6:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:29 AM
Points: 3, Visits: 78
IMO this article should be titled: Fusion IO and SQL Server.

It is not helpful in regards to the the biggest issues Solid State drives have.

I would have liked more details on longevity of the drives, a real test of SLC vs MLC, TRIM support for RAID (sofware OR hardware).

Editing the article didn't make it actual. Come on SQLServerCentral / Red Gate, why did this make it to today's mailing?! You guys are better than that.
Post #1264327
Posted Friday, March 9, 2012 7:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 8:35 AM
Points: 1,608, Visits: 198
I could see using SSD for log files rather than data storage. I would have also increased the RAM in the systems since SQL is RAM intensive. The combination should give the best performance as well as reducing the total cost.

Most of the performance issues I have found are related to poor file distribution, log and data on same drive etc. While data writes don't suffer as much, the TLOG operations do and that really has a negative effect on the performance. Using a fast access drive for TLOGs should really help with the overall performance.


Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
Post #1264362
Posted Friday, March 9, 2012 7:17 AM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Wednesday, July 23, 2014 1:21 PM
Points: 609, Visits: 407
ryabel,

When this article was written FusionIO was pretty much the main player in the enterprise space. I have covered several of the topics you are looking for on my blog as well SQLServerIO Fundamentals of Storage Systems.

I agree that the article could have a better title. Other than the title, was there anything else wrong with the article?

-wes


http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #1264375
Posted Friday, March 9, 2012 7:20 AM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Wednesday, July 23, 2014 1:21 PM
Points: 609, Visits: 407
Ray Laubert,

If you have multiple log files on a single drive that effectively makes it random I/O and a good candidate for solid state. If, however you have dedicated spindles to a single log drive performance is quite good since it is all sequential I/O.


http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #1264381
Posted Friday, March 9, 2012 8:52 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: Yesterday @ 11:52 AM
Points: 876, Visits: 2,421
Wesley Brown (3/9/2012)

...
I agree that the article could have a better title. Other than the title, was there anything else wrong with the article?
-wes


I would have to disagree with the statement "Using SQLIO, or any benchmark tool, to get the maximum performance numbers from your system isn't that helpful...". I find using an extensive SQLIO test set, on a variety of IO outstanding values, for a variety of IO block sizes to be very valuable. Note that I recommend 10-20 minute tests of each block size, and I'd also recommend using up as much RAM as possible on the target server

First, it gives you an idea of what the maximum performance you can expect in each condition is, and how they relate to each other.

Second, it lets you test various configurations (RAID levels, # of drives, stripe size settings, FusionIO options, etc.) and see how they affect each individual IO case. This gives you more information for tuning each particular system.

Third, this technique highlights bugs, limitations, edge cases, and oddities in your particular setup. I once saw a system experience an unusually and inexplicably low write rate on certain cases, regardless of the spindle configuration or whether they were solid state or spinning platter drives. More "holistic" techniques would show consolidated numbers, and with those alone you would have a very difficult time figuring out that only one or two specific IO cases have abnormal results.
Post #1264450
Posted Friday, March 9, 2012 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 14, 2012 10:16 AM
Points: 1, Visits: 4
I also have 2 production SQL servers on HP DL385 G7's that run arrays of Intel 160GB drives in RAID 10. I'm using the built-in 410i controller, with 512MB BBWC, and they just fly.

Even during maintenance, reporting time, etc, Disks are never more than 5% busy, and IO's complete in less than 1-2ms. I cannot bog them down.

I bought consumer drives (but intel, due to good reputation with raid cards), and planned on replacing them as they failed, as the price was about 10% of FusionIO, but I haven't had a single failure of any kind yet.

I've been running them hard in production for 2 years, and monitoring the writes to the drives with HP's management tools, and haven't had a single one blink yet.

Sometimes it's a heck of a lot cheaper to use multiply redundant cheap drives than enterprise SSD's.
Post #1264531
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse