Re: Using SSD's

On 19-Jun-2014 11:29 -0500, Graap, Kenneth wrote:

We recently installed 8 SSD on our system.

These units are in the same iASP as several older HDD’s.

We can’t allocate all of our “HOT” files to the SSD’s because of
space limitations.

If I had to choose between locating Physical File data or the
Logical File indexes built over this data on SSD, which type of file
would provide the best performance boost being located on SSD, the
PF’s or the LF’s

I would choose according to what I perceived to provide [or better, as verified by testing provides] the greatest benefits to the applications; I would not choose based solely on a file type [logical vs physical] designation. Clearly, a logical file access path that is utilized with an /index-only/ query implementation would effect more beneficial performance when located on SSD than instead, having the physical data on the SSD; i.e. the data from the PF would not be referenced, because the data comes from the access path itself.

Even an index\access-path that merely allows a query to limit the physical data to a /small/ amount of rows might be more worthwhile being accessed from SSD than the actual data, if that AccPth is used very often. That can be important even if the PF data access is slower due to not coming from SSD. If the amount of physical data storage requirements is going to limit the high-use and efficient access methods [per Keyed Access Path access methods] from taking advantage of the SSD storage, then the net effect of PF on SSD could be much worse than if some of those AccPth were on SSD instead. The benefit of the PF data on SSD is lowest when only a /small/ portion of its rows are being accessed by an efficient sparse access path processing, and similarly if data for many of the columns are rarely accessed, because in either case the SSD storage taken for low-use data directly limits storage for those access paths that can quickly find those [more popular] rows.