Continued from http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/13/inf-new-sql-server-features-in-sql-server-2008-r2-part-1.aspx
There are a host of enhancements that come as a part of SQL Server 2008 R2. I have outlined some more features below :
3. LockHash changes: As mentioned in http://blogs.msdn.com/sqlserverstorageengine/archive/2010/01/18/improvement-in-minimizing-lockhash-key-collisions-in-sql-server-2008r2-and-its-impact-on-concurrency.aspx, “In SQL Server 2008 R2, the hashing algorithm calculating the lockhash value was rewritten. The goals to make it more complex on the one side to avoid collisions without compromising the performance. We analyzed a lot of ISV (Independent Software Vendor) customer database plus some other other customer databases to come up with a hash algorithm which used comparable CPU cycles but produced a better distribution of hashed values. In all the cases that we tested, we found a dramatic reduction of hash key collisions.”. Please refer the blog for more details.
4. SQL Server 2008 R2 on Windows 7 / Windows 2008 R2 with more than 64 CPUs: As mentioned in https://blogs.msdn.com/psssql/archive/2009/06/16/sql-server-on-windows-7-windows-2008-r2-with-more-than-64-cpus.aspx, "the release of the Windows 7/Windows 2008 R2 will support more than 64 CPUs. It is also documented that the SQL Server 10.5 will extend the SQL Server Engine to support more than 64 CPUs.". Please refer below for more details.
http://msdn.microsoft.com/en-us/library/ee210547(SQL.105).aspx Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs
http://msdn.microsoft.com/en-us/library/ee210585(v=SQL.105).aspx ALTER SERVER CONFIGURATION SET PROCESS AFFINITY (Transact-SQL)
5. Timer changes: R2 introduces a new column in the sys.dm_os_sys_info to indicate what timer is currently in use. As mentioned in http://msdn.microsoft.com/en-us/library/ms175048(SQL.105).aspx, time_source_desc column describes the Windows API (Application Programming Interface) that SQL Server is using to retrieve wall clock time.
{ /* Below in SQL Server 2008 will give error */
select time_source_desc from sys.dm_os_sys_info
Msg 207, Level 16, State 1, Line 1 Invalid column name 'time_source_desc'.
/* Below in SQL Server 2008 R2 will give the timer API (Application Programming Interface) */
--returned MULTIMEDIA_TIMER on my system. A quick internet search reveals this refers to Windows Multimedia Timer API. }
6. DMV “Changes” in SQL 2008 R2 (for new DMVs, please refer BOL link in “Additional Enhancements” below): A MS MVP has an interesting linked server query to find DMVs that have changed in SQL 2008 R2. This linked server query is in his blog http://sqlblog.com/blogs/aaron_bertrand/archive/2009/08/11/new-columns-in-sql-server-2008-r2-dmvs.aspx (this query runs fine and only requires change in linked server name, the query initially gave me a couple errors but that was because my copy automatically removed a couple of spaces which i’d to manually add per the errors). I ran this linked server query from my SQL Server 2008 10.0.2531 (SP1) Enterprise to SQL Server 2008 R2 RTM 10.50.1600 to give me below list. So, 13 new columns, all in the sys.dm_os_* DMVs. For a couple of other DMVs, one column got a little bigger.:
status
viewname
col
type
FormerT
precision
FormerP
scale
FormerS
max_length
FormerML
new
dm_os_memory_nodes
cpu_affinity_mask
bigint
NULL
19
0
8
online_scheduler_mask
processor_group
smallint
5
2
dm_os_nodes
dm_os_sys_info
affinity_type
int
10
4
affinity_type_desc
nvarchar
120
process_kernel_time_ms
process_user_time_ms
time_source
time_source_desc
dm_os_threads
dm_os_workers
changed
dm_fts_fdhosts
fdhost_name
256
128
scheduler_count
7. Utility Control Point (UCP) and Data-tier Applications (DAC): There are two big things we are doing. SQL Server customers have a requirement to manage their SQL Server environment as a whole, addressed in this release through the concept of application and multi-server management in the SQL Server Utility.
One is the Utility Control Point (UCP), where I am going to have a server manage other servers like in below screenshot. Please refer http://msdn.microsoft.com/en-us/library/ee210579(v=SQL.105).aspx for more details.
The second problem is even more exciting to me. It is called the DAC (refers to data-tier application). When you need to move your database from one SQL instance to another SQL instance for whatever reason—maybe test reduction or moving from a busy system to a non-busy system or moving from test to production—you need to put your thinking cap on. You need to take your user database. And don’t forget about your applications tied to that… Please refer MS whitepaper “Data-tier Applications in SQL Server 2008 R2” at http://msdn.microsoft.com/en-us/library/ff381683.aspx for more details on DACs using which developers and database administrators can automate and facilitate common operations and practices in the lifecycle of database.
====================== Additional Enhancements (documented in SQL Server Books online) ======================
Please refer SQL Server Books Online "What's New (SQL Server 2008 R2)" section in http://msdn.microsoft.com/en-us/library/bb500435(SQL.105).aspx
============= Additional info =============
Considerations for Side-by-Side Instances of SQL Server 2008 R2 and SQL Server 2008 http://msdn.microsoft.com/en-us/library/ee210714(SQL.105).aspx
Vijay Rodrigues SE, Microsoft SQL Server
Reviewed by Sudarshan Narasimhan & Nickson dickson TL , Microsoft SQL Server