Post reply

Clear DMV Stats

  • June 4, 2009 at 4:30 am

    #210492

    Hello

    I need to clear DMV stats without restarting the SQL service. Is there a way to do this?

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • June 4, 2009 at 5:22 am

    #1004527

    Other than for the sys.dm_os_wait_stats and sys.dm_os_latch_stats, there's no way.

    Edit: added the other DMV that can be cleared

    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

  • June 4, 2009 at 5:29 am

    #1004528

    DBCC SQLPERF("dmvname",CLEAR)

    eg: DBCC SQLPERF("sys.dm_os_latch_stats" , CLEAR)

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 4, 2009 at 5:31 am

    #1004529

    So the only way is to restart the SQL service.

    Thanks for the quick reply

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • June 4, 2009 at 5:35 am

    #1004532

    Thanks that is a great help

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • June 4, 2009 at 6:04 am

    #1004553

    joeroshan (6/4/2009)

    DBCC SQLPERF("dmvname",CLEAR)

    That only works for 2 DMVs - sys.dm_os_latch_stats and sys.dm_os_wait_stats. No other DMV can be cleared using that.

    eg

    DBCC SQLPERF("dm_io_virtual_file_stats" , CLEAR)

    Msg 2526, Level 16, State 12, Line 1

    Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

    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

  • June 8, 2009 at 6:28 am

    #1006031

    That only works for 2 DMVs - sys.dm_os_latch_stats and sys.dm_os_wait_stats. No other DMV can be cleared using that.

    Yes. I thought that was obvious from Gail's previous post. I just meant to give the syntax. Sorry if my post was misleading

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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