Post reply

get i/o physical read/writes for daterange

  • January 6, 2017 at 1:17 pm

    #339193

    Is there a way to get the i/o stats for say the last 24 hours? I assume not however figured i'd ask.

  • January 7, 2017 at 1:22 pm

    #1921249

    This sounds like a job for a server-side trace during a time that you expect the IO to occur.

    I don't think there is a way to collect this information for a specific period of time without using the profiler.

  • January 7, 2017 at 5:07 pm

    #1921254

    Snargables (1/6/2017)

    Is there a way to get the i/o stats for say the last 24 hours? I assume not however figured i'd ask.

    Perhaps set up Windows Performance monitor to save disk reads/writes. Can also do the same with SQL Server pages.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 8, 2017 at 9:11 am

    #1921264

    You could use sys.dm_io_virtual_file_stats and capture the data every couple of seconds to a table. Then you can query that table for the IO stats specific to the database and file.

    Essentially the same as using perfmon but stored in the database and available for queries if you are looking for IO stalls, writes, etc...

    There are also SQL performance counters in sys.dm_os_performance_counters that you can capture.

    If you are specifically looking for disk IO stats as in Disk sec/read then you need to capture that from windows performance monitor (perfmon).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 8, 2017 at 9:50 pm

    This was removed by the editor as SPAM

  • January 9, 2017 at 4:51 am

    #1921327

    JasonClark (1/8/2017)

    You may use this statement to check the virtual I/O file states

    SELECT * FROM sys.dm_io_virtual_file_stats (NULL, NULL);

    GO

    This shows you how many I/Os occurred, with latencies for all the files.

    It is good if you are looking for something in the last few seconds or even minutes but there doesn't seem to be a date that can be queried, something that is important if you want to analyse trends.

    That was why I suggested the Profiler and the reason I presume others recommended PerfMon.

  • January 9, 2017 at 5:36 am

    #1921332

    JasonClark (1/8/2017)

    You may use this statement to check the virtual I/O file states

    SELECT * FROM sys.dm_io_virtual_file_stats (NULL, NULL);

    GO

    This shows you how many I/Os occurred, with latencies for all the files.

    Only useful if you want the aggregated stats since the last time the server was started, which in general is useless.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 9, 2017 at 7:27 am

    #1921356

    when I run the SELECT * FROM sys.dm_io_virtual_file_stats (NULL, NULL);

    The "sample_ms" which from what I understand is the MS since last restart. It's showing 480 hours ago which impossible. The server or service haven’t restated for months. Am I reading this column incorrectly?

  • January 9, 2017 at 7:30 am

    #1921359

    Snargables (1/9/2017)

    The "sample_ms" which from what I understand is the MS since last restart. It's showing 480 hours ago which impossible. The server or service haven’t restated for months. Am I reading this column incorrectly?

    It can wrap around if the server's been up long enough.

    Though, check the create time for tempDB to confirm when SQL last started.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 9, 2017 at 8:02 am

    #1921391

    it was rebooted 5 months ago. So when you say wrap around are u speaking of just the that column or all of them? Also, i assume when u say wrap around u mean reset.

  • January 9, 2017 at 8:50 am

    #1921410

    Snargables (1/9/2017)

    So when you say wrap around are u speaking of just the that column or all of them?

    The sample_ms, the one you were asking about.

    The other columns are aggregate since the server last started, which means this DMV is useless for your requirement, unless you have a job running at a regular interval that records the values in the DMV and the time, so that you can calculate the change over time.

    Also, i assume when u say wrap around u mean reset.

    If I'd meant reset, I'd have said reset. Wrap around, as in, once it gets large enough it goes to negative big numbers, then gets smaller as it increments further

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 11 posts - 1 through 10 (of 10 total)

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