By: Jeremy Kadlec | Comments | Related: > Hardware
Problem
Are solid state disk drives really the next big thing? Will they be able to expand the IO needs of an application further than the current set of disk drive technologies? Is this a technology I should consider with my future SQL Server platforms? The reason that I ask is because our SQL Server based applications are experiencing IO issues. We have split our databases into numerous file groups on numerous disk drives and have had some relief. At the same time we have been focusing on tuning our code and have made strides in that area. We just cannot tune the code quick enough to meet the demands. Are solid state disk something we should consider evaluating and migrating to in the near term to help with our performance issues?
Solution
If you have done all that you can to tune the application queries, improve the SQL Server performance and correctly layout the disk drives, then solid state disk drives may be a good alternative to consider to improve your application performance. If not, it makes sense to ensure the application is correctly designed and developed so that you are not just throwing hardware at a poor application design or development circumstance. In the short term you may have acceptable performance, but as more users are added to the system or new portions of the application are built and deployed, you may end up in the same predicament you are in today.
Advantages: Solid State Disk Drives
Solid state disk drives offer a number of benefits that should be considered when sizing or upgrading your current storage unit to support your SQL Server platform:
- Speed - Although the figures related to the speed from mechanical drives vary greatly i.e. depending on the type of disk (SATA, SCSI II, SCSI III, etc.), seek times, RPM's, etc. as compared to solid state disks, the consensus is that the solid state disks are able to process read and write requests much faster i.e. 40 to 250 times. Based on some accounts the solid state disk drives exceed 50,000 IO's per second. With a system that is truly IO bound, the solid state disks can offer a significant performance boost to improve the overall application performance without any application changes. Adding mechanical drives may improve performance in some scenarios, but in general not at the same proportion as solid state disks.
- No moving parts - Mechanical drives have component failures. Depending on how the disk drive is deployed leaves open the potential for application downtime or degraded performance when an array is being rebuilt. Since the solid state disk drives do not have any moving parts this failure rate is expected to be less than mechanical disk drives. However, in my opinion insufficient data is available to truly prove this fact since the usage rates for SQL Server applications are weighted heavily towards mechanical disk drives as compared to solid state disk drives. That may drastically change in the future, but the adoption rate is low at this point in time.
- Storage support - According to different sources, in the near future the solid state disks are expected to support terabytes of storage in a very small case as compared to separate storage arrays housing numerous mechanical drives to meet the IO needs. When this becomes a reality, less power will be required to support the storage sub system and less rack space is needed, both of which are beneficial to organizations looking for opportunities to act in a more 'green' manner.
Disadvantages: Solid State Disk Drives
On the other side of the coin are some critical considerations when adopting of solid sate disks:
- Cost - Depending on the manufacturer of the mechanical hard drives versus the solid state disks, the costs per GB are dramatically different. The solid state disk drives range in price from $5 to $10 per GB as compared to the mechanical disk drives which are less than $.50 per GB. When cost enters the picture, particular mechanical disk drive configurations can be considered to perform at an acceptable rate based on the application needs.
- Maturity and adoption - Thus far, mechanical disk drives have been the norm for just about all SQL Server based applications. The reported exception and my observations have been small OLTP applications i.e. 30 GB or less, with a large user base and/or high transactions per second, where the high speed of the application is absolutely critical from a business perspective. With the costs dropping and the storage expanding for solid state disks, this might shake up the disk drive market significantly, but only time will tell.
- Testing the waters - Depending on how an application reads and writes, according to some reports the solid state disks may be at a disadvantage in some random write operations. As is the case with adopting any new technology, it is imperative to test and validate your results as compared to your current solution. Although no solution is perfect, the solid state disks may have advantages with some processes and not others, but true testing in your environment will uncover those items so you can make the appropriate decision for your IO needs.
So what's the verdict?
Solid state disks have been available and in use in a variety of consumer and business capacities for a number of years. Traditionally, solid state disks have been reserved for specific types of systems such as high transaction per second systems with a relatively small data set i.e. 32 GB or less. Recently, this technology has turned the corner and solid state disks are becoming more of main stream option for platforms requiring high IO needs because the cost has been dropping. Although the cost for mechanical drives is a fraction of solid state drives, the adoption looks to be growing faster for server type scenarios i.e. a relatively small data set. At this point in time, solid state disks still appear to be impractical for large data sets and systems that are not IO bound due to the cost, but if you have IO bound applications with growing numbers of users, transactions per second, etc. then this technology is well worth your time to investigate as an option to address your needs.
Next Steps
- As you begin to size your future SQL Servers it does make sense to check into solid state disks and other emerging hardware technologies to benefit from the latest set of research and development.
- If you are experiencing disk IO issues, leveraging solid state disks does make sense, but it probably makes sense to check into your application design and coding first to validate no further steps can be taken in those areas. Just throwing hardware at the problem in the short term can improve the user experience, but it is not a long term solution.
- If the performance of your application cannot be improved any further to support your transaction levels, then solid state disks may be the best bet to address your needs. As such, research the vendors that are available and the products that they offer to see how they can meet your needs.
- Check out these related hardware tips:
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
