(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:

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc


Comments (1)

  1. Anonymous says:

    A very good white paper that describes a missing element of performance tuning. Many performance tuning speeches, presentations, papers and books focus on indexing. This helps identify where the bottleneck is. If the bottleneck is in the need for more
    memory, faster disk or more CPU, it's helpful to know that before just launching into an index analysis.

    I would recommend a minor rewrite to specifically reference SQL Server 2012 or 2014 in order to get the search hits this paper deserves.

Skip to main content