SQL Server Query Performance Guidelines Tutorial


By:   |   Updated: 2014-02-26   |   Comments (3)   |   Related: More > Performance Tuning

Itís the Database. Or Is It? Put an End to Finger Pointing!

Free MSSQLTips Webinar: Itís the Database. Or Is It? Put an End to Finger Pointing!

During this webinar, we'll show how SolarWinds Server & Application Monitor (SAM) can enable your IT team to monitor the health and availability of your Microsoft SQL Servers. SAM in the hands of your IT teams provides broad visibility across the stack to easily identify the underlying cause for database availability and performance problems.


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 Updated: 2014-02-26


get scripts

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





Comments For This Article




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.



download


Recommended Reading

Different Ways to Flush or Clear SQL Server Cache

Fastest way to Delete Large Number of Records in SQL Server

UPDATE Statement Performance in SQL Server

How to find out how much CPU a SQL Server process is really using

SQL Server stored procedure runs fast in SSMS and slow in application





get free sql tips
agree to terms


Learn more about SQL Server tools