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