INF: New SQL Server features in SQL Server 2008 R2 –Part 2

Continued from https://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 https://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.

https://msdn.microsoft.com/en-us/library/ee210547(SQL.105).aspx
Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs

https://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 https://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 https://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.:

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 https://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 https://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 https://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
https://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