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:  <blockquote> 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 &lt; 32767
ORDER BY
   drive_percent_free DESC,
   db,
   [file];

 
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:   <blockquote> 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';

 
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.   <blockquote> SELECT
   windows_release,
   windows_service_pack_level,
   windows_sku,
   os_language_version
FROM
   sys.dm_os_windows_info;

 <blockquote> SELECT
   [filename],
   creation_time,
   size_in_bytes
FROM
   sys.dm_server_memory_dumps;

 
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):  <blockquote> SELECT 
   registry_key,
   value_name,
   value_data
FROM
   sys.dm_server_registry;

 
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):  <blockquote> 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;

 
I talked about this DMV in <a href="https://sqlblog.org/2011/04/22/sql-server-2008-r2-sp1-ctp-is-now-available/" title="/blogs/aaron_bertrand/archive/2011/04/22/sql-server-2008-r2-sp1-ctp-is-now-available.aspx" target="_blank">my previous post</a>; 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.  
 
<i>* Not yet in Denali.</i>

 
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:  <blockquote> SELECT 
   virtual_machine_type,
   virtual_machine_type_desc
FROM
   sys.dm_os_sys_info;

 
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 "<a href="http://64.4.11.252/en-us/library/ms143179%28SQL.110%29.aspx" title="http://64.4.11.252/en-us/library/ms143179%28SQL.110%29.aspx" target="_blank">Breaking Changes to Database Engine Features</a>" incorrectly states that these columns were removed.  
 
<i>* This DMV is the only one on this page that has already been added/changed in current builds of Denali (CTP1).</i>

 
Some new xevents were added in SP1 as well. I'm sure Jonathan Kehayias (<a href="http://www.sqlskills.com/blogs/jonathan/" title="http://www.sqlskills.com/blogs/jonathan/" target="_blank">blog</a> | <a href="http://twitter.com/SQLPoolBoy" title="http://twitter.com/SQLPoolBoy" target="_blank">twitter</a>) 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:   <blockquote> 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;