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
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35396 Visits: 11361
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Wesley Brown
Wesley Brown
SSCommitted
SSCommitted (1.9K reputation)

Group: Moderators
Points: 1941 Visits: 442
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
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32093 Visits: 8672
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 on googles mail service
RichB
RichB
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2998 Visits: 1065
Blast from the past eh?



DaVinci007
DaVinci007
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 82
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.
Ray Laubert
Ray Laubert
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1760 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
Wesley Brown
Wesley Brown
SSCommitted
SSCommitted (1.9K reputation)

Group: Moderators
Points: 1941 Visits: 442
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
Wesley Brown
Wesley Brown
SSCommitted
SSCommitted (1.9K reputation)

Group: Moderators
Points: 1941 Visits: 442
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
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4518 Visits: 2741
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.
aaronk-1026045
aaronk-1026045
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
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