Subscribe to this blog
Briefcase
Print
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:
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 problem. Parallel 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 ;-)
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