question

RHA avatar image
RHA asked

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
ssd
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
Mart avatar image
Mart answered
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][1] (look at script 18 - 20 I think), there's also a link for [Analyzing I/O Subsystem Performance at PASS 2014][2] 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][3] Hope that helps getting you looking in the right place. P.S. link to [Glenn's queries][4] [1]: https://sqlserverperformance.wordpress.com/ [2]: https://sqlserverperformance.wordpress.com/2014/11/06/analyzing-io-subsystem-performance-at-pass-2014/ [3]: https://msdn.microsoft.com/en-us/library/dn133176.aspx [4]: https://sqlserverperformance.wordpress.com/category/diagnostic-queries/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.