SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. DMVs expose vital diagnostic information which are useful to indicate the overall health of the SQL Server. The list of DMVs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

The SQL Server Operating System (SQLOS) is a layer that lies between the host operating system (Windows OS) and the SQL Server. All interactions of the SQL Server with the underlying operating system are carried out through the SQLOS. SQLOS manages all the operating system services such as memory management, processor and disk interaction, buffer management, hosting external components (e.g. SQL-CLR) etc. Today, we will look at the available SQLOS DMVs and study in detail, the ones introduced in SQL Server 2008 R2/2012.

A full list of documented SQL Server 2012 SQLOS related DMVs is available at: http://msdn.microsoft.com/en-us/library/ms176083.aspx. The DMVs introduced in SQL Server 2008 R2/2012 are:

• sys.dm_os_cluster_properties
• sys.dm_os_memory_broker_clerks
• sys.dm_os_server_diagnostics_log_configurations
• sys.dm_os_windows_info

sys.dm_os_cluster_properties

Introduced In: SQL Server 2012

Purpose: Returns one record with the current SQL Server resource properties (failover response & logging, detection time, etc). When executed on a stand-alone instance (as my test environment is), no records are returned.

Required Permissions: VIEW SERVER STATE

BOL Link: http://msdn.microsoft.com/en-us/library/gg471591.aspx

sys.dm_os_memory_broker_clerks

Introduced In: SQL Server 2012

Purpose: Memory brokers are internal memory management objects that help the SQL Server regulate the memory allocations across all SQL Server components. Please note that the memory allocations are done by the SQLOS – the memory brokers only document and track memory allocations. Generically speaking, the components that consume memory are called memory clerks. This DMV therefore provides details about the memory allocations of SQL Server’s internal memory consumers.

Required Permissions: VIEW SERVER STATE

BOL Link: N/A – undocumented.

SELECT mbc.clerk_name,
       mbc.total_kb,
       mbc.simulated_kb,
       mbc.simulation_benefit,
       mbc.internal_benefit,
       mbc.external_benefit,
       mbc.value_of_memory,
       mbc.periodic_freed_kb,
       mbc.internal_freed_kb
FROM sys.dm_os_memory_broker_clerks AS mbc;

sys.dm_os_server_diagnostics_log_configurations

Introduced In: SQL Server 2012

Purpose: Returns one record for the SQL Server failover cluster diagnostic log. By default, the log is available in the SQL Server default log folder and the DMV also indicates whether diagnostic logging is enabled, number of logs and default size.

Even on a standalone server, one record is returned.

Required permissions: VIEW SERVER STATE

BOL Link: http://msdn.microsoft.com/en-us/library/gg471697.aspx

SELECT dlc.is_enabled,
       dlc.path,
       dlc.max_size,
       dlc.max_files
FROM sys.dm_os_server_diagnostics_log_configurations AS dlc;

sys.dm_os_windows_info

Introduced In: SQL Server 2008 R2

Purpose: Returns the service pack/release information of the underlying operating system.

Required Permissions: VIEW SERVER STATE

BOL Link: http://technet.microsoft.com/en-us/library/hh204565.aspx

SELECT wi.windows_release,
       wi.windows_service_pack_level,
       wi.windows_sku,
       wi.os_language_version
FROM sys.dm_os_windows_info AS wi;

Here’s the output from my Windows 8 test system:

Until we meet next time,

Be courteous. Drive responsibly.

A while ago, I read a post from Aaron Bertrand (B|T) regarding an appeal to start using semi-colons actively as statement terminators. Semi-colons as statement-terminators has been around for as long as I can remember, however, they have only been made mandatory in the newer statements:

• A semi-colon is required before the WITH clause (as in Common Table Expressions, CTE)
• The MERGE statement must end with a semi-colon
• In SQL 2012, the THROW statement also requires that the preceding statement ends with a semi-colon

Because the semi-colon is not mandatory, most developers do not use semi-colons in the T-SQL queries that they write. However as Aaron points out, it is already documented in Books On Line that the semi-colon will be a required feature:

Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

For most teams, this would mean to modify almost every line of code and a huge testing & development effort. To mitigate the high development effort and bring reliability in the process, teams may decide to write a small program that would add semi-colons after each statement. But there’s a small catch.

Exception to the rule: The batch separator cannot be suffixed by a semi-colon.

USE AdventureWorks2012;
GO;

The code above would simply fail to compile with the following error.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘GO’.

The reason is quite simple – a T-SQL statement is a sub-set of a batch. Because “GO” is a batch separator, it cannot be suffixed by a statement terminator because an active T-SQL batch would not exist at that time.

Reference:

• Ladies and Gentlemen, start your semi-colons! by Aaron Bertrand (B|T)
• T-SQL Syntax Conventions
• “GO” as Batch Separator – Customize batch separator in SSMS & SQLCMD

Until we meet next time,

Be courteous. Drive responsibly.

Of late, the changes made to the Catalog Views & Dynamic Management Views (DMVs) in SQL Server 2012 have attracted my attention. One of the major factors in this attraction is the rate at which new DMVs and changes to existing DMVs in Microsoft SQL Server are being rolled out. DMVs are some of the the most powerful features of Microsoft SQL Server. The list of DVMs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

In the post today, I will be discussing the DMVs related to the SQL Server installation introduced new to SQL Server 2008 R2/2012:

• sys.dm_server_services
  
• sys.dm_server_registry
  
• sys.dm_server_memory_dumps

SQL Server 2008 R2/2012 Installation DMVs

sys.dm_server_services

I have written about this DMV in the past as well. The DMV that provides information about the SQL Server and the SQL Server Agent services in the current instance of SQL Server is sys.dm_server_services. Along with information about service status, service account, process_id and clustered information, this DMV also provides information about when the SQL Server and the SQL Server Agent services were last started up!

SELECT * FROM sys.dm_server_services;

On my test system, the output of this query is shown in the screen-shot below, and consists of:

1. Service Name
   
2. Startup Type (enumeration)
   
3. Startup Description
   
4. Status (enumeration)
   
5. Status description
   
6. Process ID
   
7. Last Startup Time
   
8. Service Account
   
9. Service File Name & startup parameter
   
10. Is instance clustered?
    
11. If clustered, the cluster node name is also present

sys.dm_server_registry

For a given SQL Server instance, this DMV returns one row for each configuration and installation related registry key. For example, the following query fetches the current version of the given SQL Server instance from the registry.

SELECT dsr.registry_key,

       dsr.value_name,

       dsr.value_data

FROM sys.dm_server_registry AS dsr

WHERE dsr.value_name = ‘CurrentVersion’;

The output consists of the following three columns:

• Registry Key
  
• Value Name
  
• Value data

sys.dm_server_memory_dumps

This DMV returns one row for each memory dump file generated by the SQL Server Database Engine. This can be useful in troubleshooting potential issues with the SQL Server installation and/or operation. No records returned by this DMV is the ideal scenario.

SELECT smd.filename,

       smd.creation_time,

       smd.size_in_bytes

FROM sys.dm_server_memory_dumps AS smd;

In my environment, no records were returned by the DMV because my SQL Server has not yet crashed. However, the following columns are available for the results to be displayed in:

• File Name
  
• File creation time
  
• File Size in Bytes

Permissions

All the three DMVs require that the user must have at least VIEW SERVER STATE permissions. This is because they not only expose the details about the SQL server, they also reach out into the operating system of the host to read the registry.

Further Reading

• sys.dm_server_services – http://msdn.microsoft.com/en-us/library/hh204542.aspx
  
• sys.dm_server_registry – http://msdn.microsoft.com/en-us/library/hh204561.aspx
  
• sys.dm_server_memory_dumps – http://msdn.microsoft.com/en-us/library/hh204543.aspx
  
• Using sys.dm_server_services to know when was the SQL Server instance restarted? – http://bit.ly/UilzuG

Until we meet next time,

Be courteous. Drive responsibly.

Recently, one of my colleagues asked me an interesting question. They had a database with multiple files spread across various file-groups. The logical file names of these files were based on the product/company name. During a re-branding effort, they wanted to know if there was a way to change the logical file names and avoid transferring data over to a new database.

Here’s an example of the requirement. The script below creates a database with 3 files – a primary & secondary data file and a log file – all prefixed by the company name “AdventureWorks”.

/******************************************************************************
Create a test database
******************************************************************************/
USE master;
GO
IF EXISTS (SELECT * FROM master.sys.databases WHERE name = 'FileRenameTest')
BEGIN
    ALTER DATABASE FileRenameTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE FileRenameTest;
END;
GO

CREATE DATABASE [FileRenameTest]
    ON 
    PRIMARY(NAME = [AdventureWorks_Default], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB),
           (NAME = [AdventureWorks_Data], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest_Data1.mdf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 51200 KB)
    LOG ON (NAME = [AdventureWorks_Log], 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2K12MSSQLDATAFileRenameTest.ldf', 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 102400 KB);
GO

The requirement now is to rename the database logical files such that they begin with the new name – “Contosso”. To do so, we will be using the MODIFY FILE clause of the ALTER DATABASE statement:

/******************************************************************************
Rename logical files
******************************************************************************/
--Primary Data File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Default], NEWNAME = [Contosso_Default]);
GO

--Secondary Data File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Data], NEWNAME = [Contosso_Data]);
GO

--Log File
ALTER DATABASE FileRenameTest
MODIFY FILE (NAME = [AdventureWorks_Log], NEWNAME = [Contosso_Log]);
GO

Now, checking the file names using sys.database_files helps us to confirm that the logical file names have indeed changed.

/******************************************************************************
Check the logical file names
******************************************************************************/
USE FileRenameTest;
GO
SELECT file_id AS FileId,
       type AS FileType,
       name AS FileLogicalName,
       physical_name AS FilePhysicalName
FROM FileRenameTest.sys.database_files;
GO

References:

• ALTER DATABASE, File & Filegroup options: http://msdn.microsoft.com/en-us/library/bb522469.aspx
• sys.database_files: http://msdn.microsoft.com/en-us/library/ms174397.aspx

Until we meet next time,

Be courteous. Drive responsibly.

In the first couple of months after I started blogging, I wrote about Catalog Views & Dynamic Management Views (DMVs). While DMVs are some of the most powerful features of Microsoft SQL Server, they are still under-utilized due to the lack of awareness around them. Nevertheless, each release of Microsoft SQL Server comes with a new set of DMVs being introduced and existing DMVs being modified to expose more valuable information/details, and therefore, I decided to focus on DMVs once a week for the next couple of weeks.

To kick-off the series, I will be presenting today a comparison of the DMVs in SQL Server 2008 v/s those in SQL Server 2012. A total of 382 DMVs, catalog views, compatibility views and system tables exist in SQL Server 2012. While it may note be possible to discuss each and every one of these DMVs, I will be discussing the new DMVs and significant changes to existing ones.

I hope you find the series useful. Useful scripts presented as part of the series will also be made available on the SCRIPTS module (http://beyondrelational.com/modules/30/scripts.aspx?s=stream&tab=scripts) of BeyondRelational.com.

SQL Server 2012	SQL Server 2008	Type	Family
availability_databases_cluster	New in SQL Server 2012	Catalog Views	AlwaysOn
availability_group_listener_ip_addresses	New in SQL Server 2012	Catalog Views	AlwaysOn
availability_group_listeners	New in SQL Server 2012	Catalog Views	AlwaysOn
availability_groups	New in SQL Server 2012	Catalog Views	AlwaysOn
availability_groups_cluster	New in SQL Server 2012	Catalog Views	AlwaysOn
availability_read_only_routing_lists	New in SQL Server 2012	Catalog Views	AlwaysOn
availability_replicas	New in SQL Server 2012	Catalog Views	AlwaysOn
dm_hadr_auto_page_repair	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_availability_group_states	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_availability_replica_cluster_nodes	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_availability_replica_cluster_states	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_availability_replica_states	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_cluster	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_cluster_members	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_cluster_networks	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_database_replica_cluster_states	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_database_replica_states	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_instance_node_map	New in SQL Server 2012	DMV	AlwaysOn
dm_hadr_name_id_map	New in SQL Server 2012	DMV	AlwaysOn
dm_logpool_hashentries	New in SQL Server 2012	DMV	AlwaysOn
dm_logpool_stats	New in SQL Server 2012	DMV	AlwaysOn
dm_tcp_listener_states	New in SQL Server 2012	DMV	AlwaysOn
dm_audit_actions	dm_audit_actions	DMV	Audit
dm_audit_class_type_map	dm_audit_class_type_map	DMV	Audit
dm_server_audit_status	dm_server_audit_status	DMV	Audit
dm_cdc_errors	dm_cdc_errors	DMV	CDC
dm_cdc_log_scan_sessions	dm_cdc_log_scan_sessions	DMV	CDC
change_tracking_databases	change_tracking_databases	Catalog Views	Change Tracking
change_tracking_tables	change_tracking_tables	Catalog Views	Change Tracking
column_store_dictionaries	New in SQL Server 2012	Catalog Views	ColumnStore
column_store_segments	New in SQL Server 2012	Catalog Views	ColumnStore
configurations	configurations	Catalog Views	Configuration
fulltext_languages	fulltext_languages	Catalog Views	Configuration
trace_categories	trace_categories	Catalog Views	Configuration
trace_columns	trace_columns	Catalog Views	Configuration
trace_event_bindings	trace_event_bindings	Catalog Views	Configuration
trace_events	trace_events	Catalog Views	Configuration
trace_subclass_values	trace_subclass_values	Catalog Views	Configuration
traces	traces	Catalog Views	Configuration
data_spaces	data_spaces	Catalog Views	Data Spaces
destination_data_spaces	destination_data_spaces	Catalog Views	Data Spaces
filegroups	filegroups	Catalog Views	Data Spaces
partition_schemes	partition_schemes	Catalog Views	Data Spaces
backup_devices	backup_devices	Catalog Views	Database
database_files	database_files	Catalog Views	Database
database_mirroring	database_mirroring	Catalog Views	Database
database_mirroring_witnesses	database_mirroring_witnesses	Catalog Views	Database
database_recovery_status	database_recovery_status	Catalog Views	Database
databases	databases	Catalog Views	Database
master_files	master_files	Catalog Views	Database
schemas	schemas	Catalog Views	Database
dm_db_file_space_usage	dm_db_file_space_usage	DMV	Database
dm_db_fts_index_physical_stats	New in SQL Server 2012	DMV	Database
dm_db_index_usage_stats	dm_db_index_usage_stats	DMV	Database
dm_db_log_space_usage	New in SQL Server 2012	DMV	Undocumented
dm_db_mirroring_auto_page_repair	dm_db_mirroring_auto_page_repair	DMV	Database
dm_db_mirroring_connections	dm_db_mirroring_connections	DMV	Database
dm_db_mirroring_past_actions	dm_db_mirroring_past_actions	DMV	Database
dm_db_missing_index_details	dm_db_missing_index_details	DMV	Database
dm_db_missing_index_group_stats	dm_db_missing_index_group_stats	DMV	Database
dm_db_missing_index_groups	dm_db_missing_index_groups	DMV	Database
dm_db_persisted_sku_features	dm_db_persisted_sku_features	DMV	Database
dm_db_script_level	dm_db_script_level	DMV	Database
dm_db_session_space_usage	dm_db_session_space_usage	DMV	Database
dm_db_task_space_usage	dm_db_task_space_usage	DMV	Database
dm_db_uncontained_entities	New in SQL Server 2012	DMV	Database
openkeys	openkeys	Catalog Views	Encryption
dm_cryptographic_provider_properties	dm_cryptographic_provider_properties	DMV	Encryption
dm_database_encryption_keys	dm_database_encryption_keys	DMV	Encryption
database_mirroring_endpoints	database_mirroring_endpoints	Catalog Views	Endpoints
endpoint_webmethods	endpoint_webmethods	Catalog Views	Endpoints
endpoints	endpoints	Catalog Views	Endpoints
http_endpoints	http_endpoints	Catalog Views	Endpoints
service_broker_endpoints	service_broker_endpoints	Catalog Views	Endpoints
soap_endpoints	soap_endpoints	Catalog Views	Endpoints
tcp_endpoints	tcp_endpoints	Catalog Views	Endpoints
server_event_notifications	server_event_notifications	Catalog Views	Event Notification
server_events	server_events	Catalog Views	Event Notification
server_event_session_actions	server_event_session_actions	Catalog Views	Extended Events
server_event_session_events	server_event_session_events	Catalog Views	Extended Events
server_event_session_fields	server_event_session_fields	Catalog Views	Extended Events
server_event_session_targets	server_event_session_targets	Catalog Views	Extended Events
server_event_sessions	server_event_sessions	Catalog Views	Extended Events
dm_xe_map_values	dm_xe_map_values	DMV	Extended Events
dm_xe_object_columns	dm_xe_object_columns	DMV	Extended Events
dm_xe_objects	dm_xe_objects	DMV	Extended Events
dm_xe_packages	dm_xe_packages	DMV	Extended Events
dm_xe_session_event_actions	dm_xe_session_event_actions	DMV	Extended Events
dm_xe_session_events	dm_xe_session_events	DMV	Extended Events
dm_xe_session_object_columns	dm_xe_session_object_columns	DMV	Extended Events
dm_xe_session_targets	dm_xe_session_targets	DMV	Extended Events
dm_xe_sessions	dm_xe_sessions	DMV	Extended Events
extended_properties	extended_properties	Catalog Views	Extended Properties
database_filestream_options	New in SQL Server 2012	Catalog Views	File Stream
filetable_system_defined_objects	New in SQL Server 2012	Catalog Views	File Stream
filetables	New in SQL Server 2012	Catalog Views	File Stream
dm_filestream_file_io_handles	dm_filestream_file_io_handles	DMV	File Stream
dm_filestream_file_io_requests	dm_filestream_file_io_requests	DMV	File Stream
dm_filestream_non_transacted_handles	New in SQL Server 2012	DMV	File Stream
fulltext_catalogs	fulltext_catalogs	Catalog Views	Full-text Search
fulltext_document_types	fulltext_document_types	Catalog Views	Full-text Search
fulltext_index_catalog_usages	fulltext_index_catalog_usages	Catalog Views	Full-text Search
fulltext_index_columns	fulltext_index_columns	Catalog Views	Full-text Search
fulltext_index_fragments	fulltext_index_fragments	Catalog Views	Full-text Search
fulltext_indexes	fulltext_indexes	Catalog Views	Full-text Search
fulltext_semantic_language_statistics_database	New in SQL Server 2012	Catalog Views	Full-text Search
fulltext_semantic_languages	New in SQL Server 2012	Catalog Views	Full-text Search
fulltext_stoplists	fulltext_stoplists	Catalog Views	Full-text Search
fulltext_stopwords	fulltext_stopwords	Catalog Views	Full-text Search
fulltext_system_stopwords	fulltext_system_stopwords	Catalog Views	Full-text Search
registered_search_properties	New in SQL Server 2012	Catalog Views	Full-text Search
registered_search_property_lists	New in SQL Server 2012	Catalog Views	Full-text Search
dm_fts_active_catalogs	dm_fts_active_catalogs	DMV	Full-text Search
dm_fts_fdhosts	dm_fts_fdhosts	DMV	Full-text Search
dm_fts_index_population	dm_fts_index_population	DMV	Full-text Search
dm_fts_memory_buffers	dm_fts_memory_buffers	DMV	Full-text Search
dm_fts_memory_pools	dm_fts_memory_pools	DMV	Full-text Search
dm_fts_outstanding_batches	dm_fts_outstanding_batches	DMV	Full-text Search
dm_fts_population_ranges	dm_fts_population_ranges	DMV	Full-text Search
dm_fts_semantic_similarity_population	New in SQL Server 2012	DMV	Full-text Search
dm_io_backup_tapes	dm_io_backup_tapes	DMV	I/O
dm_io_cluster_shared_drives	dm_io_cluster_shared_drives	DMV	I/O
dm_io_pending_io_requests	dm_io_pending_io_requests	DMV	I/O
dm_server_memory_dumps	New in SQL Server 2012	DMV	Installation
dm_server_registry	New in SQL Server 2012	DMV	Installation
dm_server_services	New in SQL Server 2012	DMV	Installation
all_columns	all_columns	System View	Internal
all_objects	all_objects	System View	Internal
all_parameters	all_parameters	System View	Internal
all_sql_modules	all_sql_modules	System View	Internal
all_views	all_views	System View	Internal
system_internals_allocation_units	system_internals_allocation_units	System View	Internal
system_internals_partition_columns	system_internals_partition_columns	System View	Internal
system_internals_partitions	system_internals_partitions	System View	Internal
system_objects	system_objects	System View	Internal
system_parameters	system_parameters	System View	Internal
system_sql_modules	system_sql_modules	System View	Internal
system_views	system_views	System View	Internal
systypes	systypes	System View	Internal
sysusers	sysusers	System View	Internal
type_assembly_usages	type_assembly_usages	System View	Internal
user_token	user_token	System View	Internal
via_endpoints	via_endpoints	System View	Internal
linked_logins	linked_logins	Catalog Views	Linked Servers
remote_logins	remote_logins	Catalog Views	Linked Servers
servers	servers	Catalog Views	Linked Servers
messages	messages	Catalog Views	Messages
sysaltfiles	sysaltfiles	Compatibility Views	N/A
syscacheobjects	syscacheobjects	Compatibility Views	N/A
syscharsets	syscharsets	Compatibility Views	N/A
syscolumns	syscolumns	Compatibility Views	N/A
syscomments	syscomments	Compatibility Views	N/A
sysconfigures	sysconfigures	Compatibility Views	N/A
sysconstraints	sysconstraints	Compatibility Views	N/A
syscurconfigs	syscurconfigs	Compatibility Views	N/A
sysdatabases	sysdatabases	Compatibility Views	N/A
sysdepends	sysdepends	Compatibility Views	N/A
sysdevices	sysdevices	Compatibility Views	N/A
sysfilegroups	sysfilegroups	Compatibility Views	N/A
sysfiles	sysfiles	Compatibility Views	N/A
sysforeignkeys	sysforeignkeys	Compatibility Views	N/A
sysfulltextcatalogs	sysfulltextcatalogs	Compatibility Views	N/A
sysindexes	sysindexes	Compatibility Views	N/A
sysindexkeys	sysindexkeys	Compatibility Views	N/A
syslanguages	syslanguages	Compatibility Views	N/A
syslockinfo	syslockinfo	Compatibility Views	N/A
syslogins	syslogins	Compatibility Views	N/A
sysmembers	sysmembers	Compatibility Views	N/A
sysmessages	sysmessages	Compatibility Views	N/A
sysobjects	sysobjects	Compatibility Views	N/A
sysoledbusers	sysoledbusers	Compatibility Views	N/A
sysopentapes	sysopentapes	Compatibility Views	N/A
sysperfinfo	sysperfinfo	Compatibility Views	N/A
syspermissions	syspermissions	Compatibility Views	N/A
sysprocesses	sysprocesses	Compatibility Views	N/A
sysprotects	sysprotects	Compatibility Views	N/A
sysreferences	sysreferences	Compatibility Views	N/A
sysremotelogins	sysremotelogins	Compatibility Views	N/A
sysservers	sysservers	Compatibility Views	N/A
allocation_units	allocation_units	Catalog Views	Object catalog
assembly_modules	assembly_modules	Catalog Views	Object catalog
check_constraints	check_constraints	Catalog Views	Object catalog
columns	columns	Catalog Views	Object catalog
computed_columns	computed_columns	Catalog Views	Object catalog
default_constraints	default_constraints	Catalog Views	Object catalog
event_notifications	event_notifications	Catalog Views	Object catalog
events	events	Catalog Views	Object catalog
extended_procedures	extended_procedures	Catalog Views	Object catalog
foreign_key_columns	foreign_key_columns	Catalog Views	Object catalog
foreign_keys	foreign_keys	Catalog Views	Object catalog
function_order_columns	function_order_columns	Catalog Views	Object catalog
identity_columns	identity_columns	Catalog Views	Object catalog
index_columns	index_columns	Catalog Views	Object catalog
indexes	indexes	Catalog Views	Object catalog
internal_tables	internal_tables	Catalog Views	Object catalog
key_constraints	key_constraints	Catalog Views	Object catalog
numbered_procedure_parameters	numbered_procedure_parameters	Catalog Views	Object catalog
numbered_procedures	numbered_procedures	Catalog Views	Object catalog
objects	objects	Catalog Views	Object catalog
parameters	parameters	Catalog Views	Object catalog
partitions	partitions	Catalog Views	Object catalog
procedures	procedures	Catalog Views	Object catalog
sequences	New in SQL Server 2012	Catalog Views	Object catalog
service_queues	service_queues	Catalog Views	Object catalog
sql_dependencies	sql_dependencies	Catalog Views	Object catalog
sql_expression_dependencies	sql_expression_dependencies	Catalog Views	Object catalog
sql_modules	sql_modules	Catalog Views	Object catalog
stats	stats	Catalog Views	Object catalog
stats_columns	stats_columns	Catalog Views	Object catalog
synonyms	synonyms	Catalog Views	Object catalog
system_columns	system_columns	Catalog Views	Object catalog
table_types	table_types	Catalog Views	Object catalog
tables	tables	Catalog Views	Object catalog
trigger_event_types	trigger_event_types	Catalog Views	Object catalog
trigger_events	trigger_events	Catalog Views	Object catalog
triggers	triggers	Catalog Views	Object catalog
views	views	Catalog Views	Object catalog
partition_functions	partition_functions	Catalog Views	Partitioning
partition_parameters	partition_parameters	Catalog Views	Partitioning
partition_range_values	partition_range_values	Catalog Views	Partitioning
dm_db_partition_stats	dm_db_partition_stats	DMV	Partitioning
plan_guides	plan_guides	Catalog Views	Query Execution
dm_exec_background_job_queue	dm_exec_background_job_queue	DMV	Query Execution
dm_exec_background_job_queue_stats	dm_exec_background_job_queue_stats	DMV	Query Execution
dm_exec_cached_plans	dm_exec_cached_plans	DMV	Query Execution
dm_exec_connections	dm_exec_connections	DMV	Query Execution
dm_exec_procedure_stats	dm_exec_procedure_stats	DMV	Query Execution
dm_exec_query_memory_grants	dm_exec_query_memory_grants	DMV	Query Execution
dm_exec_query_optimizer_info	dm_exec_query_optimizer_info	DMV	Query Execution
dm_exec_query_resource_semaphores	dm_exec_query_resource_semaphores	DMV	Query Execution
dm_exec_query_stats	dm_exec_query_stats	DMV	Query Execution
dm_exec_query_transformation_stats	dm_exec_query_transformation_stats	DMV	Query Execution
dm_exec_requests	dm_exec_requests	DMV	Query Execution
dm_exec_sessions	dm_exec_sessions	DMV	Query Execution
dm_exec_trigger_stats	dm_exec_trigger_stats	DMV	Query Execution
event_notification_event_types	event_notification_event_types	Catalog Views	Query Notification
dm_qn_subscriptions	dm_qn_subscriptions	DMV	Query Notification
dm_repl_articles	dm_repl_articles	DMV	Replication
dm_repl_schemas	dm_repl_schemas	DMV	Replication
dm_repl_tranhash	dm_repl_tranhash	DMV	Replication
dm_repl_traninfo	dm_repl_traninfo	DMV	Replication
resource_governor_configuration	resource_governor_configuration	Catalog Views	Resource Governor
resource_governor_resource_pool_affinity	New in SQL Server 2012	Catalog Views	Resource Governor
resource_governor_resource_pools	resource_governor_resource_pools	Catalog Views	Resource Governor
resource_governor_workload_groups	resource_governor_workload_groups	Catalog Views	Resource Governor
dm_resource_governor_configuration	dm_resource_governor_configuration	DMV	Resource Governor
dm_resource_governor_resource_pool_affinity	New in SQL Server 2012	DMV	Resource Governor
dm_resource_governor_resource_pools	dm_resource_governor_resource_pools	DMV	Resource Governor
dm_resource_governor_workload_groups	dm_resource_governor_workload_groups	DMV	Resource Governor
assembly_types	assembly_types	Catalog Views	Scalar Types
column_type_usages	column_type_usages	Catalog Views	Scalar Types
parameter_type_usages	parameter_type_usages	Catalog Views	Scalar Types
types	types	Catalog Views	Scalar Types
asymmetric_keys	asymmetric_keys	Catalog Views	Security
certificates	certificates	Catalog Views	Security
credentials	credentials	Catalog Views	Security
crypt_properties	crypt_properties	Catalog Views	Security
cryptographic_providers	cryptographic_providers	Catalog Views	Security
database_audit_specification_details	database_audit_specification_details	Catalog Views	Security
database_audit_specifications	database_audit_specifications	Catalog Views	Security
database_permissions	database_permissions	Catalog Views	Security
database_principals	database_principals	Catalog Views	Security
database_role_members	database_role_members	Catalog Views	Security
key_encryptions	key_encryptions	Catalog Views	Security
login_token	login_token	Catalog Views	Security
master_key_passwords	master_key_passwords	Catalog Views	Security
Not available in SQL Sever 2012	database_principal_aliases	Catalog Views	Security
securable_classes	securable_classes	Catalog Views	Security
server_audit_specification_details	server_audit_specification_details	Catalog Views	Security
server_audit_specifications	server_audit_specifications	Catalog Views	Security
server_audits	server_audits	Catalog Views	Security
server_file_audits	server_file_audits	Catalog Views	Security
server_permissions	server_permissions	Catalog Views	Security
server_principal_credentials	server_principal_credentials	Catalog Views	Security
server_principals	server_principals	Catalog Views	Security
server_role_members	server_role_members	Catalog Views	Security
sql_logins	sql_logins	Catalog Views	Security
symmetric_keys	symmetric_keys	Catalog Views	Security
system_components_surface_area_configuration	system_components_surface_area_configuration	Catalog Views	Security
server_sql_modules	server_sql_modules	Catalog Views	Server Triggers
server_trigger_events	server_trigger_events	Catalog Views	Server Triggers
server_triggers	server_triggers	Catalog Views	Server Triggers
conversation_endpoints	conversation_endpoints	Catalog Views	Service Broker
conversation_groups	conversation_groups	Catalog Views	Service Broker
conversation_priorities	conversation_priorities	Catalog Views	Service Broker
message_type_xml_schema_collection_usages	message_type_xml_schema_collection_usages	Catalog Views	Service Broker
remote_service_bindings	remote_service_bindings	Catalog Views	Service Broker
routes	routes	Catalog Views	Service Broker
service_contract_message_usages	service_contract_message_usages	Catalog Views	Service Broker
service_contract_usages	service_contract_usages	Catalog Views	Service Broker
service_contracts	service_contracts	Catalog Views	Service Broker
service_message_types	service_message_types	Catalog Views	Service Broker
service_queue_usages	service_queue_usages	Catalog Views	Service Broker
services	services	Catalog Views	Service Broker
transmission_queue	transmission_queue	Catalog Views	Service Broker
dm_broker_activated_tasks	dm_broker_activated_tasks	DMV	Service Broker
dm_broker_connections	dm_broker_connections	DMV	Service Broker
dm_broker_forwarded_messages	dm_broker_forwarded_messages	DMV	Service Broker
dm_broker_queue_monitors	dm_broker_queue_monitors	DMV	Service Broker
spatial_index_tessellations	spatial_index_tessellations	Catalog Views	Spatial Datatypes
spatial_indexes	spatial_indexes	Catalog Views	Spatial Datatypes
spatial_reference_systems	spatial_reference_systems	Catalog Views	Spatial Datatypes
assemblies	assemblies	Catalog Views	SQLCLR
assembly_files	assembly_files	Catalog Views	SQLCLR
assembly_references	assembly_references	Catalog Views	SQLCLR
module_assembly_usages	module_assembly_usages	Catalog Views	SQLCLR
server_assembly_modules	server_assembly_modules	Catalog Views	SQLCLR
dm_clr_appdomains	dm_clr_appdomains	DMV	SQLCLR
dm_clr_loaded_assemblies	dm_clr_loaded_assemblies	DMV	SQLCLR
dm_clr_properties	dm_clr_properties	DMV	SQLCLR
dm_clr_tasks	dm_clr_tasks	DMV	SQLCLR
dm_os_buffer_descriptors	dm_os_buffer_descriptors	DMV	SQLOS
dm_os_child_instances	dm_os_child_instances	DMV	SQLOS
dm_os_cluster_nodes	dm_os_cluster_nodes	DMV	SQLOS
dm_os_cluster_properties	New in SQL Server 2012	DMV	SQLOS
dm_os_dispatcher_pools	dm_os_dispatcher_pools	DMV	SQLOS
dm_os_dispatchers	dm_os_dispatchers	DMV	SQLOS
dm_os_hosts	dm_os_hosts	DMV	SQLOS
dm_os_latch_stats	dm_os_latch_stats	DMV	SQLOS
dm_os_loaded_modules	dm_os_loaded_modules	DMV	SQLOS
dm_os_memory_allocations	dm_os_memory_allocations	DMV	SQLOS
dm_os_memory_broker_clerks	New in SQL Server 2012	DMV	SQLOS
dm_os_memory_brokers	dm_os_memory_brokers	DMV	SQLOS
dm_os_memory_cache_clock_hands	dm_os_memory_cache_clock_hands	DMV	SQLOS
dm_os_memory_cache_counters	dm_os_memory_cache_counters	DMV	SQLOS
dm_os_memory_cache_entries	dm_os_memory_cache_entries	DMV	SQLOS
dm_os_memory_cache_hash_tables	dm_os_memory_cache_hash_tables	DMV	SQLOS
dm_os_memory_clerks	dm_os_memory_clerks	DMV	SQLOS
dm_os_memory_node_access_stats	dm_os_memory_node_access_stats	DMV	SQLOS
dm_os_memory_nodes	dm_os_memory_nodes	DMV	SQLOS
dm_os_memory_objects	dm_os_memory_objects	DMV	SQLOS
dm_os_memory_pools	dm_os_memory_pools	DMV	SQLOS
dm_os_nodes	dm_os_nodes	DMV	SQLOS
dm_os_performance_counters	dm_os_performance_counters	DMV	SQLOS
dm_os_process_memory	dm_os_process_memory	DMV	SQLOS
dm_os_ring_buffers	dm_os_ring_buffers	DMV	SQLOS
dm_os_schedulers	dm_os_schedulers	DMV	SQLOS
dm_os_server_diagnostics_log_configurations	New in SQL Server 2012	DMV	SQLOS
dm_os_spinlock_stats	dm_os_spinlock_stats	DMV	SQLOS
dm_os_stacks	dm_os_stacks	DMV	SQLOS
dm_os_sublatches	dm_os_sublatches	DMV	SQLOS
dm_os_sys_info	dm_os_sys_info	DMV	SQLOS
dm_os_sys_memory	dm_os_sys_memory	DMV	SQLOS
dm_os_tasks	dm_os_tasks	DMV	SQLOS
dm_os_threads	dm_os_threads	DMV	SQLOS
dm_os_virtual_address_dump	dm_os_virtual_address_dump	DMV	SQLOS
dm_os_wait_stats	dm_os_wait_stats	DMV	SQLOS
dm_os_waiting_tasks	dm_os_waiting_tasks	DMV	SQLOS
dm_os_windows_info	New in SQL Server 2012	DMV	SQLOS
dm_os_worker_local_storage	dm_os_worker_local_storage	DMV	SQLOS
dm_os_workers	dm_os_workers	DMV	SQLOS
dm_tran_active_snapshot_database_transactions	dm_tran_active_snapshot_database_transactions	DMV	Transactions
dm_tran_active_transactions	dm_tran_active_transactions	DMV	Transactions
dm_tran_commit_table	dm_tran_commit_table	DMV	Transactions
dm_tran_current_snapshot	dm_tran_current_snapshot	DMV	Transactions
dm_tran_current_transaction	dm_tran_current_transaction	DMV	Transactions
dm_tran_database_transactions	dm_tran_database_transactions	DMV	Transactions
dm_tran_locks	dm_tran_locks	DMV	Transactions
dm_tran_session_transactions	dm_tran_session_transactions	DMV	Transactions
dm_tran_top_version_generators	dm_tran_top_version_generators	DMV	Transactions
dm_tran_transactions_snapshot	dm_tran_transactions_snapshot	DMV	Transactions
dm_tran_version_store	dm_tran_version_store	DMV	Transactions
selective_xml_index_namespaces	New in SQL Server 2012	Catalog Views	Undocumented
selective_xml_index_paths	New in SQL Server 2012	Catalog Views	Undocumented
syscursorcolumns	syscursorcolumns	System Tables	Undocumented
syscursorrefs	syscursorrefs	System Tables	Undocumented
syscursors	syscursors	System Tables	Undocumented
syscursortables	syscursortables	System Tables	Undocumented
column_xml_schema_collection_usages	column_xml_schema_collection_usages	Catalog Views	XML
parameter_xml_schema_collection_usages	parameter_xml_schema_collection_usages	Catalog Views	XML
xml_indexes	xml_indexes	Catalog Views	XML
xml_schema_attributes	xml_schema_attributes	Catalog Views	XML
xml_schema_collections	xml_schema_collections	Catalog Views	XML
xml_schema_component_placements	xml_schema_component_placements	Catalog Views	XML
xml_schema_components	xml_schema_components	Catalog Views	XML
xml_schema_elements	xml_schema_elements	Catalog Views	XML
xml_schema_facets	xml_schema_facets	Catalog Views	XML
xml_schema_model_groups	xml_schema_model_groups	Catalog Views	XML
xml_schema_namespaces	xml_schema_namespaces	Catalog Views	XML
xml_schema_types	xml_schema_types	Catalog Views	XML
xml_schema_wildcard_namespaces	xml_schema_wildcard_namespaces	Catalog Views	XML
xml_schema_wildcards	xml_schema_wildcards	Catalog Views	XML

[EDIT: February 10, 2013; 10:30PM IST]: Rectified categorizing inconsistencies in the table.

Until we meet next time,

Be courteous. Drive responsibly.