question

wingman2468 avatar image
wingman2468 asked

Best use of SSD drive?

I recently took over several SQL Servers that have at least 3 separate physical drive arrays that were originally used for tempdb, SQLData and SQLlogs (just like many of the systems I've supported in the past). However, sometime prior to me joining the team, the previous DBA purchased one SSD drive for each server and he moved everything to this one SSD drive including tempdb, SQLData and SQLLog files. I'm seeing significant disk IO bottlenecks. Disk Queue Lengths are through the roof during peak operations. I'm thinking I should leverage the other drive arrays to improve disk IO. My question is: Given our current config (1 SSD drive and 3 separate drive arrays (RAID10)), between tempdb, data and log, which is best suited for the SSD drive and or which should I move to the hard drives?
ssd
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.

JohnM avatar image
JohnM answered
I would leave TempDB on the SSD and then move the Data & Log files to their own separate drive arrays. SSD's are usually good for random stuff, so TempDB is usually an excellent candidate for that type of storage media.
5 comments
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.

I would enhance this with just leaving tempDB on the SSD. The main reason is not actually performance here (you would normally leave the data file experiencing the highest i/o for your biggest benefit), rather I would be concerned about data recovery. Right now you have no redundancy on that single SSD. Lose that and all your data goes bye bye and you are back to recovering from tape. At least if it's just TempDB you can start the server up and move TempDB to another drive to get back up and running relatively quickly.
3 Likes 3 ·
What, no "it depends"? I'm disappointed!
2 Likes 2 ·
Yeah, I'd like to add my own answer, but I think you've nailed it. Tempdb is the biggest I/O hole in SQL Server, so making it as fast as possible is probably the best bet in most systems. Better still though, SSD arrays for each drive.
1 Like 1 ·
I know, I was slacking I'm sorry!! ;-)
1 Like 1 ·
Great point. I appreciate everyone's input.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
I would like to say that it does depend. You haven't let us know yet what the system load looks like yet. Is tempdb usage high? Are you using features that particularly tax tempdb? I agree that the data and log files should probably be separated. However, I suggest that you take a close look at wait stats and storage system usage after the change to make sure the move was a good one. Whatever you do: measure, change and re-measure to ensure the change was "good"
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.