I have been analysing a database server and found that disk I/O is quite a major bottleneck on the server.
We already moved to RAID 10 arrays, and while it did provide some level of improvement, the disk bottleneck still exists.
A selection of the queries do not help matters; some server side cursors which are quite badly designed, but they come from a bespoke piece of software which I cannot change, so we are left with trying to push them through as best as possible.
It has recently been suggested to move to SSD drives for the main database (which is on a separate drive) to assist with a significant performance increase in I/O.
While the basic disk stats would suggest that it will definitely help with performance, I have never had experience of hosting SQL on SSD, so was looking for some experiences or advice from anyone who has previously used it.
Is it reliable?
What level of performance increase do they provide in real terms?
Which components of SQL Server should ideally be hosted on SSD drives to optimise performance?
All replies much appreciated thanks