What has changed in SQL 2008 R2 SP1?

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

Leave a Reply