Identify performance bottlenecks in SQL Server

Identifying and resolving bottlenecks is key to a high-performing SQL Server. Types of bottlenecks include memory, disk and CPU bottlenecks. SQL Server MVP Hilary Cotter explains how to locate these log jams using System Monitor, SQL Profiler and DMVs.

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 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.


TABLE OF CONTENTS


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.

 

This was first published in July 2007

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close