Step-by-Step Guide

Identify performance bottlenecks in SQL Server

A beer bottle has one bottleneck (or limiting constraint) that will slow down the flow of beer. In SQL Server, there are four physical factors, also referred to as bottlenecks, that can slow down your overall system throughput and degrade performance. These SQL Server bottlenecks are disk, memory, processor and network.

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

    Requires Free Membership to View

    Login

More on SQL Server performance and tuning:
Target SQL Profiler to determine performance problems

Performance tune your SQL Server with these tools and tips
by a completely different bottleneck. For example, on a SQL Server with disk constraints (a low rpm drive), and a small amount of available memory (which will cause excessive paging), you will find high processor utilization, as SQL Server's processor is spending a large amount of time paging to disk. Adding more memory may reduce the amount of paging, but the disk bottleneck may become the primary constraint.

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.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top