Udostępnij za pośrednictwem

SQL 2016 - It Just Runs Faster: Updated Scheduling Algorithms

  • Artykuł

SQL Server 2016 gets a scalability boost from scheduling updates. Testing uncovered issues with the percentile scheduling based algorithms in SQL Server 2012 and 2014. A large, CPU quantum worker and a short, CPU quantum worker can receive unbalanced access to the scheduling resources.

 

Take the following example. Worker 1 is a large, read query using read ahead and in-memory database pages and Worker 2 is doing shorter activities. Worker 1 finds information already in buffer pool and does not have to yield for I/O operations. Worker 1 can consume its full CPU quantum.

 

On the other hand, Worker 2 is performing operations that require it to yield. For discussion let's say Worker 2 yields at 1/20th of its CPU, quantum target. Taking resource governance and other activities out of the picture the scheduling pattern looks like the following.

 

clip_image001

 

Worker 1 is getting ~5 times more CPU cycles than Worker 2. In testing we found issues with various workloads and system tasks. If Worker 2 is the log writer it takes longer to harden log records, which holds locks, which can lead to blocking and throughput issues.

 

SQL Server 2016 and Windows Azure SQL Database (WASD) monitors the quantum usage patterns allowing all workers to get fair treatment. The same pattern described above looks like the following on SQL Server 2016. In this simplistic example Worker 2 is allowed to consume repeated quantum's preventing Worker 1 from monopolizing the scheduler in an unfriendly pattern.

 

clip_image002

 

Note: The scheduler changes were deployed to Windows Azure SQL Server Database in March of 2014.

 

'It Just Runs Faster' - SQL Server 2016 scheduling algorithms balance the work load better leading to enhanced scalability.

 

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

  • Anonymous
    April 02, 2016
    Impressive work! I'm loving the "it just runs faster" posts Bob! Any idea if the scheduling improvements made it into the RC1 or RC2 bits?Will we have a chance of the same scheduling improvements making their way into a 2014 SP?-Mike
  • Anonymous
    April 07, 2016
    The comment has been removed