We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical data. The table is currently 101 million rows of 35 columns, and growing at the rate of around 250,000 rows a day. Splitting the table into smaller tables unfortunately isn't really an option due to a mass of legacy code.
The database itself (around 100Gb) is held as a single file on a single SSD drive. The server has another two 10K SAS disks used for the boot OS, paging etc, and the server has 22Gb of RAM.
Although everything's running fine, we have a few dozen users who need to query the data in this table. We have limited control over what these queries do: sometimes it's pulling a few hundred rows from yesterday, at other times it's tens of thousands of rows from 6 months ago. 99.9% of the activity is reading rows; there is very little writing apart from the live data being
INSERTed throughout the day. At peak times, simple queries that return a lot of data can take half an hour or more to complete.
We have indexes in place that are helping, but the ultimate bottleneck appears to be disk I/O. The SSD in place isn't the fastest, and as a result we're looking at retrofitting a RAID1+0 array of high-end SSD drives to increase performance (we've checked the array card can handle the throughput).
Assuming we have this array in place, what is the best plan to increase read throughput to this database? If we have a super-fast SSD array, is that enough? Or would partitioning the database into separate files on separate logical drives be a better idea, even though they're essentially destined for the same disks? Similarly, would splitting database and log files across logical drives in the same array make any difference?