SQL 2016 - It Just Runs Faster: Updated Scheduling Algorithms

  • Articolo

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