All it takes to know that Denali (SQL Server Version 11) will be a major upgrade from SQL Server 2008 R2 (SQL Server Version 10.5) is to compare the number of Dynamic Management Views and Functions (DMVs) available.
select count(*) from master.sys.sysobjects where name like 'dm%'
SQL Server 2008 R2 SP1 reports 141 DMVs while Denali CTP3 counts 174! Let’s see….174-141=33! Here’s what’s new – and just for good measure to ensure I didn’t leave any out, I’ll number them:
Almost everywhere you look in the Denali Database Engine much of what is “new” revolves around Availability Groups and HADR (High Availability / Disaster Recovery) options. Thus, many of the new DMVs (10) provide insight into these features. These DMVs have the naming convention – dm_hadr_%. I’ll investigate these further when I get around to testing out AlwaysOn. But, in the meantime, you’ll find most but (as of this writing) not all of these documented in the Denali BOL DMV section (http://msdn.microsoft.com/en-us/library/ff877943(v=SQL.110).aspx) under the “AlwaysOn Availability Groups Dynamic Management Views and Functions”. You’ll also find that the new DMV, dm_tcp_listener_states, is also listed in this group in BOL, so I guess that makes 11 new DMVs in this category.
Another 10 new DMVs appear to deal with log information, but I can’t find any documentation on the ones using the dm_log% naming at present and most appear to be functions. Nor is information on dm_db_log_space_usage readily available. I suspect this deficiency will be remedied by RTM. However, by running a SELECT query on sys.dm_db_log_space_usage, you’ll find that it returns a single row for the current or specified database with the following columns – database_id, total_log_size_in_bytes, used_log_space_in_bytes, used_log_space_in_percent.
I like this one tremendously – it provides exactly what I expected based on the name. I’ve been working with SQL Server since version 4.21 and now – finally – I no longer have to parse DBCC results or other DMVs and do these log space calculations for myself! Yippee!
On the other hand, dm_db_objects_disabled_on_compatibility_level_change isn’t exactly what I expected based on the name. I finally found it documented under the new section of DMVs in BOL called “Spatial Related Dynamic Management Views and Functions”. Huh? I’ll save you the trouble of clicking the link…BOL states that this DMV: Lists the persisted computed columns and spatial indexes that will be disabled as a result of changing compatibility level.Ok – so I’m guessing by the name that perhaps this will be expanded on in the future, but right now it is a fairly limited set of objects to which it applies.
If that list of 33 isn’t enough for you, there are the following DMVs which could also be classified as new to Denali, but were also “retroactively” released with SP1 for SQL Server 2008 R2. So if you’ll be skipping SQL Server 2008 R2 SP1, then these will definitely be new, too, when you move to Denali.
My favorites from this list are dm_os_windows_info and dm_server_services. These two DMVs allow me to determine the Windows version my SQL Server instance is running on as well as information about the instance’s SQL Server services, such as start mode and service account name, without resorting to WMI or SQL-SMO queries. I can now get this information directly via a T-SQL query. The DMV dm_os_volume_stats is actually a function to which you’ll need to provide a database id and file id and it will return the size of the volume and remaining space on the volume containing that file, plus other details. I have to confess I was a little disappointed that this didn’t return a row for every volume containing any database file, so that I could quickly see if any of the SQL Server data volumes have space issues. But, I guess something has to remain for Microsoft to add in future releases!