question

mcljeff avatar image
mcljeff asked

TempDB Data ON SSD and TempDB Log on SAN LUN

We recently have implemented separating TempDB Data Files using SSD (12 files and same sizes with trace flag 1117) and Log files using SAN LUN. DMV is giving us very high numbers on Latency. Our current configuration is SQL 2012 and running on active passive environment.
performancetempdb
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
What's the question?
1 Like 1 ·
JohnM avatar image JohnM commented ·
Also, where is the DMV showing latency? On the TempDB files or the log files? Both?
0 Likes 0 ·
mcljeff avatar image mcljeff commented ·
The Highest is on the 12 TempDB Files. The Read Latency is 8 ms while the write is 830+ms. The question is why the file latency is showing high. Previous settings is all TempDB is using one SAN LUN
0 Likes 0 ·
JohnM avatar image JohnM mcljeff commented ·
Just curious, what was the latency before the move?
0 Likes 0 ·

1 Answer

·
VishalhSingh avatar image
VishalhSingh answered
I believe, it should be other way around. Since the log files are more I/O expensive they needs faster drives. I am not sure about how your SAN looks like (RAID model) or if they are of iSCSi or FC type disks, what is their RPM etc but anything lesser would suggest you to keep log in SSD as they are much faster due to the fact they deal with in memory operation (electronic storage with IC chip) unlike mechanical drives.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

KenJ avatar image KenJ commented ·
Will moving the log files from a separate HDD volume onto a separate SSD volume reduce write latency for the unrelated SSD drives that houses the data files?
0 Likes 0 ·
VishalhSingh avatar image VishalhSingh commented ·
hosting the log and data files are subjective. In the above scenarios there are HDD and SSD. Since the SSD drives are faster hosting LDFs there will reduce the latency as LDF requires more throughput than MDF. Also, once should consider number of LUNs are available Vs number of Data files (of various DBs) and accommodate them equally across disks based on their TPS rates.
0 Likes 0 ·

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.