Whats the best possible way to use the SSDs on SQL Server
Whats the best possible way to use the SSDs on SQL Server? Will that differ a lot with type\make-model of SSDs? I have Dell R720 . Read multiple articles and ended up confusing whether to use them even for tempdb
Hey @RHA I'd simply use the SSDs to ease the load on any drives that currently are your bottle neck. It could be the drive with your tempdb files on, it could be the drive with your log files on, it could be one of your data drives...sorry. It really depends on your environment to what will be best to put on them, the log drive or the tempdb drives are probably the best eyes closed approach but if you have monitoring you can use that to establish where they could best be utilized. Knowing the limits of your current storage system and the new SSDs will help with this. You can run some queries for IO issues, looking for the worst and moving those files perhaps. Take a look at Glen Berry's set of scripts on his blog: [Glenn Berry's SQL Server Performance] (look at script 18 - 20 I think), there's also a link for [Analyzing I/O Subsystem Performance at PASS 2014] which may help you. Depending on the load your putting on the SSD's it's possible you could have more than one type of thing on there i.e. tempdb and logs, just depends on what you have and what you need. If you're using 2014 you can consider the buffer pool extension too: [Buffer Pool Extension] Hope that helps getting you looking in the right place. P.S. link to [Glenn's queries] :
I've seen cases where SSDs have been used to 'solve' performance problems linked to badly running queries experiencing IO bottlenecks. Guess what - the IO bottleneck goes away, and a CPU bottleneck takes it's place! If the queries are inefficiently reading too much data, then all SSDs will do s get that excess of data to the CPU faster. Not saying SSDs don't have their place, and tempdb is probably a good candidate, and as @Mart says if you are on 2014, then BPE too.