SQL Server Performance

Life of data extracted using DMVs & DMFs in SQL Server 2005?

Discussion in 'SQL Server Knowledge Sharing Network (SqlServer-qa' started by satya, Sep 12, 2007.

  1. satya Moderator

    Its rare that we come across peculiar (easy though) questions to answers within the forums here, but today a question got me interest. The matter is "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."
    To open up the short forms of DMV - Dynamic Management Views and DMF - Dynamic Management Function are the new handy tools to the DBAs in SQL Server 2005 by not relying upon other software tools or so. Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of dynamic management views and functions:
    • Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
      • Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database
    • As specified both the statements return internal, implementation-specific state data. Further that Microsoft SQL Development team suggested that the schemas and the data they return may change in future releases of SQL Server, so better be-wise to use them or read BOL when using same code in upcoming versions of SQL Server.
      The reply to the question is that majority of DMVs and DMFs are not persisted and will reset themselves back to zero once SQL Server is restarted. For instance like sys.dm_db_index_usage_stats the counters are resetted every time the SQL Server service is restarted.
  2. ojos_locos New Member

    Don't forget that it's not just a restart of SQL Server that causes the counters to be set back to zero. Operations such as setting a database to read_only have the same effect for rows relating to that database. Is there a definitive list of events that have this effect?John
  3. satya Moderator

    I have to check that again to confirm whether changing the database would cause such an affect. As most of the DMVs are related to SQL Server configuration.
  4. satya Moderator

    Can I have more information on what sort of DMVs you are observed, as of now I don't seem to get much information.

Share This Page