Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Query Performance Guidelines Tutorial


By:   |   Read Comments (3)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


Read the tutorial!

In my years being a DBA I've seen many (even made some myself) common mistakes when reviewing the SQL queries that run against the systems I maintain. With this experience I've found that there are a some general guidelines that should be followed when writing queries and also when designing a database schema.

In this tutorial we will take a look at a few different areas where these common mistakes are made and what can be done to fix them. These areas include: query writing, indexing and schema design.

The specific topics that will be covered include the following:

  • Query writing:
    • How Join Order Can Affect the Query Plan
    • Remove Function Calls From the SELECT List
    • Avoid Using <> in WHERE Clause
    • Avoid Using Functions in WHERE Clause
    • Avoid Using Wildcard Characters to Start Search Criteria
    • Use a Derived Table in Place of IN Predicate With Aggregate Functions
  • Indexing:
    • Make Sure All JOIN Columns are Indexed
    • Use WHERE, JOIN, ORDER BY, SELECT Column Order When Creating Indexes
    • Make Sure All Tables Have a Clustered Index Defined
  • Schema design:
    • Use DELETE CASCADE Option to Handle Child Key Removal in Foreign Key Relationships
    • Denormalize For Performance
Read the tutorial!


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, September 09, 2014 - 9:44:56 AM - Dennylou Back To Top

Always the critic never the complement.  I found this very helpful.  As with everything that you read, use the things that help and discard those that do not pertain.  I believe this is a great turorial, gives someone a lot of food for thought.  Keep up the great work.  As for the critique, they are all good, but again everything needs to be tried and tested before it becomes production.


Wednesday, February 26, 2014 - 10:47:30 AM - Bill Back To Top

The conclusions reached in the index order tutorial may be a bit simplistic.  I'm referring to your recommendation that the WHERE column always be placed before the JOIN column in the index order.  In a robust system, where many different queries are executed against the tables, this strategy could quickly lead to index bloat.  That is, you could end up having a non-clustered index for every query that has a different WHERE clause.  This in turn causes it's own problems - excessive space used, keeping the indexes up to date, defragmenting them, etc.  I think your example is a bit too simplistic to justify such a sweeping generalization.


Wednesday, February 26, 2014 - 7:39:57 AM - G Bryant McClellan Back To Top

Generally that is a great distillation of many of the things I have learned starting with SQL v4.2. Your supporting cases and demonstrations are very illustrative.

As always, things depend on other things. There are edge cases where clustered indexes can be a penalty. Lookup tables that fit in 1 page experience less I/O as a heap because the clustered index does not need to be read. So while I would generally agree and, in fact, encourage developers to think carefully about exactly what that clustered key refers to they should also see that there are cases where a heap is a tuning tool.


Learn more about SQL Server tools