SQL Server 2012 - New and Modified Dynamic Management Views and Functions - DMVs
That's just about the limit. In Books Online for SQL Server 2012 (BOL) by reading What's New (Database Engine), I came around the topic Manageability Enhancements (Database Engine) and its paragraph New and Modified Dynamic Management Views and Functions, and I only see six added or modified system views. And I think by myself, these are the same six DMVs I had read in CTP Denali BOL - no update?
But I knew that there are more new DMVs. I do not mean the AlwaysOn Availability Groups Dynamic Management Views and Functions of the AlwaysOn feature etc. ... - So, I think by myself - figure out!
OK, I have a SQL Server 2008 R2 instance and a SQL Server 2012 instance (both are only for testing, no production environment!). But I don't want to query each instance and copy the result for further investigations in excel - like this way:
SELECT name, type, type_desc
FROM sys.system_objects WITH (NOLOCK)
WHERE name LIKE 'dm_%'
ORDER BY name OPTION (RECOMPILE);
New Dynamic Management Views and Functions in SQL Server 2012
I create a valid linked server point form SQL Server 2012 to SQL Server 2008 R2 (@server = N'SQL2008R2'), then I run below query from SQL Server 2012 - which will find DMVs that did not exists in 2008 R2 ('SQL2008R2') - comparing the datasets in sys.system_objects.
USE [master]; --- execute on SQL Server 2012
N'sys.' + d.[name] AS [DMV NAME],
d.[type] AS [DMV TYPE],
d.type_desc AS [DMV TYPE DESC]
[master].sys.system_objects AS d WITH (NOLOCK)
LEFT OUTER JOIN --- LinkedServer SQL Server 2008 R2
[SQL2008R2].[master].sys.system_objects AS r WITH (NOLOCK)
d.[name] = r.[name] ---* collate Latin1_General_CI_AS
AND d.[schema_id] = r.[schema_id]
r.[name] IS NULL
AND d.[schema_id] = 4
AND d.[is_ms_shipped] = 1
AND d.[name] like 'dm_%'
d.[name] OPTION (RECOMPILE);
Linked Query Result
I get this resultset, and YES SQL Server 2012 has really 36 new Dynamic Management Views and Functions.
... Now you can query Books Online for SQL Server 2012 ....
To struggle through these new Dynamic Management Views and Functions in SQL Server 2012, will be an ongoing and hard work :-)
I wish you a nice day,
|Print article||This entry was posted by tosc on 2012-09-13 at 15:36:00 . Follow any responses to this post through RSS 2.0.|
backup «best practices» books bug ctp «cumulative update» denali dmv ebook humor index indexoptimize integrity kbfix links loginproperty maintenance «ms sql server 2008» pass performance php profiler «reporting services» reviews «ross mistry» rtm serverproperty «service pack» «service pack 2» «service pack 3» «service packs» sharepoint «sql server» «sql server 2005» «sql server 2008 r2» «sql server 2012» «sql server 2014» sqlcat sqlpass «sqlpass franken» ssms ssmstoolspack sysadmin «system views» t-sql «technical note» tempdb tools upgrade whitepapers