Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Waits and Queues

Recently, I was asked to do an extensive sql server performance audit and review.  I will be sharing some of the information, scripts and documents that I referenced when I completed this thorough analysis.  One of the most important things in performance tuning, is the knowledge that goes along with best practices on SQL Server Waits and Queues.  The article is quite in-depth, but contains a treasure trove of valuable performance tuning information, especially with respect to waits and queues. 

In this whitepaper, you will find examples and explanations of every kind of Wait Type, and how to correlate it to a particular performance resource issue or bottleneck.  You will readily be able to identify the cause of these waits, and learn how to optimize the performance of your sql server.  A user typically experiences poor performance as a series of waits. Whenever a SQL query is able to run but is waiting on another resource, it records details about the cause of the wait. These details can be accessed using the sys.dm_os_wait_stats DMV.  One can examine the accumulated cause of all the waits, based on the output of the DMV, as displayed below:

SQL Waits DMV

So, just to take the first wait type that shows up as the highest % of waiting, on the server, CXPACKET.

This wait occurs when trying to synchronize the query processor exchange iterator. Consider lowering the degree of parallelism if contention on this wait type becomes a problemParallel process waits can sometimes occur when data is skewed. In such cases, one parallel thread may process a larger number of rows while another may process a smaller number of rows and so on.

In an OLTP environment, excessive CXPACKET waits can affect the throughput of other OLTP traffic.  In a data warehouse environment, CXPACKET waits are expected for multiple proc environments.

 

Therefore, for OLTP workloads, you may want to consider limiting parallelism by setting max degree of parallelism to some number less than the total number of CPUs, and other than the default '0', meaning ALL processors.  Please see the document for definitions on other wait types.

In another example, I already authored some articles on IO usage, statistics, and scripts. High I/O usage alone is not necessarily an indication of a problem, or I/O pressure.  In fact, IO_stalls, is the total cumulative time, in milliseconds, that users waited for I/O to be completed on the file since the last restart of SQL Server.  One DMV, sys.dm_io_virtual_file_stats provides a breakdown of SQL Server reads, writes, and io_stalls for a particular database or transaction log file.

There is a complete section dedicated to IO_Stalls, which is one type of wait that can help identify performance bottlenecks.  However, this is just a tiny bit in a large pool of useful information contained in the doc.

Here's a summary of this document, which can be downloaded directly from Microsoft, by clicking on the link below.

 

SQL Server 2005 Performance Tuning using the Waits and Queues is a proven methodology that allows one to identify the best opportunities to improve performance, the so called “biggest bang for the buck”. These performance improvements are likely to have a significant return on the performance tuning time investment. The methodology helps identify the areas of slow performance by looking at the problem from two directions called Waits and Queues. An analysis of Waits indicates where SQL Server is spending lots of time waiting. In addition, the biggest waits point out the most important or relevant Queues (that is, Performance Monitor counters and other data) for this workload. The cross validation of the waits analysis enables us to eliminate all except the most significant performance counters, and provides a strong indication of pressure on specific resources.

Application performance can be easily explained by looking at SQL Server waits and System or Resource queues. In SQL Server 2005, the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective. The system or resource perspective uses Performance Monitor counters and other data sources to provide a breakdown of system resource usage according to resource queues. Taken together, the value of the application and resource perspectives used by the waits and queues methodology enables us to narrow in on the bottleneck and eliminate the irrelevant performance data.

In sum, Performance Tuning using the Waits and Queues methodology is an effective way to quickly identify and resolve application performance problems because it lets the user discover new and potentially unexpected problem areas, within applications or solutions without the typical guesswork that can accompany such work.

Performance tuning of applications and solutions has been around for many years. The performance of SQL Server 2005 database applications should be evaluated from several different perspectives. Each perspective tells a different section of the complete performance story. Together they paint a detailed performance picture of the whole and also cross validate observations in each specific perspective.

Although, the document was drafted for SQL Server 2005, it is certainly applicable to SQL Server 2008, (some new DMV's may have been introduced).

Without further adieu, to download this whitepaper entitled, SQL Server 2005 Waits and Queues - SQL Server Best Practice Article, authored by Tom Davidson, updated by Danny Tambs, and technically reviewed by Sanjay Mishra (full accreditation goes to these gentlemen), goto:

http://msdn.microsoft.com/en-us/library/cc966413.aspx --> Scroll down to the bottom to get the doc.  I hope your internet connection is good and doesn't keep you WAITing ;-)

 

 

Comments

Posted by Anonymous on 16 January 2010

Pingback from  Senior Business Intelligence Developer / Confidential / Clifton … | Business Intelligence Wisdom

Posted by michael bourgon on 31 March 2010

Several of the links provided for scripts no longer work.  Any way to get an updated version (or for you to post the scripts here)?

Posted by michael bourgon on 31 March 2010

Specifically, the scripts for get_waitstats_2005 and track_waitstats_2005 (and the related tables) are missing.  If you hit the link in the paper, it takes you to a page that the search engine thinks is related (but isn't).

Posted by Anonymous on 20 May 2010

Pingback from  Motorhomes Sale Tucson Az Pedata Rv, Tucson Free Ge

Posted by Anonymous on 20 May 2010

Pingback from  Premier Change, Admin Premier Direct 8th May

Posted by Anonymous on 20 May 2010

Pingback from  380se Headlight Buy, Sale Used Mercedes Benz 380se Automatic Transmission

Posted by Anonymous on 20 May 2010

Pingback from  V3000 Suburban Aftermarket Oem, V30l Definition

Posted by Anonymous on 20 May 2010

Pingback from  1990 Geo Tracker 4x4, Rockhopper Tracker May June

Posted by Anonymous on 20 May 2010

Pingback from  Lucerne Distributor, Lucerne Blurb

Posted by Anonymous on 21 May 2010

Pingback from  Se300 Aftermarket E55 Amg, Buy Se300 Label Printer

Posted by Anonymous on 21 May 2010

Pingback from  Vw Passat Parts Uk Locate, Masini Second Hand Vw Passat Programul Rabla

Posted by Anonymous on 21 May 2010

Pingback from  K3500 Radiator Prices Chevrolet Spark Plug Brake Pads, Express 2500 Replacement Silverado 1500 S10 Blazer

Posted by Anonymous on 21 May 2010

Pingback from  Intrepid Free Shipping Ramcharger Dodge Avenger Parts, Stratus Body Kits Replace Dodge Ramcharger

Posted by Anonymous on 21 May 2010

Pingback from  Weber Spirit Sp 320 Propane Gas Grill, Spirit Discount Costa

Posted by Anonymous on 21 May 2010

Pingback from  Kingswood Best Online, Chevrolet Kingswood Blub Ford Fairlane

Posted by Anonymous on 21 May 2010

Pingback from  Buy Scamp Put, Buy Scamp Comparison

Posted by Anonymous on 21 May 2010

Pingback from  Sell 2008 Acura Mdx Volvo Xc90, 2001 Acura Mdx Brakes

Posted by Anonymous on 21 May 2010

Pingback from  Caterham R500 Ducati, R500 Sale 2006 Slr Mclaren Mercedes Benz Clk

Posted by Anonymous on 21 May 2010

Pingback from  W210 Replacement Headlight Assembly, 620 Pickup Coupon Nissan 200sx Datsun 210

Posted by Anonymous on 21 May 2010

Pingback from  Monarch Butterfly Crafts, Nike Air Monarch Iii Width 4e

Posted by Anonymous on 21 May 2010

Pingback from  Lexus Is350 Headlight Head Lamp, Lexus Convertible Is350 Dolares

Posted by Anonymous on 21 May 2010

Pingback from  Lw200 Direct Fit Oem Problems, Discount 2004 Pontiac Sunfire Fit Bumper Cover

Posted by Anonymous on 22 May 2010

Pingback from  Buy Dodge Pb200 Body Parts, Pb200 Full Vacuum

Posted by Anonymous on 22 May 2010

Pingback from  Xg300 Accessories Hyundai Santa Fe, Hyundai Santa Fe Substitute Brake Pads

Posted by Anonymous on 22 May 2010

Pingback from  Relay Circuit Works, Part Saturn Relay 2 Minivan Keyless Entry

Posted by Anonymous on 22 May 2010

Pingback from  Ford Fiesta S India, Party Supplies Fiesta

Posted by Anonymous on 22 May 2010

Pingback from  Superman Legacy Part 2, Nikon S550 Sale Li Ion Battery

Posted by Anonymous on 22 May 2010

Pingback from  Executive L Desk With Hutch Ball Bearing Slides, Executive Limousine Reno Sedans Shuttles

Posted by Anonymous on 22 May 2010

Pingback from  Husqvarna 3120 Power, Assisted Vertical Knee Raise Power Tower

Posted by Anonymous on 22 May 2010

Pingback from  Pb200 Substitute Intended, Sale 1985 B2000 Mazda Mx6

Posted by Anonymous on 22 May 2010

Pingback from  Toyota Tundra Style Fender Flares, Replace 2002 Tundra Dome Light

Posted by Anonymous on 22 May 2010

Pingback from  Headlight Auto Parts Suzuki Verona, Verona Performance Parts Suzuki Aerio Air Intake

Posted by Anonymous on 22 May 2010

Pingback from  S550 Amc 2008 Mercedes Benz, Y550 Inch Price - 212.animejin.com

Posted by Anonymous on 23 May 2010

Pingback from  Acheter Crucial Ballistix Tracer Pc8500, 94 Mercury Tracer Repair - 387.an74.com

Posted by Anonymous on 23 May 2010

Pingback from  Cj7 Adoption Great, J20 Part Vehicle Grand Wagoneer 1986 Jeep Cj7 - 269.dlmreza.net

Posted by Anonymous on 23 May 2010

Pingback from  G20 Aftermarket J30 G35 Parts Infiniti Fx45, Hyundai Xg350 Refurbished Classified - 341.animejin.com

Posted by Anonymous on 23 May 2010

Pingback from  1998 Lexus Gs400 Review Rear Spoiler, Ls400 Door Handle Promo - 373.unlockiphone30.net

Posted by Anonymous on 23 May 2010

Pingback from  E 450 Econoline Aftermarket F 350, Econoline Super Duty Sale Replacement Headlight Assembly Oxygen Sensor Ford E 450 - 353.rkwrh.com

Posted by Anonymous on 23 May 2010

Pingback from  Rondo Light Contemporary, Tempo Headlight Turn Signal Lamp - 305.1fh.org

Posted by Anonymous on 23 May 2010

Pingback from  200 Ml63 Amg Infiniti Fx45, Ml63 Amg 009 - 374.renters.ws

Posted by Anonymous on 24 May 2010

Pingback from  2009 Kia Optima Colors, Cva Optima Elite Comments - 76.jordanbrandallamerican.com

Posted by Anonymous on 24 May 2010

Pingback from  Bmw 325es Mods, 325es Commercial - 284.tijuanareader.com

Posted by Anonymous on 24 May 2010

Pingback from  Factory Rims Cadillac Allante, Allante Info - 38.jeepsunlimted.com

Posted by Anonymous on 24 May 2010

Pingback from  Rx400h Used Es330 Gs450h Lexus Gx470, 2004 Lexus Gx470 Bluetooth - 385.an74.com

Posted by Anonymous on 24 May 2010

Pingback from  Sony Bravia Kdl 40s5100 Specifications, 300z Discount Hp Coupon Laserjet P1006 - 232.rkwrh.com

Posted by Anonymous on 24 May 2010

Pingback from  Lamp 2005 Kia Amanti, Kia Amanti Sale Mpg - 465.mfbattle.com

Posted by Anonymous on 24 May 2010

Pingback from  Ford Windstar Radiator Not Working Cooling Fan, Problems 1997 F 350 1999 Ford Windstar - 195.computeronlinebingo.com

Posted by Anonymous on 24 May 2010

Pingback from  Part Finder 2002 Bmw 745i Tail Light, 2007 745i Bmw E65 - 150.1fh.org

Posted by Anonymous on 24 May 2010

Pingback from  E 450 Econoline Super Duty Bronco Ii Explorer Sport Trac, C5 C1500 Suburban Radiator Supermodels - 205.tgrconversions.com

Posted by Anonymous on 24 May 2010

Pingback from  Order 2002 Hyundai Xg350, Salvage Used Hyundai Xg350 - 224.tgrconversions.com

Posted by Anonymous on 25 May 2010

Pingback from  1964 Grand Prix Auction 8 Lug Wheels, Air Spring Replacement 2008 Mercury Grand Marquis Monroe Shock Absorbers - 317.1fh.org

Posted by Anonymous on 25 May 2010

Pingback from  Parts Honda Civic Cold Air Intake Including, Cheap Get New Honda S2000 - 427.ja3ra.com

Posted by Anonymous on 25 May 2010

Pingback from  Elantra Touring Sport, 09 Elantra Body Kits - 123.zapstreaming.com

Posted by Anonymous on 25 May 2010

Pingback from  Acura Nsx Blub Picture Galleries, Acura Nsx Block - 208.zapstreaming.com

Posted by Anonymous on 25 May 2010

Pingback from  Cimarron Aftermarket Deville Cadillac Catera Parts, Classic Cadillac Catera - 14.cmanager.org

Posted by Anonymous on 25 May 2010

Pingback from  Pb300 Accessories Hard Rubber, B300 Headlight New Ram 2500 Dodge Polara - 1.an74.com

Posted by Anonymous on 25 May 2010

Pingback from  Townsman Motel Pittsburg Ks, Chevrolet Townsman Free Cover - 471.mfbattle.com

Posted by Anonymous on 25 May 2010

Pingback from  Ssr Auto Real Estate, Pt Chevy Ssr Accessories Owners - 223.computeronlinebingo.com

Posted by Anonymous on 25 May 2010

Pingback from  Gm Ipod Cadillac Escalade, Headlight 2000 Cadillac Escalade Fog Light - 434.codebluehacks.org

Posted by Anonymous on 25 May 2010

Pingback from  E 150 Powerstroke Pt, E 100 Econoline Club Wagon Bulbapedia Exhaust Manifold - 22.jeepsunlimted.com

Posted by Anonymous on 25 May 2010

Pingback from  Nissan Willys Cars, $1500 Headlight 2005 Dodge Ram Nissan Titan - 43.tijuanareader.com

Posted by Anonymous on 25 May 2010

Pingback from  1999 Bmw Z3 2.8 Traditional, 1999 Bmw 328i Check Engine Light - 81.cmanager.org

Posted by Anonymous on 25 May 2010

Pingback from  Lx450 Demo, Lexus Lx450 Message Board - 400.cmanager.org

Posted by Anonymous on 25 May 2010

Pingback from  Difference Between Saturn Sc1 Sc2, Saturn Sc1 Accessories Body Parts - 177.animejin.com

Posted by Anonymous on 25 May 2010

Pingback from  Sc430 Part Our Lexus, Discount Replacement 2009 Lexus Sc430 - 188.jordanbrandallamerican.com

Posted by Anonymous on 25 May 2010

Pingback from  Mercedes Benz R500 Rims Clk55 Amg, R500 Zip Code - 182.mfbattle.com

Posted by Anonymous on 25 May 2010

Pingback from  2000 Dodge Ram 3500 Dually 4x4, C350 Parts Other Cabin Air Filter Replacement Tail Light - 264.akemet.com

Posted by Anonymous on 25 May 2010

Pingback from  Ej200 Games, J200 Sunbird Part Pontiac 6000 Escalade Esv - 73.akemet.com

Posted by Anonymous on 25 May 2010

Pingback from  Z2 Sale Diamond Stealth, Stealth Bat - 459.cmanager.org

Posted by Anonymous on 25 May 2010

Pingback from  142 Review Mighty Avengers Brian Michael Bendis, 142 Radiator Motorcycle Mounting Brackets - 475.akemet.com

Posted by Anonymous on 25 May 2010

Pingback from  J20 Discount Grand Wagoneer Comanche Cherokee Laredo, 1992 Jeep Comanche Truck - 43.mfbattle.com

Posted by Anonymous on 25 May 2010

Pingback from  C25 C2500 1500 Aftermarket Sierra Denali, C25 Fireball Xl5 - 58.akemet.com

Posted by Anonymous on 27 May 2010

Pingback from  1989 - 2000 @ User Chevy Lumina, Chevrolet Lumina Apv Radiator Repair - 175.animejin.com

Leave a Comment

Please register or log in to leave a comment.