Set of new features in SQL Server 2005 enabled by SQLOS


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 

Comments (4)

  1. Начиная с версии SQL 7.0 появилось такое понятие как SQL OS. Государство в госуд

  2. Dan says:

    "Provides resource partitioning of such resources as CPU and memory across different application for single SQL Server instance"

    Hi, cool article.  I am starting to do some testing with SQL Server 2005, but where do I configure this feature?

    Thanks,

  3. La stratégie :

    SQL Server 2005 Update

    from Paul Flessner (6/04/2006)

    Les versions et combien ca…

  4. Joe says:

    I am not sure why we see comments in Russian here, but to answer Oleg’s questions, SQL OS term is coined to reflect how SQL Server interacts with the OS – all the direct calls into the Base OS APIs – memory management, I/O management, thread scheduling and synchronization.