Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

What is Bottleneck Analysis?

When the SQL Server is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting may mean nothing in the overall scheme of things, or it can be an indicator that a database bottleneck exists.

This is when wait-based or bottleneck analysis comes into play. DBAs use this form of performance analysis to determine if perceived bottlenecks in a database are contributing to a performance problem.

Bottleneck analysis is a valid method of measuring performance because it helps a DBA track where a database server, user sessions, the operating system and etc., have been spending their time. If I/O completion waits or heavy table-scan activity has been dragging a database’s performance down, a DBA can use bottleneck analysis to confirm the actual root cause.

Most likely, a DBA who has to manage SQL Server databases in addition to the SQL Server is no stranger to bottleneck analysis. SQL Server introduced a wait event interface long ago, although no one really began to notice it until SQL Server7. As of this writing, SQL Server Database 10g has been out for about a year, and if the DBA has migrated to it, it is likely that person has seen the increased emphasis SQL Server has put on bottleneck analysis as the primary method to use in identifying performance issues.

SQL Server professionals are just now beginning to recognize the benefits of using a bottleneck approach to performance analysis. Much like the early days of SQL Server, few SQL Server DBAs have looked at Microsoft’s wait event interface, but this is beginning to change. There is more to bottleneck analysis than just examining wait events, called wait types in SQL Server. Broadly speaking, there are two major types of bottlenecks for which the DBA should be on the lookout.

The first type of bottleneck is the “hit the wall” variety. An example of this would be a SQL Server database running out of space in its transaction log or a database suddenly going offline. For all intents and purposes, work grinds to a complete stop, or the impact of the bottleneck is great enough to stop major activities. These types of bottlenecks, usually called immediate bottlenecks, are the kind that should be detected far in advance of their actual occurrence, because when they do occur, the DBA can expect the phone to immediately start ringing off the hook with users lodging complaints.

The second type of bottleneck is more insidious because it is gradual in nature. The bottleneck starts slowly with hardly anyone noticing a performance slowdown, but it begins to build in effect and intensity. It slowly starts to drain the life from the SQL Server system, and because of this, it has been nicknamed a vampire. An example of this type of bottleneck is similar to object fragmentation, in which an object becomes more and more disorganized until I/O performance is drastically affected.

When troubleshooting immediate and vampire styled bottlenecks, typically four areas of the SQL Server are reviewed:

  • Storage

  • System

  • Sessions

  • Operating System


The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter