Search results matching tags 'DMVs' and 'SQL 11'

Connect Digest : 2011-05-02

AaronBertrand — Mon, 02 May 2011 11:15:00 GMT

This week, after seeing a lot of the DMV enhancements made in SQL Server 2008 R2 SP1, I thought I would take a look at some dynamic management view/function requests.

Page split personality

Michael Zilberstein blogged on Monday about how Extended Events and the transaction log differ in the way they report page split information, and that it would be useful to have accurate information about these events available in the DMVs. So he filed this Connect item:
#664900 : Please add page splits data per index, index level and page split type
I asked for a very similar item back in 2008, but thought that it belonged in sys.dm_db_index_physical_stats:
#388403 : Include page split information in sys.dm_db_index_physical_stats
They've already provided additional info via Extended Events (see #388482 : SQL Server Extended Events Page/Split Event Additions) so, hopefully, they will take our DMV requests seriously as well.

How stale is my database?

Kimberly Tripp (blog | twitter) posted this item, asking for a column in sys.databases to reflect last accessed time (I think it belongs in an existing or new DMV, not in the catalog view). An additional comment on the item suggested a terrific enhancement to this potential feature, where each individual login would be represented with their last access time, making it easy to differentiate when a database is only being accessed by system or background processes.
#659846 : Database last accessed time
They've recently rejected a similar item requesting last accessed time for objects within a database (see #454714 : I should be able to see the last time an object was accessed in sysobjects), though that one had zero votes, so I'm hopeful that Kimberly's request may be deemed both easier to implement and more important to the community.

I'm an E7-8800, what are you?

Glenn Alan Berry (blog | twitter) asked for an additional column on the sys.dm_os_sys_info DMV that reflects the CPU's description (e.g. Xeon X5505). As you may have noticed on his blog, Glenn spends a lot of effort on analyzing performance of workloads on different CPUs, and there are some pretty substantial differences between CPUs that may look quite similar to the average person - especially when seeing vastly different performance on two servers that are otherwise very similar (same amount of RAM, same SAN, etc).
#657435 : Add a processor_description column to sys.dm_os_sys_info in SQL Server Denali
An additional comment requested that the true observed speed would be shown, as opposed (or in addition) to the manufacturer's stated clock speed - the true speed is a number than can vary greatly if you are using power saving options.

You're a word, but are you reserved?

This almost certainly wouldn't be in a DMV, but I still like the idea and it almost fits here. Greg Low (blog | twitter) is requesting a system view of some kind that exposes a list of reserved words - my comment here was that relying on IntelliSense alone is a fool's errand.
#653455 : System View that lists Reserved Words
On a somewhat-but-not-really-related note, Meredith Ryan-Smith (blog | twitter) recently blogged about a workaround she used to export all of the object names from sys.all_objects into a custom dictionary for use in Word, PowerPoint, etc. to avoid the ugly red squiggly lines pointing out that you misspelled sp_server_diagnostics and sp_who2. I think that if the metadata Greg is asking for were made available, then this idea would be easy to extend to reserved words, so that Word also wouldn't try to convince you that perfectly valid "words" like COLUMNSTORE and DATA_COMPRESSION are spelled wrong.

Do you really like DBCC?

Greg Low also requested that the information returned by running DBCC SHOW_STATISTICS be made available through a DMV (another user suggested adding a column like is_stale to sys.stats, but I think a separate DMV makes more sense).
#611155 : DBCC SHOW_STATISTICS info should be available as a DMV
Exposing this information in a DMV would also, presumably, make it easier to access information like STATS_DATE without having to be in the context of that database (see #654972 : STATS_DATE only works as documented when current db is where the tables are).

More changes you might not have noticed in SQL Server 2008 R2 SP1 CTP

AaronBertrand — Mon, 25 Apr 2011 14:28:00 GMT

On Friday, after Microsoft released the CTP, I talked a little bit about the changes you will see in SQL Server 2008 R2 SP1. Namely:

new rowcount-related columns in sys.dm_exec_query_stats;
extended functionality of the FORCESEEK query hint;
a new FORCESCAN query hint;
support for 15,000 partitions (to match new functionality added in SQL Server 2008 SP2);
a new DAC framework for in-place upgrades; and,
a new disk space control in PowerPivot for SharePoint.

Since then, I have been digging a little deeper, as the release notes are pretty vague about the changes, and the Books Online updates have not been published yet - at least not that I have seen (and I don't expect them to be published until after the service pack is RTM).

In addition to the new columns in sys.dm_exec_query_stats, there are some other DMVs that have been added or changed in comparison to the RTM release of SQL Server 2008 R2, and some new Extended Events objects as well. I'll also note below each item description whether the change is present in Denali (initially I thought there would be more, but there is only one case where this has happened for DMVs in CTP1).

New system objects:

All columns for new DMVs, and new columns on existing DMVs:

sys.dm_os_volume_stats

This new Dynamic Management Function provides you with information about the disk subsystem where your database files live. It takes the parameters @DatabaseID and @FileID, making it easy to CROSS APPLY against sys.sysalfiles to get all kinds of information about all of the databases across your instance. You can see free vs. available space, whether the drive supports compression / alternate streams / sparse files, and whether the drive is compressed or read only:
SELECT 
   vs.volume_mount_point, -- e.g. C:\
   vs.volume_id,
   vs.logical_volume_name,
   vs.file_system_type,  -- e.g. NTFS
   db = DB_NAME(f.dbid), 
   [file] = f.[name],
   f.[filename],
   file_size_MB = f.[size] / 128,
   drive_size_MB = vs.total_bytes/1024/1024, 
   drive_free_space_MB = vs.available_bytes/1024/1024, 
   drive_percent_free = CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes),
   vs.supports_compression, 
   vs.supports_alternate_streams, 
   vs.supports_sparse_files, 
   vs.is_read_only, 
   vs.is_compressed
FROM
   sys.sysaltfiles AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.dbid, f.fileid) AS vs
WHERE
   f.dbid < 32767
ORDER BY
   drive_percent_free DESC,
   db,
   [file];
This is much more powerful than the undocumented and unsupported extended procedure xp_fixeddrives, and I assume - though I haven't tested yet - that it will see things like external hard drives, mount points and other specialty devices. Granted, with or without the usage of sys.sysaltfiles, it is only going to tell you about volumes where you actually have at least one database file.

* Not yet in Denali.

sys.sp_db_increased_partitions

This stored procedure was first introduced in SQL Server 2008 SP2, and enables you to create up to 15,000 partitions on a single table (before this change, the maximum was 1,000 partitions). The reason for the change is that some big customers wanted a partition per day, and the built-in limit of 1,000 meant they couldn't even reach three years of data using that structure. The stored procedure takes two parameters: the database name (@dbname sysname) and whether to turn the setting on or off (@increased_partitions varchar(6)). The latter parameter can take any of the following values: 'on', 'off', 'true' or 'false' - I recommend using lower case to be sure the parameters are not misinterpreted on a binary or case-sensitive collation. As an example, to turn on and then off increased partition support for the AdventureWorks database, you can run the following code:
EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'on';

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'off';

-- or

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'true';

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'false';
* Not yet in Denali.

sys.sp_MScheckIsPubOfSub

I assume this stored procedure is not meant for public consumption, but is probably used by other internal stored procedures involving replication. From the comments in the stored procedure:
/*
  *  This proc will discover if the specified subid is a subscriber to the specified pubid.
  *  Currently, this is a one level check, it does not support n-level re-publishing.
*/
The procedure takes two uniqueidentifer input parameters (@pubid and @subid), and returns a single output parameter (@pubOfSub bit).

* Not yet in Denali.

sys.dm_os_windows_info

This new DMV complements sys.dm_os_sys_info with a bit of information about the underlying Windows operating system - version and service pack information, sku and language.
SELECT
   windows_release,
   windows_service_pack_level,
   windows_sku,
   os_language_version
FROM
   sys.dm_os_windows_info;
On my system (US English Windows 7 Ultimate SP1), the following results were returned (the only other windows_sku value I know of for this DMV so far is 28, for Windows 7 Ultimate N):

* Not yet in Denali.

sys.dm_server_memory_dumps

Thankfully I have only had to look into memory dumps a couple of times, but this new DMV will give you a row for each dump that has been generated, including the name of the file, when it was created, and how big it is:

SELECT
   [filename],
   creation_time,
   size_in_bytes
FROM
   sys.dm_server_memory_dumps;
And also thankfully, this query returns 0 rows on all of the systems I have updated to the CTP of Service Pack 1. Note that, unlike almost every other date/time-related column in every DMV in the system, creation_time is returned as datetimeoffset, not datetime. Just something to keep in mind if you are accessing this data programmatically.

* Not yet in Denali.

sys.dm_server_registry

One of the things I've always hated is having to resort to the undocumented and unsupported extended procedure xp_regread. This DMV eliminates the need to use that xp, provided you are after information that is explicitly relevant to the current instance of SQL Server. On this particular instance, I get information about the engine and SQL Server Agent services (including service account names, startup parameters and agent history properties), as well as plenty of network-related information that is very tedious to obtain in current versions (for example, the port(s) in use and whether they are dynamic):
SELECT 
   registry_key,
   value_name,
   value_data
FROM
   sys.dm_server_registry;
I'm not going to show all of the data for this specific instance, but here are the distinct registry keys that are returned for a named instance "SQL2008R2" - this should give you a good idea of the information you can get to without having to trawl through the SQL Server logs, Agent property dialogs, or dig into the registry manually:

* Not yet in Denali.

sys.dm_server_services

In SQL Server 2008, sys.dm_os_sys_info was enhanced with the column sqlserver_start_time which shows when the SQL Server service was started. This new DMV has a row for each server service, and includes the startup time as well the current status, the startup type, the current process_id (making it easy to determine the instance you see in perfmon or task manager on a machine with multiple instances), the account the service is running under, and whether the service is clustered (which is returned as an nvarchar(1), for some strange reason). Like the new registry DMV, you can now get to this information programmatically and a whole lot easier than you could do in previous versions. Here is an example from my system, which is only currently running SQL Server and SQL Server Agent (I truncated some column names to get the image to fit into this post):
SELECT
   servicename,
   [startup] = startup_type_desc,
   [status] = status_desc,
   pid = process_id,
   last_startup_time,
   service_account,
   [clus] = is_clustered
FROM
   sys.dm_server_services;
Results:

There are two things to note about last_startup_time for the core SQL Server service: (1) it is exposed as a datetimeoffset data type, so it includes offset information from UTC, and (2) it may be a second or two off from the sqlserver_start_time value in sys.dm_os_sys_info. I don't have an explanation for the latter, but I did complain about both the missing Agent startup time and the odd data type choices in this DMV (this is not the only questionable data type choice in this DMV's output):

#664557 : sys.dm_server_services.last_startup_time is always NULL for SQL Agent

#664559 : Odd data type choices in sys.dm_server_services

* Not yet in Denali.

sys.dm_exec_query_stats

I talked about this DMV in my previous post; it is not a new DMV, but it gets some shiny new columns. Essentially, there are four new columns showing row count statistics for each query exposed by the view: total_rows, last_rows, min_rows and max_rows. This enhances the information you have about each query if you are want to know if it has returned a vastly different number of rows over time.

* Not yet in Denali.

sys.dm_os_sys_info

This DMV is also not new, but it has a couple of new columns dedicated to virtualization, so you can tell programmatically, for example, if the SQL Server instance is on a virtualized or physical host. The new columns are virtual_machine_type and virtual_machine_type_desc. I don't have a full mapping of the possible values yet; since I only have 2008 R2 running on virtual machines, I can only show you the values in that situation:
SELECT 
   virtual_machine_type,
   virtual_machine_type_desc
FROM
   sys.dm_os_sys_info;
Results:

Note that the machines where I ran these queries are running on Windows 7 virtual machines under VMWare Fusion, so don't always believe that "HYPERVISOR" means Hyper-V explicitly.

Also note: the current Denali BOL documentation for "Breaking Changes to Database Engine Features" incorrectly states that these columns were removed.

* This DMV is the only one on this page that has already been added/changed in current builds of Denali (CTP1).

Extended Events

Some new xevents were added in SP1 as well. I'm sure Jonathan Kehayias (blog | twitter) will talk more about them soon enough, but I can at least identify them - by joining a couple of XE views on the local, SP1 server to a linked server running 2008 R2 RTM:
SELECT
   l.name,
   l.map_value
FROM
   sys.dm_xe_map_values AS l
LEFT OUTER JOIN
   [GREENLANTERN\SQL2008R2RTM].[master].sys.dm_xe_map_values AS r
   ON l.name = r.name
   AND l.map_key = r.map_key
WHERE
   r.name IS NULL;

SELECT
   l.name,
   l.description
FROM
   sys.dm_xe_objects AS l
LEFT OUTER JOIN
   [GREENLANTERN\SQL2008R2RTM].[master].sys.dm_xe_objects AS r
   ON l.name = r.name
WHERE
   r.name IS NULL;
Results:

* These are partially in Denali CTP1. Well, the first two rows in the first resultset, at least.

Reference queries

For reference, you can see the queries I used here to perform this Sherlock Holmes-esque work against the DMVs:

I talked about how to find new DMVs and other system objects in November 2010.
I talked about how to find new and changed DMV columns in August 2009.

Download

SP1 CTP for "normal" SKUs (and also a stand-alone download for upgrading client tools only):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df

SP1 CTP for the various Express Editions (Express, Express with Tools, Express with Advanced Services):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3

SP1 CTP Feature Packs (please don't ask me what all of these files are, but someone else did a pretty good job):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409

Summary

I'm still torn about features being added in service packs, but if you're on this platform, clearly there are some changes that you might be able to take advantage of once the Service Pack 1 is officially RTM. And I have every reason to believe that these changes will be ported to Denali, probably by the time you see the next CTP.

Please remember, though, that this service pack is not going to include fixes from cumulative update #7 for SQL Server 2008 R2, so if you're relying on those fixes, don't install this service pack - it will undo those changes. Also don't install the service pack CTP if you think you might be interested in cumulative update #8, since it will be problematic to downgrade to that CU once the service pack is installed (even the CTP). And who knows when the service pack will be officially released (never mind the subsequent CU that will get it "caught up" with the current branch of RTM fixes).

Also, please remember that DMV data does not persist through a service restart. So, while some of these queries will yield relatively static results (such as those that talk about the operating system or that dig information out of the registry), things like the rowcount values added to sys.dm_exec_query_stats are only valid since the time the service was started.

And finally, please don't try, and please don't ask; you cannot install this service pack on SQL Server 2008. This is ONLY for the SQL Server 2008 R2 release.

SQL Server v.Next (Denali) : Changes to performance counters

AaronBertrand — Sat, 08 Jan 2011 17:44:00 GMT

In a previous post about changed system objects in Denali, I talked about the changes to memory-related DMVs due to underlying changes in the memory manager. The SQLOS team has posted a great introduction to these changes, and they plan to post more details in future posts. In the meantime, and due to a question yesterday from Tom LaRock (blog | twitter) ...

... I thought I would tell you about some performance counters that have changed between SQL Server 2008 R2 and Denali - most of which involve these memory manager changes. In response to Tom, initially I just performed a count and compared, but this isn't very accurate; since many counters are database-specific, the counts depend greatly on how many databases you have installed (as well as edition and features - more on that below). So instead I wanted to query and see the actual differences ... on a 2008 R2 instance, I created a linked server pointing to a Denali instance, then I ran this query:

;WITH loc AS
(
   SELECT obj, cn
   FROM 
   (
       SELECT
           obj = SUBSTRING([object_name], CHARINDEX(':', [object_name]) + 1, 256), 
           cn = counter_name
       FROM sys.dm_os_performance_counters
   ) AS x
   GROUP BY obj, cn
),
rem AS 
(
   SELECT obj, cn
   FROM 
   (
       SELECT
           obj = SUBSTRING([object_name], CHARINDEX(':', [object_name]) + 1, 256), 
           cn = counter_name
       FROM [Denali_Linked_Server].[master].sys.dm_os_performance_counters
   ) AS x
   GROUP BY obj, cn
)
SELECT 
   [2008R2_obj] = loc.obj, 
   [2008R2_ctr] = loc.cn,
   [Denali_obj] = rem.obj,
   [Denali_ctr] = rem.cn
FROM
   loc 
FULL OUTER JOIN
   rem 
   ON loc.obj = rem.obj
   AND loc.cn = rem.cn
WHERE 
   loc.obj    IS NULL
   OR rem.obj IS NULL
ORDER BY
   [Denali_obj],
   [Denali_ctr],
   [2008R2_obj], 
   [2008R2_ctr];

The results:

2008 R2 Object 2008 R2 Counter Denali Object Denali Counter

Buffer Manager AWE lookup maps/sec <removed in Denali>

Buffer Manager AWE stolen maps/sec <removed in Denali>

Buffer Manager AWE unmap calls/sec <removed in Denali>

Buffer Manager AWE unmap pages/sec <removed in Denali>

Buffer Manager AWE write maps/sec <removed in Denali>

Buffer Manager Free pages <removed in Denali>

Buffer Manager Reserved pages <removed in Denali>

Buffer Manager Stolen pages <removed in Denali>

Buffer Manager Target pages <removed in Denali>

Buffer Manager Total pages <removed in Denali>

Buffer Node Foreign pages <removed in Denali>

Buffer Node Free pages <removed in Denali>

Buffer Node Stolen pages <removed in Denali>

Buffer Node Target pages <removed in Denali>

Buffer Node Total pages <removed in Denali>

Buffer Partition Free list empty/sec <removed in Denali>

Buffer Partition Free list requests/sec <removed in Denali>

Buffer Partition Free pages <removed in Denali>

<new in Denali> Access Methods InSysXact waits/sec

<new in Denali> Availability Replica Bytes Received from Replica/sec

<new in Denali> Availability Replica Bytes Sent to Replica/sec

<new in Denali> Availability Replica Bytes Sent to Transport/sec

<new in Denali> Availability Replica Flow Control Time (ms/sec)

<new in Denali> Availability Replica Flow Control/sec

<new in Denali> Availability Replica Receives from Replica/sec

<new in Denali> Availability Replica Resent Messages/sec

<new in Denali> Availability Replica Sends to Replica/sec

<new in Denali> Availability Replica Sends to Transport/sec

<new in Denali> Database Replica File Bytes Received/sec

<new in Denali> Database Replica Log Bytes Received/sec

<new in Denali> Database Replica Log Send Queue

<new in Denali> Database Replica Recovery Queue

<new in Denali> Database Replica Redone Bytes/sec

<new in Denali> Databases Log Flush Write Time (ms)

<new in Denali> Memory Manager Database Cache Memory (KB)

<new in Denali> Memory Manager Free Memory (KB)

<new in Denali> Memory Manager Log Pool Memory (KB)

<new in Denali> Memory Manager Reserved Server Memory (KB)

<new in Denali> Memory Manager Stolen Server Memory (KB)

<new in Denali> Memory Node Database Node Memory (KB)

<new in Denali> Memory Node Foreign Node Memory (KB)

<new in Denali> Memory Node Free Node Memory (KB)

<new in Denali> Memory Node Stolen Node Memory (KB)

<new in Denali> Memory Node Target Node Memory (KB)

<new in Denali> Memory Node Total Node Memory (KB)

2008 R2 Object	2008 R2 Counter	Denali Object	Denali Counter
Buffer Manager	AWE lookup maps/sec	<removed in Denali>
Buffer Manager	AWE stolen maps/sec	<removed in Denali>
Buffer Manager	AWE unmap calls/sec	<removed in Denali>
Buffer Manager	AWE unmap pages/sec	<removed in Denali>
Buffer Manager	AWE write maps/sec	<removed in Denali>
Buffer Manager	Free pages	<removed in Denali>
Buffer Manager	Reserved pages	<removed in Denali>
Buffer Manager	Stolen pages	<removed in Denali>
Buffer Manager	Target pages	<removed in Denali>
Buffer Manager	Total pages	<removed in Denali>
Buffer Node	Foreign pages	<removed in Denali>
Buffer Node	Free pages	<removed in Denali>
Buffer Node	Stolen pages	<removed in Denali>
Buffer Node	Target pages	<removed in Denali>
Buffer Node	Total pages	<removed in Denali>
Buffer Partition	Free list empty/sec	<removed in Denali>
Buffer Partition	Free list requests/sec	<removed in Denali>
Buffer Partition	Free pages	<removed in Denali>
<new in Denali>	Access Methods	InSysXact waits/sec
<new in Denali>	Availability Replica	Bytes Received from Replica/sec
<new in Denali>	Availability Replica	Bytes Sent to Replica/sec
<new in Denali>	Availability Replica	Bytes Sent to Transport/sec
<new in Denali>	Availability Replica	Flow Control Time (ms/sec)
<new in Denali>	Availability Replica	Flow Control/sec
<new in Denali>	Availability Replica	Receives from Replica/sec
<new in Denali>	Availability Replica	Resent Messages/sec
<new in Denali>	Availability Replica	Sends to Replica/sec
<new in Denali>	Availability Replica	Sends to Transport/sec
<new in Denali>	Database Replica	File Bytes Received/sec
<new in Denali>	Database Replica	Log Bytes Received/sec
<new in Denali>	Database Replica	Log Send Queue
<new in Denali>	Database Replica	Recovery Queue
<new in Denali>	Database Replica	Redone Bytes/sec
<new in Denali>	Databases	Log Flush Write Time (ms)
<new in Denali>	Memory Manager	Database Cache Memory (KB)
<new in Denali>	Memory Manager	Free Memory (KB)
<new in Denali>	Memory Manager	Log Pool Memory (KB)
<new in Denali>	Memory Manager	Reserved Server Memory (KB)
<new in Denali>	Memory Manager	Stolen Server Memory (KB)
<new in Denali>	Memory Node	Database Node Memory (KB)
<new in Denali>	Memory Node	Foreign Node Memory (KB)
<new in Denali>	Memory Node	Free Node Memory (KB)
<new in Denali>	Memory Node	Stolen Node Memory (KB)
<new in Denali>	Memory Node	Target Node Memory (KB)
<new in Denali>	Memory Node	Total Node Memory (KB)

You can see that several AWE-related counters have been removed, since AWE is no longer supported in Denali (this means if you are on 32-bit hardware and have > 4GB of RAM, you may as well start shopping for new hardware if you plan to be an early adopter). You'll also notice several Buffer Manager/Node/Partition objects are gone in Denali, replaced by Memory Manager/Node objects (and now measured in KB as opposed to pages). Some of these are described here. There are also some new replica counters based on the new availability and HA/DR features in Denali (described here and here). The new Log Flush Write Time (ms) counter is described in this BOL topic: SQL Server, Databases Object. I can't find any information about the InSysXact waits/sec counter; hopefully the documentation will catch up by RTM (or, ideally, by the next CTP).

So, if you're currently monitoring any of these performance counters in 2008 R2 or previous versions, take note that not only are they changing in Denali, but also that their values can mean something different.

Now keep in mind that this does not address changes to performance counters that are only present when certain features are enabled, such as database mirroring and replication. I will try to re-run these tests when I have a suitable environment with as many features enabled as possible.

SQL Server v.Next (Denali) : How has sys.databases changed?

AaronBertrand — Tue, 30 Nov 2010 19:37:00 GMT

Earlier, I documented the system objects that have changed in Denali. One that caught my eye was a slew of new columns made available through sys.databases, mostly to support a feature I also blogged about recently, contained databases. Consider the following simple query:

SELECT * FROM sys.databases;

Yes, this comes from a DMV, but where does the DMV really get its data? First I wanted to view the execution plans in 2008 R2 and Denali to compare them: were there any significant differences? Opening them in Management Studio proved to be relatively useless... to see everything on the screen, I can tell that they're a bit different, but zooming in and scrolling all over the place is for the birds:

2008 R2 graphical plan in SSMS

Denali graphical plan in SSMS

So I opened my trusty SQL Sentry Plan Explorer (which I've blogged about before) and got a much better quick view of these plans, without having to perform any zooming whatsoever:

2008 R2 graphical plan in Plan Explorer

Denali graphical plan in Plan Explorer

So this looks interesting so far; the execution plan for Denali is much busier, and has some references to internal objects I haven't noticed before: table-valued functions SYSLANG and SYSFULLTEXTLANGUAGES, and a system table called sysguidrefs. You can see the actual definition of the view pretty simply, by issuing:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.databases'));

In SQL Server 2008 R2, you get this (about 4,200 characters):

CREATE VIEW sys.databases AS
   SELECT d.name, d.id AS database_id,
       r.indepid AS source_database_id,
       d.sid AS owner_sid,
       d.crdate AS create_date,
       d.cmptlevel AS compatibility_level,
       CONVERT(SYSNAME, CollationPropertyFromID(p.cid, 'name')) AS collation_name,
       p.user_access, ua.name AS user_access_desc,
       sysconv(bit, d.status & 0x400) AS is_read_only,          -- DBR_RDONLY
       sysconv(bit, d.status & 1) AS is_auto_close_on,          -- DBR_CLOSE_ON_EXIT
       sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on,      -- DBR_AUTOSHRINK
       p.state, st.name AS state_desc,
       sysconv(bit, d.status & 0x200000) AS is_in_standby,      -- DBR_STANDBY
       sysconv(bit, d.status & 0x40000000) AS is_cleanly_shutdown,  -- DBR_CLEANLY_SHUTDOWN
       sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled,  -- DBR_SUPPLEMENT_LOG
       p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
       sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on,      -- DBR_READCOMMITTED_SNAPSHOT
       p.recovery_model, ro.name AS recovery_model_desc,
       p.page_verify_option, pv.name AS page_verify_option_desc,
       sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on,          -- DBR_AUTOCRTSTATS
       sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on,     -- DBR_AUTOUPDSTATS
       sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on,   -- DBR_AUTOUPDSTATSASYNC
       sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on,         -- DBR_ANSINULLDFLT
       sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on,             -- DBR_ANSINULLS
       sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on,                  -- DBR_ANSIPADDING
       sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on,         -- DBR_ANSIWARNINGS
       sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on,                    -- DBR_ARITHABORT
       sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on,      -- DBR_CATNULL
       sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on,         -- DBR_NUMEABORT
       sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on,           -- DBR_QUOTEDIDENT
       sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on,           -- DBR_RECURTRIG
       sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
       sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default,           -- DBR_DEFLOCALCURS
       sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled,         -- DBR_FTENABLED
       sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on,                -- DBR_TRUSTWORTHY
       sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on,                -- DBR_DBCHAINING
       sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced,  -- DBR_UNIVERSALAUTOPARAM
       sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server,   -- DBR_MASTKEY
       sysconv(bit, d.category & 1) AS is_published,
       sysconv(bit, d.category & 2) AS is_subscribed,
       sysconv(bit, d.category & 4) AS is_merge_published,
       sysconv(bit, d.category & 16) AS is_distributor,
       sysconv(bit, d.category & 32) AS is_sync_with_backup,
       d.svcbrkrguid AS service_broker_guid,
       sysconv(bit, CASE WHEN d.scope = 0 THEN 1 ELSE 0 END) AS is_broker_enabled,
       p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
       sysconv(bit, d.status2 & 4) AS is_date_correlation_on,       -- DBR_DATECORRELATIONOPT
       sysconv(bit, d.category & 64) AS is_cdc_enabled,
       sysconv(bit, d.status2 & 0x100) AS is_encrypted,                     -- DBR_ENCRYPTION
       sysconv(bit, d.status2 & 0x8) AS is_honor_broker_priority_on             -- DBR_HONORBRKPRI
   FROM master.sys.sysdbreg d OUTER APPLY OPENROWSET(TABLE DBPROP, d.id) p
   LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
   LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
   LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
   LEFT JOIN sys.syspalvalues si  ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
   LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
   LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
   LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
   WHERE d.id < 0x7fff
       AND has_access('DB', d.id) = 1

And in Denali, it's much more verbose - almost 50% larger (about 6,200 characters):

CREATE VIEW sys.databases AS
   SELECT d.name, d.id AS database_id,
       r.indepid AS source_database_id,
       d.sid AS owner_sid,
       d.crdate AS create_date,
       d.cmptlevel AS compatibility_level,
       CONVERT(SYSNAME, CollationPropertyFromID(p.cid, 'name')) AS collation_name,
       p.user_access, ua.name AS user_access_desc,
       sysconv(bit, d.status & 0x400) AS is_read_only,          -- DBR_RDONLY
       sysconv(bit, d.status & 1) AS is_auto_close_on,          -- DBR_CLOSE_ON_EXIT
       sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on,      -- DBR_AUTOSHRINK
       p.state, st.name AS state_desc,
       sysconv(bit, d.status & 0x200000) AS is_in_standby,      -- DBR_STANDBY
       p.is_cleanly_shutdown,
       sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled,  -- DBR_SUPPLEMENT_LOG
       p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
       sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on,      -- DBR_READCOMMITTED_SNAPSHOT
       p.recovery_model, ro.name AS recovery_model_desc,
       p.page_verify_option, pv.name AS page_verify_option_desc,
       sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on,          -- DBR_AUTOCRTSTATS
       sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on,     -- DBR_AUTOUPDSTATS
       sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on,   -- DBR_AUTOUPDSTATSASYNC
       sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on,         -- DBR_ANSINULLDFLT
       sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on,             -- DBR_ANSINULLS
       sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on,                  -- DBR_ANSIPADDING
       sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on,         -- DBR_ANSIWARNINGS
       sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on,                    -- DBR_ARITHABORT
       sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on,      -- DBR_CATNULL
       sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on,         -- DBR_NUMEABORT
       sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on,           -- DBR_QUOTEDIDENT
       sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on,           -- DBR_RECURTRIG
       sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
       sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default,           -- DBR_DEFLOCALCURS
       sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled,         -- DBR_FTENABLED
       sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on,                -- DBR_TRUSTWORTHY
       sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on,                -- DBR_DBCHAINING
       sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced,  -- DBR_UNIVERSALAUTOPARAM
       sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server,   -- DBR_MASTKEY
       sysconv(bit, d.category & 1) AS is_published,
       sysconv(bit, d.category & 2) AS is_subscribed,
       sysconv(bit, d.category & 4) AS is_merge_published,
       sysconv(bit, d.category & 16) AS is_distributor,
       sysconv(bit, d.category & 32) AS is_sync_with_backup,
       d.svcbrkrguid AS service_broker_guid,
       sysconv(bit, CASE WHEN d.scope = 0 THEN 1 ELSE 0 END) AS is_broker_enabled,
       p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
       sysconv(bit, d.status2 & 4) AS is_date_correlation_on,       -- DBR_DATECORRELATIONOPT
       sysconv(bit, d.category & 64) AS is_cdc_enabled,
       sysconv(bit, d.status2 & 0x100) AS is_encrypted,                     -- DBR_ENCRYPTION
       CONVERT(bit, d.status2 & 0x8) AS is_honor_broker_priority_on,                -- DBR_HONORBRKPRI
       sgr.guid AS replica_id,
       sgr2.guid AS group_database_id,
       default_language_lcid = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(smallint, p.default_language) ELSE NULL END,
       default_language_name = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(SYSNAME, sld.name) ELSE NULL END,
       default_fulltext_language_lcid = CASE WHEN ((d.status2 & 0x80000)=0x800000 AND p.containment = 1) THEN CONVERT(INT, p.default_fulltext_language) ELSE NULL END,
       default_fulltext_language_name = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(SYSNAME, slft.name) ELSE NULL END,
       is_nested_triggers_on = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(bit, p.allow_nested_triggers) ELSE NULL END,
       is_transform_noise_words_on = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(bit, p.transform_noise_words) ELSE NULL END,
       two_digit_year_cutoff = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(smallint, p.two_digit_year_cutoff) ELSE NULL END,
       containment = sysconv(tinyint, (d.status2 & 0x80000)/0x80000), -- DBR_IS_CDB
       containment_desc = CONVERT(NVARCHAR(60), cdb.name)
   FROM master.sys.sysdbreg d OUTER APPLY OPENROWSET(TABLE DBPROP, d.id) p
   LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
   LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
   LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
   LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
   LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
   LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
   LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
   LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0    -- SRC_AVAILABILITYGROUP 
   LEFT JOIN master.sys.sysclsobjs  ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP
   LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID
   LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID
   LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 & 0x80000)=0x80000 THEN 1 ELSE 0 END
   LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language
   LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language
   WHERE d.id < 0x7fff
       AND has_access('DB', d.id) = 1

But of course you can't dig much deeper than this. If you try and see into objects like sys.singleobjectrefs and sys.sysdbreg, you get the SQL Server equivalent to a 404 Not Found error. This is even true if you use an old hack to view the system resource db directly (which I've described before).

So there isn't all that much value in this after all, unless you're an avid explorer. For me, I like to dig in as far as I can to see how things work, but I get pretty aggravated when I see syntax that works in system objects, but that we can't use ourselves (such as "hidden" OPENROWSET() calls) or things that seem custom for no obvious reason, such as the sysconv() function - why don't they just use CONVERT()?

More importantly, I have a guilty pleasure in seeing bad practices in practice (or at least inconsistency). Notice in the above code that the new columns for database containment properties have been added using "alias = expression" syntax, whereas the existing code had exclusively used "expression as alias" notation. They also should be using statement terminators, if for no other reason than to demonstrate that they really mean it when they say they will be required in some future version of SQL Server (see this rant from last year).

There are other things that irk me too - lack of AS for table/view aliases, use of LEFT JOIN instead of LEFT OUTER JOIN, inconsistent spacing, sometimes putting more than one output column on the same line, sloppy indenting and alignment... it surprises me when my own code quality and consistency standards would reject just about every module that ships in SQL Server. It might take 5 extra minutes to polish this view up, and if I were responsible for it, I would simply squeeze it into my deadline. Of course I don't have much insight into the Microsoft development cycle, but I do know that a lot of - let's say - "less than stellar" code comes out of those offices. The stuff above is pretty tame in comparison.

Probably the most important thing *YOU* should be aware of: there are extra columns here, so if you are currently building #temp tables or auditing scripts with the previous schema of sys.databases in mind, you'll need to be prepared for this change so that your existing scripts don't break.

SQL Server v.Next (Denali) : New, Changed and Removed System Objects

AaronBertrand — Mon, 29 Nov 2010 14:10:00 GMT

While it's still only the CTP1 timeframe, getting a glimpse of the changes in system objects can start preparing you for what's coming - especially when there are potential breaking changes, as you'll see below. Sure, a few of these things can change (and hopefully be fixed!) as the product gets closer to release, but once you have the scripts on disk, you can take a pulse at any time. Obviously, queries that go after the system objects directly are going to be much less tedious and annoying than trying to parse Books Online for changes (even if you assume that Books Online is up to date - which, as you'll see below, is not the case).

Before starting, I'll assume that you have a 2008 R2 instance and a Denali instance; these instances should have valid linked servers pointing at each other. In the code below you will see references to fairly intuitive linked server names: [GREENLANTERN\SQL2008R2] and [GREENLANTERN\DENALI]. I will try to remember to make it clear which instance the code should run from (in a few cases it matters, because functions like OBJECT_SCHEMA_NAME() don't work remotely without using <linked server>...sp_executeSQL).

New system objects in SQL Server "Denali"

This is a query run from the Denali server that will find objects that did not exist in 2008 R2. I had to code in an exclusion in the where clause; while sp_MS_marksystemobject certainly exists in 2008 R2, it does not appear in sys.all_objects the way it does in Denali.

SELECT
    [name] = N'sys.' + o.name,
    [type] = o.type_desc
FROM
    [master].sys.all_objects AS o
LEFT OUTER JOIN
    [GREENLANTERN\SQL2008R2].[master].sys.all_objects AS oo
ON
    o.name = oo.name
    AND o.[schema_id] = oo.[schema_id]
WHERE
    oo.name IS NULL
    AND o.[schema_id] = 4
    AND o.name <> N'sp_MS_marksystemobject'
ORDER BY
    o.type_desc,
    o.name;

Some of these objects are documented, but several still are not listed in the current version of Denali BOL. I was going to remove the links for the items that aren't documented, but I'll leave all of the search links intact so that they will continue to work when they *are* added to Books Online.

name type

sys.sp_availability_group_command_internal EXTENDED_STORED_PROCEDURE

sys.sp_describe_first_result_set EXTENDED_STORED_PROCEDURE

sys.sp_describe_undeclared_parameters EXTENDED_STORED_PROCEDURE

sys.sp_migrate_user_to_contained EXTENDED_STORED_PROCEDURE

sys.sp_server_diagnostics EXTENDED_STORED_PROCEDURE

sys.dm_db_objects_disabled_on_compatibility_level_change SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_exec_describe_first_result_set SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_exec_describe_first_result_set_for_object SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_fts_index_keywords_by_property SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logconsumer_cachebufferrefs SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logconsumer_privatecachebuffers SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpool_consumers SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpool_sharedcachebuffers SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpoolmgr_freepools SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpoolmgr_respoolsize SQL_INLINE_TABLE_VALUED_FUNCTION

sys.dm_logpoolmgr_stats SQL_INLINE_TABLE_VALUED_FUNCTION

sys.sp_filestream_force_garbage_collection SQL_STORED_PROCEDURE

sys.sp_filestream_recalculate_container_size SQL_STORED_PROCEDURE

sys.sp_help_spatial_geography_histogram SQL_STORED_PROCEDURE

sys.sp_help_spatial_geometry_histogram SQL_STORED_PROCEDURE

sys.sp_sequence_get_range SQL_STORED_PROCEDURE

sys.sysclones SYSTEM_TABLE

sys.sysdbfiles SYSTEM_TABLE

sys.sysftproperties SYSTEM_TABLE

sys.sysmatrixages SYSTEM_TABLE

sys.sysmatrixbricks SYSTEM_TABLE

sys.sysmatrixconfig SYSTEM_TABLE

sys.sysmatrixmanagers SYSTEM_TABLE

sys.sysxmitbody SYSTEM_TABLE

sys.availability_groups VIEW

sys.availability_replicas VIEW

sys.dm_db_uncontained_entities VIEW

sys.dm_hadr_availability_group_states VIEW

sys.dm_hadr_availability_replica_states VIEW

sys.dm_hadr_database_replica_states VIEW

sys.dm_hadr_database_synchronization_states VIEW

sys.dm_hadr_instance_node_map VIEW

sys.dm_hadr_name_id_map VIEW

sys.dm_logpool_hashentries VIEW

sys.dm_logpool_stats VIEW

sys.registered_search_properties VIEW

sys.registered_search_property_lists VIEW

sys.sequences VIEW

name	type
sys.sp_availability_group_command_internal	EXTENDED_STORED_PROCEDURE
sys.sp_describe_first_result_set	EXTENDED_STORED_PROCEDURE
sys.sp_describe_undeclared_parameters	EXTENDED_STORED_PROCEDURE
sys.sp_migrate_user_to_contained	EXTENDED_STORED_PROCEDURE
sys.sp_server_diagnostics	EXTENDED_STORED_PROCEDURE
sys.dm_db_objects_disabled_on_compatibility_level_change	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_exec_describe_first_result_set	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_exec_describe_first_result_set_for_object	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_fts_index_keywords_by_property	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logconsumer_cachebufferrefs	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logconsumer_privatecachebuffers	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_consumers	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_sharedcachebuffers	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_freepools	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_respoolsize	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_stats	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.sp_filestream_force_garbage_collection	SQL_STORED_PROCEDURE
sys.sp_filestream_recalculate_container_size	SQL_STORED_PROCEDURE
sys.sp_help_spatial_geography_histogram	SQL_STORED_PROCEDURE
sys.sp_help_spatial_geometry_histogram	SQL_STORED_PROCEDURE
sys.sp_sequence_get_range	SQL_STORED_PROCEDURE
sys.sysclones	SYSTEM_TABLE
sys.sysdbfiles	SYSTEM_TABLE
sys.sysftproperties	SYSTEM_TABLE
sys.sysmatrixages	SYSTEM_TABLE
sys.sysmatrixbricks	SYSTEM_TABLE
sys.sysmatrixconfig	SYSTEM_TABLE
sys.sysmatrixmanagers	SYSTEM_TABLE
sys.sysxmitbody	SYSTEM_TABLE
sys.availability_groups	VIEW
sys.availability_replicas	VIEW
sys.dm_db_uncontained_entities	VIEW
sys.dm_hadr_availability_group_states	VIEW
sys.dm_hadr_availability_replica_states	VIEW
sys.dm_hadr_database_replica_states	VIEW
sys.dm_hadr_database_synchronization_states	VIEW
sys.dm_hadr_instance_node_map	VIEW
sys.dm_hadr_name_id_map	VIEW
sys.dm_logpool_hashentries	VIEW
sys.dm_logpool_stats	VIEW
sys.registered_search_properties	VIEW
sys.registered_search_property_lists	VIEW
sys.sequences	VIEW

System objects dropped in SQL Server "Denali"

We can run a query similar to the above from the 2008 R2 instance, to see what kind of things will no longer work in Denali.

SELECT
    [name] = N'sys.' + o.name,
    [type] = o.type_desc
FROM
    [master].sys.all_objects AS o
LEFT OUTER JOIN
    [GREENLANTERN\DENALI].[master].sys.all_objects AS oo
ON
    o.name = oo.name
    AND o.[schema_id] = oo.[schema_id]
WHERE
    oo.name IS NULL
    AND o.[schema_id] = 4
ORDER BY
    o.type_desc,
    o.name;

The results: these are mostly XPs that have been marked for deprecation several versions ago (and actually are still marked as "future deprecated" in the current Denali Books Online - see Connect #625200) However I cannot yet explain the service broker DMV that is missing; I filed a bug about this in Connect #625206.

name type

sys.sp_batch_params EXTENDED_STORED_PROCEDURE

sys.sp_fetchLOBfromcookie EXTENDED_STORED_PROCEDURE

sys.xp_adsirequest EXTENDED_STORED_PROCEDURE

sys.xp_deletemail EXTENDED_STORED_PROCEDURE

sys.xp_findnextmsg EXTENDED_STORED_PROCEDURE

sys.xp_get_mapi_default_profile EXTENDED_STORED_PROCEDURE

sys.xp_get_mapi_profiles EXTENDED_STORED_PROCEDURE

sys.xp_MSADEnabled EXTENDED_STORED_PROCEDURE

sys.xp_MSADSIObjReg EXTENDED_STORED_PROCEDURE

sys.xp_MSADSIObjRegDB EXTENDED_STORED_PROCEDURE

sys.xp_MSADSIReg EXTENDED_STORED_PROCEDURE

sys.xp_readmail EXTENDED_STORED_PROCEDURE

sys.xp_sendmail EXTENDED_STORED_PROCEDURE

sys.xp_startmail EXTENDED_STORED_PROCEDURE

sys.xp_stopmail EXTENDED_STORED_PROCEDURE

sys.xp_test_mapi_profile EXTENDED_STORED_PROCEDURE

sys.sp_ActiveDirectory_Obj SQL_STORED_PROCEDURE

sys.sp_ActiveDirectory_SCP SQL_STORED_PROCEDURE

sys.sp_ActiveDirectory_Start SQL_STORED_PROCEDURE

sys.sp_dropalias SQL_STORED_PROCEDURE

sys.sp_MScheckIsPubOfSub SQL_STORED_PROCEDURE

sys.sp_processmail SQL_STORED_PROCEDURE

sys.database_principal_aliases VIEW

sys.dm_broker_forwarded_messages VIEW

name	type
sys.sp_batch_params	EXTENDED_STORED_PROCEDURE
sys.sp_fetchLOBfromcookie	EXTENDED_STORED_PROCEDURE
sys.xp_adsirequest	EXTENDED_STORED_PROCEDURE
sys.xp_deletemail	EXTENDED_STORED_PROCEDURE
sys.xp_findnextmsg	EXTENDED_STORED_PROCEDURE
sys.xp_get_mapi_default_profile	EXTENDED_STORED_PROCEDURE
sys.xp_get_mapi_profiles	EXTENDED_STORED_PROCEDURE
sys.xp_MSADEnabled	EXTENDED_STORED_PROCEDURE
sys.xp_MSADSIObjReg	EXTENDED_STORED_PROCEDURE
sys.xp_MSADSIObjRegDB	EXTENDED_STORED_PROCEDURE
sys.xp_MSADSIReg	EXTENDED_STORED_PROCEDURE
sys.xp_readmail	EXTENDED_STORED_PROCEDURE
sys.xp_sendmail	EXTENDED_STORED_PROCEDURE
sys.xp_startmail	EXTENDED_STORED_PROCEDURE
sys.xp_stopmail	EXTENDED_STORED_PROCEDURE
sys.xp_test_mapi_profile	EXTENDED_STORED_PROCEDURE
sys.sp_ActiveDirectory_Obj	SQL_STORED_PROCEDURE
sys.sp_ActiveDirectory_SCP	SQL_STORED_PROCEDURE
sys.sp_ActiveDirectory_Start	SQL_STORED_PROCEDURE
sys.sp_dropalias	SQL_STORED_PROCEDURE
sys.sp_MScheckIsPubOfSub	SQL_STORED_PROCEDURE
sys.sp_processmail	SQL_STORED_PROCEDURE
sys.database_principal_aliases	VIEW
sys.dm_broker_forwarded_messages	VIEW

DMV / Catalog View columns added or changed in SQL Server "Denali"

This query, run on the Denali instance, will show new or changed columns compared to SQL Server 2008 R2:

;WITH r AS
(
    SELECT
        c.[object_id],
        [schema]      = OBJECT_SCHEMA_NAME(c.[object_id]),
        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
        t.name <> N'sysname'
)
SELECT
    [status] = CASE
        WHEN l.viewname IS NULL THEN 'new'
        ELSE 'changed'
    END,
    r.[schema],
    r.viewname,
    r.col,
    r.[type],
    FormerType = l.[type],
    r.[precision],
    FormerPrecision = l.[precision],
    r.scale,
    FormerScale = l.scale,
    max_length = r.max_length / CASE 
        WHEN r.[type] = N'nvarchar' THEN 2 ELSE 1 END,
    FormerMaxLength = l.max_length / CASE 
        WHEN l.[type] = N'nvarchar' THEN 2 ELSE 1 END
FROM
    r
LEFT OUTER JOIN
(
    SELECT
        [schema] = s.name,
        viewname = v.name,
        col = c.name,
        t = c.system_type_id,
        [type] = t.name,
        c.[precision],
        c.scale,
        c.max_length
    FROM
        [GREENLANTERN\SQL2008R2].[master].sys.all_columns AS c
    INNER JOIN
        [GREENLANTERN\SQL2008R2].[master].sys.all_views AS v
        ON c.[object_id] = v.[object_id]
    INNER JOIN
        [GREENLANTERN\SQL2008R2].[master].sys.schemas AS s
        ON v.[schema_id] = s.[schema_id]
    INNER JOIN
        [GREENLANTERN\SQL2008R2].[master].sys.types AS t
        ON c.system_type_id = t.system_type_id
    WHERE
        t.name <> N'sysname'
) AS l
ON
    r.viewname = l.viewname
    AND r.[schema] = l.[schema]
    AND r.col = l.col
WHERE
    (
        l.col 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)
        )
    )
    AND EXISTS
    (
        SELECT 1
            FROM [GREENLANTERN\SQL2008R2].[master].sys.all_views
            WHERE name = r.viewname
    )
ORDER BY
   [status] DESC,
   r.viewname,
   r.col;

The results: many new property columns for sys.databases, several new (well, renamed; see below) memory usage indicators in a few key DMVs, and some new columns around contained databases. (Please note that not all of the columns should imply that those features, e.g. two-digit year cutoff, will be supported in the next major version of SQL Server.) Also, a handful of the big string columns in various DMVs have been widened from 256 or 2,048 characters to 3,072; I'm not sure if any of these caused bugs in earlier versions, or they are just being proactive.

column status view name column name new data type old data type

new sys.all_sql_modules is_contained bit -

new sys.data_spaces is_system bit -

new sys.database_principals authentication_type int -

new sys.database_principals authentication_type_desc nvarchar(60) -

new sys.database_principals default_language_lcid int -

new sys.database_principals default_language_name nvarchar(128) -

new sys.databases containment tinyint -

new sys.databases containment_desc nvarchar(60) -

new sys.databases default_fulltext_language_lcid int -

new sys.databases default_fulltext_language_name nvarchar(128) -

new sys.databases default_language_lcid smallint -

new sys.databases default_language_name nvarchar(128) -

new sys.databases group_database_id uniqueidentifier -

new sys.databases is_nested_triggers_on bit -

new sys.databases is_transform_noise_words_on bit -

new sys.databases replica_id uniqueidentifier -

new sys.databases two_digit_year_cutoff smallint -

new sys.dm_clr_appdomains compatibility_level int -

new sys.dm_database_encryption_keys encryptor_type nvarchar(128) -

new sys.dm_db_file_space_usage allocated_extent_page_count bigint -

new sys.dm_db_file_space_usage filegroup_id smallint -

new sys.dm_db_file_space_usage total_page_count bigint -

new sys.dm_exec_cached_plans global_token varbinary(14) -

new sys.dm_exec_sessions authenticating_database_id int -

new sys.dm_os_memory_cache_counters pages_in_use_kb bigint -

new sys.dm_os_memory_cache_counters pages_kb bigint -

new sys.dm_os_memory_cache_entries pages_kb bigint -

new sys.dm_os_memory_clerks page_size_in_bytes bigint -

new sys.dm_os_memory_clerks pages_kb bigint -

new sys.dm_os_memory_nodes foreign_committed_kb bigint -

new sys.dm_os_memory_nodes pages_kb bigint -

new sys.dm_os_memory_objects max_pages_in_bytes bigint -

new sys.dm_os_memory_objects pages_in_bytes bigint -

new sys.dm_os_sys_info committed_kb bigint -

new sys.dm_os_sys_info committed_target_kb bigint -

new sys.dm_os_sys_info physical_memory_kb bigint -

new sys.dm_os_sys_info virtual_machine_type int -

new sys.dm_os_sys_info virtual_machine_type_desc nvarchar(60) -

new sys.dm_os_sys_info virtual_memory_kb bigint -

new sys.dm_os_sys_info visible_target_kb bigint -

new sys.dm_os_worker_local_storage query_driver_address varbinary(8) -

new sys.filegroups is_system bit -

new sys.fulltext_indexes property_list_id int -

new sys.partition_functions is_system bit -

new sys.partition_schemes is_system bit -

new sys.server_event_session_events predicate_xml nvarchar(0) -

new sys.server_principals is_fixed_role bit -

new sys.server_principals owning_principal_id int -

new sys.server_trigger_events is_trigger_event bit -

new sys.sql_modules is_contained bit -

new sys.trigger_events is_trigger_event bit -

changed sys.dm_os_memory_cache_entries entry_data nvarchar(3072) nvarchar(2048)

changed sys.dm_os_ring_buffers record nvarchar(3072) nvarchar(2048)

changed sys.dm_os_waiting_tasks resource_description nvarchar(3072) nvarchar(2048)

changed sys.dm_xe_map_values map_value nvarchar(3072) nvarchar(2048)

changed sys.dm_xe_object_columns description nvarchar(3072) nvarchar(256)

changed sys.dm_xe_objects description nvarchar(3072) nvarchar(256)

changed sys.dm_xe_packages description nvarchar(3072) nvarchar(256)

changed sys.dm_xe_session_events event_predicate nvarchar(3072) nvarchar(2048)

changed sys.dm_xe_session_object_columns column_value nvarchar(3072) nvarchar(2048)

changed sys.syscacheobjects cacheobjtype nvarchar(50) nvarchar(17)

column status	view name	column name	new data type	old data type
new	sys.all_sql_modules	is_contained	bit	-
new	sys.data_spaces	is_system	bit	-
new	sys.database_principals	authentication_type	int	-
new	sys.database_principals	authentication_type_desc	nvarchar(60)	-
new	sys.database_principals	default_language_lcid	int	-
new	sys.database_principals	default_language_name	nvarchar(128)	-
new	sys.databases	containment	tinyint	-
new	sys.databases	containment_desc	nvarchar(60)	-
new	sys.databases	default_fulltext_language_lcid	int	-
new	sys.databases	default_fulltext_language_name	nvarchar(128)	-
new	sys.databases	default_language_lcid	smallint	-
new	sys.databases	default_language_name	nvarchar(128)	-
new	sys.databases	group_database_id	uniqueidentifier	-
new	sys.databases	is_nested_triggers_on	bit	-
new	sys.databases	is_transform_noise_words_on	bit	-
new	sys.databases	replica_id	uniqueidentifier	-
new	sys.databases	two_digit_year_cutoff	smallint	-
new	sys.dm_clr_appdomains	compatibility_level	int	-
new	sys.dm_database_encryption_keys	encryptor_type	nvarchar(128)	-
new	sys.dm_db_file_space_usage	allocated_extent_page_count	bigint	-
new	sys.dm_db_file_space_usage	filegroup_id	smallint	-
new	sys.dm_db_file_space_usage	total_page_count	bigint	-
new	sys.dm_exec_cached_plans	global_token	varbinary(14)	-
new	sys.dm_exec_sessions	authenticating_database_id	int	-
new	sys.dm_os_memory_cache_counters	pages_in_use_kb	bigint	-
new	sys.dm_os_memory_cache_counters	pages_kb	bigint	-
new	sys.dm_os_memory_cache_entries	pages_kb	bigint	-
new	sys.dm_os_memory_clerks	page_size_in_bytes	bigint	-
new	sys.dm_os_memory_clerks	pages_kb	bigint	-
new	sys.dm_os_memory_nodes	foreign_committed_kb	bigint	-
new	sys.dm_os_memory_nodes	pages_kb	bigint	-
new	sys.dm_os_memory_objects	max_pages_in_bytes	bigint	-
new	sys.dm_os_memory_objects	pages_in_bytes	bigint	-
new	sys.dm_os_sys_info	committed_kb	bigint	-
new	sys.dm_os_sys_info	committed_target_kb	bigint	-
new	sys.dm_os_sys_info	physical_memory_kb	bigint	-
new	sys.dm_os_sys_info	virtual_machine_type	int	-
new	sys.dm_os_sys_info	virtual_machine_type_desc	nvarchar(60)	-
new	sys.dm_os_sys_info	virtual_memory_kb	bigint	-
new	sys.dm_os_sys_info	visible_target_kb	bigint	-
new	sys.dm_os_worker_local_storage	query_driver_address	varbinary(8)	-
new	sys.filegroups	is_system	bit	-
new	sys.fulltext_indexes	property_list_id	int	-
new	sys.partition_functions	is_system	bit	-
new	sys.partition_schemes	is_system	bit	-
new	sys.server_event_session_events	predicate_xml	nvarchar(0)	-
new	sys.server_principals	is_fixed_role	bit	-
new	sys.server_principals	owning_principal_id	int	-
new	sys.server_trigger_events	is_trigger_event	bit	-
new	sys.sql_modules	is_contained	bit	-
new	sys.trigger_events	is_trigger_event	bit	-
changed	sys.dm_os_memory_cache_entries	entry_data	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_os_ring_buffers	record	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_os_waiting_tasks	resource_description	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_xe_map_values	map_value	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_xe_object_columns	description	nvarchar(3072)	nvarchar(256)
changed	sys.dm_xe_objects	description	nvarchar(3072)	nvarchar(256)
changed	sys.dm_xe_packages	description	nvarchar(3072)	nvarchar(256)
changed	sys.dm_xe_session_events	event_predicate	nvarchar(3072)	nvarchar(2048)
changed	sys.dm_xe_session_object_columns	column_value	nvarchar(3072)	nvarchar(2048)
changed	sys.syscacheobjects	cacheobjtype	nvarchar(50)	nvarchar(17)

DMV / Catalog View columns dropped from SQL Server "Denali"

And finally, we should check for columns that have been dropped from DMVs or catalog views, since cpu_ticks_in_ms was dropped from sys.dm_os_sys_info in SQL Server 2008 with little fanfare. On the 2008 R2 instance, you can run this query:

;WITH r AS
(
    SELECT
        c.[object_id],
        [schema] = OBJECT_SCHEMA_NAME(c.[object_id]),
        viewname = v.name,
        col = c.name
    FROM
        [master].sys.all_columns AS c
    INNER JOIN
        [master].sys.all_views AS v
        ON c.[object_id] = v.[object_id]
)
SELECT
    r.[schema],
    r.viewname,
    r.col
FROM
    r
LEFT OUTER JOIN
(
    SELECT
        [schema] = s.name,
        viewname = v.name,
        col = c.name
    FROM
        [GREENLANTERN\DENALI].[master].sys.all_columns AS c
    INNER JOIN
        [GREENLANTERN\DENALI].[master].sys.all_views AS v
        ON c.[object_id] = v.[object_id]
    INNER JOIN
        [GREENLANTERN\DENALI].[master].sys.schemas AS s
        ON v.[schema_id] = s.[schema_id]
) AS l
ON
    r.viewname = l.viewname
    AND r.[schema] = l.[schema]
    AND r.col = l.col
WHERE
    l.col IS NULL
    AND EXISTS
    (
        SELECT 1
            FROM [GREENLANTERN\DENALI].[master].sys.all_views
            WHERE name = r.viewname
    )
ORDER BY
   r.viewname,
   r.col;

The results: as it turns out, the only columns that have been "dropped" are the ones that have been renamed or consolidated (they are listed as new above).

view name column name

sys.dm_os_memory_cache_counters multi_pages_in_use_kb

sys.dm_os_memory_cache_counters multi_pages_kb

sys.dm_os_memory_cache_counters single_pages_in_use_kb

sys.dm_os_memory_cache_counters single_pages_kb

sys.dm_os_memory_cache_entries pages_allocated_count

sys.dm_os_memory_clerks multi_pages_kb

sys.dm_os_memory_clerks page_size_bytes

sys.dm_os_memory_clerks single_pages_kb

sys.dm_os_memory_nodes multi_pages_kb

sys.dm_os_memory_nodes single_pages_kb

sys.dm_os_memory_objects max_pages_allocated_count

sys.dm_os_memory_objects pages_allocated_count

sys.dm_os_sys_info bpool_commit_target

sys.dm_os_sys_info bpool_committed

sys.dm_os_sys_info bpool_visible

sys.dm_os_sys_info physical_memory_in_bytes

sys.dm_os_sys_info virtual_memory_in_bytes

view name	column name
sys.dm_os_memory_cache_counters	multi_pages_in_use_kb
sys.dm_os_memory_cache_counters	multi_pages_kb
sys.dm_os_memory_cache_counters	single_pages_in_use_kb
sys.dm_os_memory_cache_counters	single_pages_kb
sys.dm_os_memory_cache_entries	pages_allocated_count
sys.dm_os_memory_clerks	multi_pages_kb
sys.dm_os_memory_clerks	page_size_bytes
sys.dm_os_memory_clerks	single_pages_kb
sys.dm_os_memory_nodes	multi_pages_kb
sys.dm_os_memory_nodes	single_pages_kb
sys.dm_os_memory_objects	max_pages_allocated_count
sys.dm_os_memory_objects	pages_allocated_count
sys.dm_os_sys_info	bpool_commit_target
sys.dm_os_sys_info	bpool_committed
sys.dm_os_sys_info	bpool_visible
sys.dm_os_sys_info	physical_memory_in_bytes
sys.dm_os_sys_info	virtual_memory_in_bytes

If you're currently using any of these DMVs in your own code, this is potentially a major breaking change - you may want to make note of the columns that have changed, as upgrading to Denali may break some of your scripts.