Before you begin your process of detecting and resolving bottlenecks, you must set up a baseline. A baseline is an accurate and complete performance record of your SQL Server performed during a representative load over a representative time period. This means capturing all the performance monitor counters during a cycle. This cycle could be a day, week or even a month, and would capture both the peak times and the off peak times.
The baseline lets you:
- Analyze and determine bottlenecks
- Compare and contrast the impact of changes that have been made to the system
- Determine periods of low activity that can be a maintenance window
The bulk of this article will focus on analyzing and determining the cause of bottlenecks. However, you need to collect a baseline so you can determine the impact of any changes you make to your SQL Server and to determine the cause of any future performance degradation. For example, should the amount of data in a table grow exponentially, a table scan may have been chosen over an index scan, which can degrade performance significantly. A good baseline will allow you to detect such changes.
Bottleneck symptoms to consider
Memory bottleneck symptoms
Memory bottleneck symptoms normally manifest themselves in the error log as error messages:
Requires Free Membership to View
- while waiting for memory resources to execute the query. Rerun the query.
- Timeouts while waiting for a memory resource – there is insufficient memory available in the buffer pool.
- 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
You may notice a significant increase in query execution time, sudden large numbers of recompilations, a drop in the number of active queries and sudden unexpected CPU spikes.
Typically, your SQL Server will also have low page life expectancy and low buffer cache hit ratio. The system will be sluggish and I/O use will be higher than normal. CPU use will be pegged at more than 80% for long periods of time.
Disk bottleneck symptoms
Disk bottleneck symptoms normally manifest themselves as error messages in the application log (i.e., SQL Server has encountered 3,465 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file …). Disk bottlenecks can also manifest themselves as slow SQL Server response times, and disk counters operating close to their maximum values for sustained periods of time. If you walk up to the servers, you will hear disk thrashing or a continuous sound coming from your disks.
CPU bottleneck symptoms
A CPU bottleneck is easy to spot because your SQL Server will have constant high CPU utilization and low overall throughput.
| 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