February 7, 2008 | SQL Server

16 Changed Dynamic Management Views in SQL Server 2008

Yesterday Denis Gobo told you about 33 new DMVs in SQL Server 2008; previously, I had noticed that a few of the existing DMVs from 2005 had changed slightly.  So, Denis sparked my curiosity, and I ran the following query on a recent build of SQL Server 2008, which had a linked server pointing to SQL Server 2005:

SELECT s1.vn, s1.cn
FROM
(
        SELECT
                vn = OBJECT_NAME([object_id]),
                cn = name
        FROM
                master.sys.all_columns
        WHERE
                OBJECT_NAME([object_id]) LIKE 'dm[_]%'
)
        s1
LEFT OUTER JOIN
(
        SELECT
                vn = v.name,
                cn = c.name
        FROM
                [SQL2005_LinkedServer].master.sys.all_columns c
        INNER JOIN
                [SQL2005_LinkedServer].master.sys.all_views v
        ON    
                c.[object_id] = v.[object_id]
        WHERE
               v.name LIKE 'dm[_]%'
)
    s2
ON
        s1.vn = s2.vn
        AND s1.cn = s2.cn
WHERE
    s2.vn IS NULL
    AND EXISTS
    (
        SELECT 1
            FROM [SQL2005_LinkedServer].master.sys.all_views
            WHERE name LIKE 'dm[_]%'
            AND name = s1.vn
    )
ORDER BY
    vn,cn;

Here are the results; 25 new columns across 16 DMVs.  Several seem to have to do with resource governor, but the one I think I like the best is sys.dm_os_sys_info.sqlserver_start_time … this goes back to a suggestion I made on Ladybug, which was a bug/suggestion system that pre-dates Connect.

View Column
dm_db_file_space_usage database_fragment_id
dm_exec_cached_plans pool_id
dm_exec_query_memory_grants group_id
dm_exec_query_memory_grants ideal_memory_kb
dm_exec_query_memory_grants is_small
dm_exec_query_memory_grants pool_id
dm_exec_query_resource_semaphores pool_id
dm_exec_requests group_id
dm_exec_sessions group_id
dm_fts_active_catalogs is_importing
dm_os_buffer_descriptors numa_node
dm_os_memory_cache_entries pool_id
dm_os_schedulers quantum_length_us
dm_os_sys_info sqlserver_start_time
dm_os_sys_info sqlserver_start_time_ms_ticks
dm_os_tasks parent_task_address
dm_os_worker_local_storage broker_address
dm_repl_schemas re_colattr
dm_repl_traninfo begin_time
dm_repl_traninfo commit_time
dm_repl_traninfo error_count
dm_repl_traninfo is_known_cdc_tran
dm_repl_traninfo session_id
dm_repl_traninfo session_phase
dm_tran_active_transactions filestream_transaction_id

3 comments on this post

    • Denis Gobo - February 7, 2008, 8:12 PM

      Thanks Aaron,
      >>but the one I think I like the best is sys.dm_os_info.sqlserver_start_time …
      sys.dm_os_info should be sys.dm_os_sys_info right? (according to the list )
      I guess we can throw away our sp_procoption ProcName,startup,'on' code to track this in the future  😉

    • AaronBertrand - February 7, 2008, 9:03 PM

      Yes, and thanks for the correction.  🙂

    • Moh. Hassan - August 27, 2008, 2:36 PM

      There is a change of  sys.dm_os_sys_info in sql 2008 , and the disappearance the column "cpu_ticks_in_ms" (Number of CPU ticks in milliseconds).
      So prformance dashboard reports is not working.
      Is there a correspondence column for it

Comments are closed.