July 22, 2014 at 1:16 pm
Hi All,
I am new to the forum and don't have a ton of SQL experience. In our environment we have 1 x MSSQL2008R2 instance which is running on a failover cluster hosted by two servers. The storage for SQL is on a fiber channel SAN array.
My issue is that the tempdb avg wait times have been averaging between 1000ms and 1500ms which is absolutely horrible. There are 24 logical processors on the SQL server and currently there are 4 x tempdb mdf files. The odd thing about this is that after an mssql service restart these tempdb avg wait times with drop all the way back down to between 1 to 2ms and will stay in that range for several days. Then after a few days they will jump up to the 1000ms to 1500ms range and stay there until the mssql service is restarted again.
Is it possible that something is getting hung in the tempdb files to cause this? I wouldn't assume that this characteristic is normal. Any help is greatly appreciated and if more information about our setup is required I would be happy to provide that information. Thanks in advance.
July 22, 2014 at 1:17 pm
What exactly do you mean by a 'TempDB wait'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2014 at 1:18 pm
GilaMonster (7/22/2014)
What exactly do you mean by a 'TempDB wait'?
These are the results from the query that is run:
tempdb (Data) Size:5000 MB File: T:\tempdb\tempdb.mdf
Avg Wait:0 ms/read Reads:900195 Bytes:56281440256 WaitTime:843939 ms Avg:62521 bytes/read
Avg Wait:1458 ms/write Writes:964242 Bytes:63116009472 WaitTime:1406122821 ms Avg:65456 bytes/write
tempdb (Log) Size:1000 MB File: T:\tempdb\templog.ldf
Avg Wait:3 ms/read Reads:95 Bytes:4923392 WaitTime:299 ms Avg:51825 bytes/read
Avg Wait:23 ms/write Writes:659008 Bytes:40010247168 WaitTime:15258539 ms Avg:60712 bytes/write
tempdb (Data) Size:5000 MB File: T:\tempdb\tempdb1.mdf
Avg Wait:0 ms/read Reads:899923 Bytes:56290017280 WaitTime:786340 ms Avg:62549 bytes/read
Avg Wait:1333 ms/write Writes:964789 Bytes:63123849216 WaitTime:1286930041 ms Avg:65427 bytes/write
tempdb (Data) Size:5000 MB File: T:\tempdb\tempdb2.mdf
Avg Wait:0 ms/read Reads:900116 Bytes:56274796544 WaitTime:795867 ms Avg:62519 bytes/read
Avg Wait:1324 ms/write Writes:964883 Bytes:63123316736 WaitTime:1277761465 ms Avg:65420 bytes/write
tempdb (Data) Size:5000 MB File: T:\tempdb\tempdb3.mdf
Avg Wait:0 ms/read Reads:899874 Bytes:56279752704 WaitTime:807586 ms Avg:62541 bytes/read
Avg Wait:1324 ms/write Writes:964783 Bytes:63121842176 WaitTime:1278038304 ms Avg:65425 bytes/write
July 22, 2014 at 1:20 pm
And what are you running/using to get that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2014 at 1:28 pm
GilaMonster (7/22/2014)
And what are you running/using to get that?
It is a stored procedure that is built into the database (that is what it says when ou scroll over the command in the query window in management studio). Perhaps there is a more accurate query I could run that wasn't built into the database? Again apologies for not having a whole lot experience with SQL.
July 22, 2014 at 1:39 pm
Could it be AVG write stall like I use in the below query? With 2 out of 16 of my instances I get high write stall in the 1500 range too with similar SAN array. Most other instances get 5-15 response time.
SELECT
(1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'
and name = 'tempdev'
July 22, 2014 at 1:46 pm
JeepHound (7/22/2014)
Could it be AVG write stall like I use in the below query? With 2 out of 16 of my instances I get high write stall in the 1500 range too with similar SAN array. Most other instances get 5-15 response time.SELECT
(1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'
and name = 'tempdev'
Hi. I think you are exactly correct. I ran the query you posted above and the result was the exact same as the other stored procedure I have been running with was 1447ms. So does this confirm that there is a serious I/O latency issue? Thanks.
July 22, 2014 at 1:48 pm
I digress on this one, I was using it as a benchmark for splitting out the tempdb. I also read the recommendation to be below 20, however I have not received 1 user complaint about the database that is housed in that instance. I was hoping someone else could shed some light on it.
July 23, 2014 at 7:11 am
JeepHound (7/22/2014)
I digress on this one, I was using it as a benchmark for splitting out the tempdb. I also read the recommendation to be below 20, however I have not received 1 user complaint about the database that is housed in that instance. I was hoping someone else could shed some light on it.
Cool, hopefully someone might be able to shed some more light on this one for us.
August 22, 2014 at 7:48 am
Still can't seem to find out what is causing this issue. Any other troubleshooting suggestions to determine what is causing the avg wait time to sky rocket and hold? Thanks.
August 25, 2014 at 12:11 pm
Is the tempdb files set to particular size or set to autogrowth?. Please clarify
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply