Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  SQL Server Tips by Burleson

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 Oracle databases in addition to the SQL Server is no stranger to bottleneck analysis. Oracle introduced a wait event interface long ago, although no one really began to notice it until Oracle7. As of this writing, Oracle 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 Oracle 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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter