|
|
|
|
Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.
I was expecting more changes, but as Denis Gobo already pointed out, there is not a single new DMV in SQL Server 2008 R2. There have been some column additions, however, as well as a couple that have increased in size. You might think that I determined this by loading both Books Online collections into some kind of amazing diff tool, or spent hours poring over the sets of DMV definitions. No, there is a much easier way. I installed an instance of 2008, then an instance of R2, and then created a linked server on the 2008 instance to the R2 instance ("SQL2008R2"). Then it was a fairly simple query:
SELECT
[status] = CASE
WHEN l.viewname IS NULL THEN 'new'
ELSE 'changed'
END,
r.viewname,
r.col,
r.[type],
FormerT = l.[type],
r.[precision],
FormerP = l.[precision],
r.scale,
FormerS = l.scale,
r.max_length,
FormerML = l.max_length
FROM
(
SELECT
viewname = v.name,
col = c.name,
t = c.system_type_id,
[type] = t.name,
c.[precision],
c.scale,
c.max_length
FROM
[SQL2008R2].[master].sys.all_columns AS c
INNER JOIN
[SQL2008R2].[master].sys.all_views AS v
ON c.[object_id] = v.[object_id]
INNER JOIN
[SQL2008R2].[master].sys.types AS t
ON t.system_type_id = c.system_type_id
WHERE
v.name LIKE 'dm[_]%'
AND t.name <> 'sysname'
) AS r
LEFT OUTER JOIN
(
SELECT
viewname = v.name,
col = c.name,
t = c.system_type_id,
[type] = t.name,
c.[precision],
c.scale,
c.max_length
FROM
[master].sys.all_columns AS c
INNER JOIN
[master].sys.all_views AS v
ON c.[object_id] = v.[object_id]
INNER JOIN
[master].sys.types AS t
ON t.system_type_id = c.system_type_id
WHERE
v.name LIKE 'dm[_]%'
AND t.name <> 'sysname'
) AS l
ON
r.viewname = l.viewname
AND r.col = l.col
WHERE
l.viewname IS NULL
OR r.t <> COALESCE(l.t, -10)
OR r.[precision] <> COALESCE(l.[precision], -10)
OR r.scale <> COALESCE(l.scale, -10)
OR r.max_length <> COALESCE(l.max_length, -10)
ORDER BY
[status] DESC,
r.viewname,
r.col; |
The results: 
So, 10 new columns, all in the sys.dm_os_* DMVs. For a couple of other DMVs, one column got a little bigger.
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About AaronBertrand
...about me...
|
|
|
|
|