(SQL) Tip of the Day: Waits and Queues
Today’s (SQL) Tip…
SQL Server has long used a multi-threaded programmatic style based upon a “waits and queues” methodology. Everything from pages in memory, time on the CPU, and even locks are based upon sequential request and access. In an on-site environment, many connections or queries requesting the same resources can often cause a bottleneck. In a cloud environment where multiple tenants may be competing for CPU resources, RAM, and other items, performance can be drastically impacted by queries doing nothing more than hurrying up to wait.
Although the below whitepaper is old (SQL 2005), it still has an excellent description of how and why SQL Server uses a wait and queue methodology that is still applicable even in the cloud-based world. In addition, the whitepaper describes how to use knowledge of this methodology to improve your queries and investigate performance related issues. As such, it’s a recommended read: