Set of new features in SQL Server 2005 enabled by SQLOS

  • Article

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