Post reply

tempdb IO Problem !

  • January 31, 2015 at 3:47 am

    #314199

    I have a database which has high temp table usage. Recently, I'm facing to tempdb's raid high IO usage. tempdab is working lonely in raid 1 with SSD storage. I use this query to help determining problem :

    SELECT TOP(10)

    *

    ,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms'

    FROM

    sys.dm_os_wait_stats

    WHERE

    waiting_tasks_count > 0

    ORDER BY

    wait_time_ms DESC

    and here is results :

    what should I do ?

  • January 31, 2015 at 7:44 pm

    #1774594

    I am not sure that your query actually shows a problem with TempDB. If you want to use wait stats to troubleshoot SQL Server I would start by reading Paul Randal's blog entries on Wait Stats. You can do that by going to sqlskills.com and search the site for "tell me where it hurts". There is also a seminal white paper written for using wait stats for troubleshooting. It can be downloaded from the link below.

    https://technet.microsoft.com/en-us/library/cc966413.aspx

    For IO related issues I would start by looking at latency via sys.dm_io_virtual_file_stats.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply