Changes from SQL 2008 R2 to SQL 2008 R2 SP1
Here is my comparison of changes for the SQL 2008 R2 SP1 update.
New Column(s)
sys.dm_exec_query_stats
http://msdn.microsoft.com/en-us/library/ms189741.aspx
SELECT total_rows,last_rows,min_rows,max_rows,* FROM sys.dm_exec_query_stats
sys.dm_os_sys_info
http://msdn.microsoft.com/en-us/library/ms175048.aspx
SELECT virtual_machine_type,virtual_machine_type_desc,* FROM sys.dm_os_sys_info
New Stored Procedure(s)
This procedure raises the number of partitions from 1000 to 15000.
sys.sp_db_increased_partitions
http://technet.microsoft.com/en-us/library/hh204563.aspx
EXEC sys.sp_db_increased_partitions @dbname = 'MyDatabase', @increased_partitions = 'on'
New Views(s)
sys.dm_os_windows_info
http://technet.microsoft.com/en-us/library/hh204565.aspx
SELECT * FROM sys.dm_os_windows_info
sys.dm_server_registry
http://technet.microsoft.com/en-us/library/hh204561.aspx
SELECT * FROM sys.dm_server_registry
sys.dm_server_services
http://technet.microsoft.com/en-us/library/hh204542.aspx
SELECT * FROM sys.dm_server_services
sys.dm_server_memory_dumps
http://technet.microsoft.com/en-us/library/hh204543.aspx
SELECT * FROM sys.dm_server_memory_dumps
sys.dm_os_volumne_stats (database_id, file_id)
http://technet.microsoft.com/en-us/library/hh223223.aspx
SELECT * FROM sys.dm_os_volume_stats
Here is an example I have cooked up that will show how each database consumes space on the underlying volumes.
SELECT DB_NAME(f.[database_id]) [DatabaseName] ,vs.[volume_mount_point] [VolumeMountPoint] ,vs.[logical_volume_name] [LogicalVolumeName] ,vs.[file_system_type] [FileSystemType] ,f.[type_desc] [TypeDesc] ,f.[name] [LogicalName] ,f.[physical_name] [Path] ,CONVERT(DECIMAL(15,3),vs.[total_bytes]/1073741824) [TotalOnVolume_GiB] ,CONVERT(DECIMAL(15,3),vs.[available_bytes]/1073741824) [AvailableOnVolume_GiB] ,CONVERT(DECIMAL(15,3),f.[size]*CONVERT(FLOAT,8192)/1073741824) [Size_GiB] FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.[database_id], f.[file_id]) vs