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) */

select time_source_desc
from sys.dm_os_sys_info

--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

NULL

0

NULL

8

NULL

new

dm_os_memory_nodes

online_scheduler_mask

 

bigint

NULL

19

NULL

0

NULL

8

NULL

new

dm_os_memory_nodes

processor_group

 

smallint

NULL

5

NULL

0

NULL

2

NULL

new

dm_os_nodes

online_scheduler_mask

 

bigint

NULL

19

NULL

0

NULL

8

NULL

new

dm_os_nodes

processor_group

 

smallint

NULL

5

NULL

0

NULL

2

NULL

new

dm_os_sys_info

affinity_type

 

int

NULL

10

NULL

0

NULL

4

NULL

new

dm_os_sys_info

affinity_type_desc

 

nvarchar

NULL

0

NULL

0

NULL

120

NULL

new

dm_os_sys_info

process_kernel_time_ms

 

bigint

NULL

19

NULL

0

NULL

8

NULL

new

dm_os_sys_info

process_user_time_ms

 

bigint

NULL

19

NULL

0

NULL

8

NULL

new

dm_os_sys_info

time_source

 

int

NULL

10

NULL

0

NULL

4

NULL

new

dm_os_sys_info

time_source_desc

 

nvarchar

NULL

0

NULL

0

NULL

120

NULL

new

dm_os_threads

processor_group

 

smallint

NULL

5

NULL

0

NULL

2

NULL

new

dm_os_workers

processor_group

 

smallint

NULL

5

NULL

0

NULL

2

NULL

changed

dm_fts_fdhosts

fdhost_name

 

nvarchar

nvarchar

0

0

0

0

256

128

changed

dm_os_sys_info

scheduler_count

 

int

smallint

10

5

0

0

4

2

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.

clip_image002clip_image003

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.

clip_image004

======================
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