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 Bad SQL?

Before the DBA can identify problem SQL in the database, the question of “What is bad SQL?” needs to be asked. What criteria should be used when the hunt for problem SQL in critical systems is performed?

Even seasoned experts disagree on what constitutes efficient and inefficient SQL, so there is no way to sufficiently answer this question to every Microsoft professional’s satisfaction. The following list contains general criteria that can be used when evaluating the output from various database monitors or personal diagnosticScripts:

  • Overall Response (Elapsed) Time: This is how much time the query took to parse, execute, and fetch the data needed to satisfy the query. It should not include the network time needed to make the round trip from the requesting client workstation to the database server.

  • CPU Time: This is how much CPU time the query took to parse, execute, and fetch the data needed to satisfy the query.

  • Physical I/O: Often used as the major statistic in terms of identifying good vs. bad SQL, this is a measure of how many disk reads were caused by the query in order to satisfy the user’s request. While controlling disk I/O is desired, it is important the DBA not focus solely on physical I/O as the single benchmark of inefficient SQL. Make no mistake, disk access is slower than memory access and also consumes processing time making the physical to logical transition, but the entire I/O picture of a SQL statement should be considered. This includes looking at a statement’s logical I/O as well.

  • Logical I/O: This is a measure of how many memory reads the query took to satisfy the user’s request. The goal of tuning I/O for a query should be to examine both logical and physical I/O and use appropriate mechanisms to keep both to a minimum.

  • Repetition: This is a measure of how often the query has been executed. A problem in this area is not as easy to spot as the others unless the DBA is very familiar with the application. A query that takes a fraction of a second to execute may still be causing a headache on the system if it is executed erroneously. One example would be a query that executes in a runaway T-SQL loop over and over again.

There are other criteria that can be examined. Examples include criteria such as sort activity, temp table usage, or access plan statistics that show items such as Cartesian joins and the like; however, more often than not, these measures are reflected in the criteria listed above. Fortunately, SQL Server records most of the above measures. This makes tracking the SQL that has been submitted against Microsoft databases much easier than other database engines.


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