Couple of weeks ago I had a chance to visit set of our customers and talk to them directly. It was an unforgettable experience. As a part of the visit I made set of presentations related to SQLOS and new features it enables in SQL Server 2005. I thought that some of you might be interested to take a look at the presentation so below is presentation's outline. Let me know if you have any questions.

What is New?

• Dynamic affinity
• Load balancing
• Dynamic memory settings
• Native NUMA support
• SoftNUMA support
• Application partitionning
• Memory Broker
• Common caching framework
• DAC
• Diagnostics

Dynamic Affinity

• Changes set of CPUs SQL Server is allowed to use
• Use “sp_configure affinitymask*” to change SQL Server’s affinity. No longer requires SQL Server reboot
• Enables great consolidation story for SQL Server and other applications on the same machine
• Enables dynamic reconfiguration during failover
• Before: SQL Server has to be rebooted for affinity changes to take effect

Load Balancing

• Distributes requests amongst schedulers
• Connections are no longer bounded to specific CPU, scheduler
• Homogeneous CPU load – doesn’t overload given CPU when using hard affinity and hence improves performance both response time or throughput
• Before: Connection were bound to a given Scheduler/CPU when opened

Dynamic memory settings

• Controls amount of physical memory SQL Server can use
• Use sp_configure max server memory or min server memory. No longer requires SQL Server restart for changes to take affect
• Enables great consolidation story for SQL Server and other applications on the same machine
• Provides dynamic reconfiguration during failover
• Before: SQL Server had to be rebooted for memory changes to take effect when using AWE or locked pages in memory

Native NUMA Support

• Mimics hardware configuration
• Automatically enables NUMA support on real NUMA hardware
• Provides great scalability and performance on high end hardware
• Enables application partitioning for better scalability, configuration and maintenance
• Acts as perfect consolidation story along with dynamic affinity and memory settings
• Before: Very restricted support for NUMA, only enabled with a specific trace flag

SoftNUMA Support

• Activates custom NUMA configuration on top of any hardware
• Registry settings control SoftNUMA configuration
• Provides greater performance, scalability, and manageability on SMP as well as on real NUMA hardware
• Before: No SoftNUMA support

Application Partitioning

• Provides resource partitioning of such resources as CPU and memory across different application for single SQL Server instance
• Achieved by leveraging either NUMA or SoftNUMA configuration along with other new SQL Server features
• Enables predicted resource distributions amongst different application
• Enables soft application isolation with better performance characteristics rather than multi instance
• Before: Limited application partitioning support – only with VIA
• Memory Broker
• Provides dynamic memory distribution amongst large internal memory consumers such as Buffer Pool, Optimizer, Query Execution and Caches
• Enabled automatically
• Tunes memory usage by each memory consumer according to a specific load
• Before: No dynamic memory redistribution

Common Caching Framework

• All caches share single common caching frame work
• Enabled automatically
• Provides finer grain caching control
• All caches play together
• Size of a single cache is restricted
• New command dbcc freesystemcache
• Single, common, response to different types of memory pressure
• Before: Only database cache and procedure cache were sharing caching framework

Dedicated Admin Connection (DAC)

• Provides access to overloaded server
• DAC resources are allocated during SQL Server startup
• Great to control runaway queries that consume all resources
• Can be used to run diagnostic queries
• Considered to be a last resort to connect to unhealthy server
• Caution: Don’t run complex queries
• Before: No DAC suported might result in SQL Server restart

Diagnostics

• Enables rich diagnostic and understanding of resource distribution/consumption by different components inside of SQL Server
• Exposed through OS performance monitor and set of dynamic management views, DMVs
• Enables DBA and Microsoft support engineers to resolve
• Memory problems
• High CPU utilization
• High contention points
• I/O subsystem bottlenecks
• Before: Performance monitor only