May 2, 2014 at 4:27 pm
Hi everyone, I'm seeing some really high average write stalls. As I have read, anything greater than 20ms is considered bad. With numbers like ~6400ms on writes and ~150ms on reads, I think that maybe I'm reading it wrong or else tempdb is doing very poorly. Tempdb is by itself on the S drive connected to the SAN.
SQL 2012
physical_namenamenum_of_writesavg_write_stall_msnum_of_readsavg_read_stall_ms
S:\Tempdb\tempdb.mdf tempdev450756298.58629 44154140.7896
S:\Tempdb\tempdb2.ndftempdev2440756441.31160544121157.7225584
S:\Tempdb\tempdb3.ndftempdev3440896438.10392644152139.0611071
S:\Tempdb\tempdb4.ndftempdev4440736438.95369 44110164.7617547
This system was built before we started formatting the drives to 64k allocation units. Would 64k have made a significant difference? We use TDE on a few of the databases so I understand that tempdb also gets encrypted. Would that impact performance so significantly? We also use AlwaysOn AG asynchronously from this primary to two secondaries. Does that impact tempdb?
When I look at the drive details for the S: drive, I see this:
DiskDescriptionTypeTotal GBDevice TypeHardware VendorPartitions
4Disk driveSCSI200.00SCSI (Port:3, Target ID:3, LUN:0)DGC RAID 10 SCSI Disk Device1
I've read a lot of articles but my numbers are so different than what is expected that I wonder where to begin.
Thanks for reading.
May 2, 2014 at 9:49 pm
PHXHoward (5/2/2014)
Hi everyone, I'm seeing some really high average write stalls. As I have read, anything greater than 20ms is considered bad. With numbers like ~6400ms on writes and ~150ms on reads, I think that maybe I'm reading it wrong or else tempdb is doing very poorly. Tempdb is by itself on the S drive connected to the SAN.SQL 2012
[font="Courier New"]
physical_name name num_of_writesavg_write_stall_msnum_of_readsavg_read_stall_ms
S:\Tempdb\tempdb.mdf tempdev450756298.58629 44154140.7896
S:\Tempdb\tempdb2.ndftempdev2440756441.31160544121157.7225584
S:\Tempdb\tempdb3.ndftempdev3440896438.10392644152139.0611071
S:\Tempdb\tempdb4.ndftempdev4440736438.95369 44110164.7617547
[/font]
This system was built before we started formatting the drives to 64k allocation units. Would 64k have made a significant difference? We use TDE on a few of the databases so I understand that tempdb also gets encrypted. Would that impact performance so significantly? We also use AlwaysOn AG asynchronously from this primary to two secondaries. Does that impact tempdb?
When I look at the drive details for the S: drive, I see this:
DiskDescriptionTypeTotal GBDevice TypeHardware VendorPartitions
4Disk driveSCSI200.00SCSI (Port:3, Target ID:3, LUN:0)DGC RAID 10 SCSI Disk Device1
I've read a lot of articles but my numbers are so different than what is expected that I wonder where to begin.
Thanks for reading.
There are sooo many things that this could be. First thing I would do is tune your queries. You could be hammering tempdb because you have bad stuff in the database or code.
Most clients I go to have numerous things configured suboptimally or just plain wrong on their IO subsystem.
How did you collect your stall information?
Have you don't any File IO stall analysis each hour or 10 minutes?
There are a number of good blog posts to be found about how to analyze what is hitting tempdb.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply