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

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


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  

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