SQL Server Performance

DMV and DMFs retention???

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by amu_27, Sep 12, 2007.

  1. amu_27 New Member

    As in SQL 2005 there are DMVs available. I want to ask what is the retention period for the data for these views? How long I can go back and look at the information? I don't find this information anywhere. Please help. Thanks in advance.
    amu_27, Sep 12, 2007
    #2

  2. FrankKalis Moderator

    I guess that depends on the particular DMV you have in mind. For some, like sys.dm_db_index_usage_stats the counters are resetted every time the SQL Server service is restarted.
    FrankKalis, Sep 12, 2007
    #3

  3. satya Moderator

    Majority of DMVs and DMFs are not persisted and will reset themselves back to zero once SQL Server is restarted.
    satya, Sep 12, 2007
    #4

  4. thomas New Member

    Also bear in mind that some, like sys.dm_db_index_physical_stats, which replaces dbcc showcontig, are not persisted at all, but are materialized when queried, so will scan tables/indexes and can take a while to run.
    thomas, Sep 12, 2007
    #5

  5. amu_27 New Member

    So this is how I look at it. The ones that are not persisted are more like Memory views and other persisted are stored looks like in either system database or somewhere.
    Is that correct? I want to make sure that the one that are not persisted and rely on memory that what happen if your SQL server is busy and used all the buffer than it will loose that data even I didn't restart the SQL server?
    amu_27, Sep 12, 2007
    #6

  6. satya Moderator

    We can advise you on more if you come up with those specified DMV/DMFs you wanted.
    satya, Sep 13, 2007
    #7

  7. amu_27 New Member

    Can we come up for all of them and create as article here as well? This allows to anyone to refer and understand if it is persistant view or not. just a thought...
    amu_27, Sep 13, 2007
    #8

  8. satya Moderator

    [:)] what do you know, I have just sent a FAQ to admin in thsi regard.
    satya, Sep 15, 2007
    #9

Share This Page