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
sys.dm_server_services

OS related resource configuration
New DMVs
sys.dm_os_volume_stats
sys.dm_os_windows_info
sys.dm_server_registry

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.

Comments (4)

  1. Jacky_chen says:

    I hope the SQLOS team blog can  give more articles about memory && CPU  mechanism under SQLOS,because the material  taking about SQLOS  mechanism is so scarce,thank you .

  2. gbowerman says:

    Thanks Jacky, noted.

  3. Paul Wehland says:

    What is the max memory on Window8?  Will it exceed 2TB?

  4. gbowerman says:

    I've not heard an official announcement, but according to Paul Thurrott's site, a new max memory value of 4TB was disclosed at the BUILD conference: http://www.winsupersite.com/…/windows-server-8s-scalability-numbers-140931

    – Guy