The next time you are enjoying a long, tall, cold one, pause for a moment to study the neck of the bottle. The neck is smaller in size than the larger body of the bottle. The bottle design limits the amount of beer exposed to air in order to limit oxidization and, more importantly, provides a large reservoir of air to buffer the internal air pressure in the bottle during the pasteurization process and, in some cases, the bottle design allows it to continue the fermentation process after bottling and pasteurization. As you pour the beer out of the bottle, notice how the speed of the pour is limited by the size of the bottle's neck.
With SQL Server, bottlenecks are likewise very powerful. One bottleneck can manifest itself as other bottlenecks, and once you remove one, you may find the SQL Server is constrained
Once you've identified the bottleneck, the easy way to resolve it is by replacing the resource with one that provides better performance:
- Disk bottleneck. Add faster drives or a more appropriate RAID array.
- Memory bottleneck. Add more memory.
- Processor bottleneck. Add more or faster processors.
- Network bottleneck. Move to a gigabyte Ethernet.
These easy answers, however, are not often economical decisions, and in many cases will not solve the underlying problems causing these bottlenecks.
Here are a couple more points about SQL Server bottlenecks: Disk bottlenecks tend to manifest themselves in high processor utilization situations because the processor is busy managing disk I/O. Memory bottlenecks manifest themselves as high processor and disk utilization (the processor is busy managing virtual memory and disk I/O). When examining bottlenecks, first start examining memory bottlenecks, then disk bottlenecks and finally processor bottlenecks.
- Home: Introduction
- Step 1: Create baseline and consider bottleneck symptoms
- Step 2: Detect performance bottlenecks with these tools
- Step 3: SQL Server queries with DMVs for examining bottlenecks
|ABOUT THE AUTHOR:|
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.|
Copyright 2007 TechTarget
This was first published in July 2007