Waits and Queues

How many of you have read this article: SQL Server Performance Tuning Using Waits and Queues? (PDF) If you haven’t, you might look at a review of the paper from Jeremiah Peschka. There is good advice about which parts to read and which to ignore a decade later. There’s an updated version from Paul Randal and SQLSkills as well.

This leads me to where you might expect this week, with a question for you. Do you use waits and queues to troubleshoot? (Survey)

Not every performance problem needs you to dig deeply into the way that the database engine processes your query, but there are no shortage of times when understanding waits and queues can help you determine how to tune your system. This is a well known method of trying to determine where to focus your efforts, so I wonder how many of you have tried this.

If you have, let us know what’s worked or what hasn’t. If you’ve never tried this, then drop a quick note as well if you have a reason you haven’t used waits and queues.

Performance tuning is a skill everyone needs, and most everyone can work on. This is often one of the most popular topics for talks and classes, and any help you can get will grow your career. Both in your own self confidence as well as impressive to interviewers when you have an answer or a methodology to give when solving any problems you’re given. Whether in an interview or in your job.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.

1 Response to Waits and Queues

  1. Paul Brewer says:

    Hi Steve,
    I worked on a database warehouse a few years ago that was the target for multiple heavy lifting SSIS packages. When 4 or more of these packages were inserting data concurrently, the server ground to a halt with all other requests stuck in a ‘RESOURCE_SEMAPHORE’ wait state. No one gives the feature much credit but we solved the problem using the resource governor. By default each request can acquire up to a 25% memory grant, we revised this down to 10%, concurrency more than doubled and SSIS package run times were unaffected. This change was driven using wait stats, ‘RESOURCE SEMAPHORE’ waits went down from > 10% to < 1%.
    Regards
    Paul

    Like

Comments are closed.