dm_io_virtual_file_stats

  • Dear SQLGuru,

    I have two flat files that we process nightly say file1.txt and file2.txt. We process file1 at 12:45am and file2 at 4:45am nightly. The size of file1 is 5 times bigger than file2 and both files run throught same series of procedures and tables have exactly identical structure on two separate databases.

    The issue I currently have is it takes more than 20 mins to process the tiny file1 but it only takes maximum 5 mins to process file2. I can also confirm that nothing runs during 4:40am to 5:30am window on both SQL and OS perspective.

    I setup two metrics by using virtual_file_stats DMV and dump data into a permanent table every 3 mins while files were processing in last three days and have a query to aggregate the data. Below is my query:

    SELECT Capture_Time,

    CAST(Num_of_Reads/(IO_STALL/1000.0) as Numeric (18,2)) as [Reads/Sec]

    , CAST(Num_of_Writes/(IO_STALL/1000.0) as Numeric (18,2))as [Writes/Sec]

    , (Num_of_Reads+Num_of_writes) as

    , CAST((Num_of_Reads+Num_of_Writes)/(IO_STALL/1000.0) as Numeric(18,2)) as

    , IO_STALL/CASE WHEN (num_of_bytes_read+num_of_bytes_written)>0

    THEN num_of_bytes_read+num_of_bytes_written

    ELSE 1 END AS [IoStall/Op]

    FROM DBA..file_stats

    WHERE Database_id=17

    The value for Reads/sec,Write/sec,I/O Count and I/O /Sec for file2 metric is way less than file1 metric but why file2 takes way longer to complete. IOStall/OP is zero both both.

    Thanks so much for your advise.

    Silaphet,

  • Silaphet Mounkhaty (3/22/2010)


    Dear SQLGuru,

    I have two flat files that we process nightly say file1.txt and file2.txt. We process file1 at 12:45am and file2 at 4:45am nightly. The size of file1 is 5 times bigger than file2 and both files run throught same series of procedures and tables have exactly identical structure on two separate databases.

    The issue I currently have is it takes more than 20 mins to process the tiny file1 but it only takes maximum 5 mins to process file2. I can also confirm that nothing runs during 4:40am to 5:30am window on both SQL and OS perspective.

    I setup two metrics by using virtual_file_stats DMV and dump data into a permanent table every 3 mins while files were processing in last three days and have a query to aggregate the data. Below is my query:

    SELECT Capture_Time,

    CAST(Num_of_Reads/(IO_STALL/1000.0) as Numeric (18,2)) as [Reads/Sec]

    , CAST(Num_of_Writes/(IO_STALL/1000.0) as Numeric (18,2))as [Writes/Sec]

    , (Num_of_Reads+Num_of_writes) as

    , CAST((Num_of_Reads+Num_of_Writes)/(IO_STALL/1000.0) as Numeric(18,2)) as

    , IO_STALL/CASE WHEN (num_of_bytes_read+num_of_bytes_written)>0

    THEN num_of_bytes_read+num_of_bytes_written

    ELSE 1 END AS [IoStall/Op]

    FROM DBA..file_stats

    WHERE Database_id=17

    The value for Reads/sec,Write/sec,I/O Count and I/O /Sec for file2 metric is way less than file1 metric but why file2 takes way longer to complete. IOStall/OP is zero both both.

    Thanks so much for your advise.

    Silaphet,

    Could you please clarify? I am uncertain if file1 is slower or file2 is slower.

    Please look at the points of emphasis in your text again.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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