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 |
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
Yes, and thanks for the correction. 🙂
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 😉