16 Changed Dynamic Management Views in SQL Server 2008

AaronBertrand — Thu, 07 Feb 2008 13:46:00 GMT

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

Search results matching tags 'DMVs', 'SQL Server 2008', 'dynamic management views', and 'katmai'

16 Changed Dynamic Management Views in SQL Server 2008