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
|