New SQLOS features in SQL Server 2012

Here's a quick summary of SQLOS feature enhancements going into SQL Server 2012. Most of these are already in the CTP3 pre-release, except for the Resource Governor enhancements which will be in the release candidate. We've blogged about a couple of these items before. I plan to add detail. Let me know which ones you'd like to see more on:

- Memory Manager Redesign :

Predictable sizing and governing SQL memory consumption:

sp_configure ‘max server memory’ now limits all memory committed by SQL Server
Resource Governor governs all SQL memory consumption (other than special cases like buffer pool)

Improved scalability of complex queries and operations that make >8K allocations

Improved CPU and NUMA locality for memory accesses

Single memory manager that handles page allocations of all sizes

Consistent Out-of-memory handling & management across different internal components

- Optimized Memory Broker for Column Store indexes (Project Apollo)

- Resource Governor

Support larger scale multi-tenancy by increasing Max. number of resource pools20 -> 64 [for 64-bit]

Enable predictable chargeback and isolation by adding a hard cap on CPU usage

Enable vertical isolation of machine resources

Resource pools can be affinitized to individual or groups of schedulers or to NUMA nodes

New DMV for resource pool affinity

 - CLR 4 support, adds .NET Framework 4 advantages

- sp_server_dianostics

Captures diagnostic data and health information about SQL Server to detect potential failures

Analyze internal system state

Reliable when nothing else is working


- New SQLOS DMVs (in 2008 R2SP1)

SQL Server related configuration - New DMV

OS related resource configuration
New DMVs

XEvents for SQL and OS related Perfmon counters

Extend sys.dm_os_sys_info

See previous blog posts here and here.

- Scale / Mission critical

Increased scalability: Support Windows 8 max memory and logical processors

Dynamic Memory support in Standard Edition - Hot-Add Memory enabled when virtualized

- Various Tier1 Performance Improvements, including reduced instructions for superlatches.