Monthly Archives: September 2016

SQL Server Queries: Writing queries for performance – Part 2

Writing an efficient query

An efficient query is the result of following a few simple rules and patterns. Part 1 of the series outlined general goals when creating a query. This part of the series gives concrete rules for writing efficient queries.

The Rules

  1. Use the minimum number of tables while meeting business requirements
  2. Only return the columns needed / specify the columns to be used
    1. Never use SELECT *
    2. This reduces network traffic, client processing and ensures you won’t get extra columns returned in your query if the base tables are modified
  3. Add a WHERE clause if possible
    1. Directly reduces I/O and network traffic
    2. Use sargable arguments (arguments that can effectively use an index)
      • =
      • >
      • <
      • <=
      • >=
      • BETWEEN
      • IN
      • LIKE ‘Text%’
    3. Avoid non-sargable arguments
      • <>
      • NOT IN
      • NOT LIKE
      • LIKE ‘%text’
      • LIKE ‘%text%’
      • OR
      • Functions in WHERE clause or JOIN
      • Avoid use of functions in clause
  4. Use “SET NOCOUNT ON” for all stored procedures
  5. Use stored procedures or parameterized queries
    1. Allows your execution plan to be re-used
    2. Especially important when the query is executed multiple times
  6. Use consistent formatting
    1. Create guidelines and be sure your developers follow them
  7. Verify the query plan
    1. Indexes
    2. Seeks versus scans
    3. Implicit conversions
    4. Join types
  8. Avoid cursors / RBAR
  9. Avoid query hints and index hints (let the query engine do its’ job and adjust the plan as the data changes over time)


SQL Server Queries: Writing queries for performance – Part 1

Troubleshooting query performance can be a frustrating task for developers. The goal of this series is to break down the steps to analyze a query and provide concrete actions that can be used to create performance oriented queries.

When trying to improve the performance of a query you will almost always be working on some or all of the following high level tasks:

  • Minimize network traffic
  • Minimize physical I/O
  • Minimize logical I/O
  • Minimize CPU usage
  • Perform set-based operations

Remember – queries are optimized in relation to the current system and database. This means that performance begins before a single line of code is written and before a single byte of data is inserted. Disk setup, network setup, server setup, data model, indexes and other databases on the system all impact performance (this also means that your query impacts the other queries or even entire databases running on the same server).

Writing efficient queries also means less troubleshooting in the future.