12 Steps for Tuning SQL Server query performance

This is a quick blog

[read: steps] on how to tune the SQL Server query performance. Assuming you have the knowledge of the tech jargons associated with SQL Server and related things, let’s begin with the first things first.

1. Check for Tables+Row counts

  • Make sure you are working with tables (not views)
  • Make certain you know the row count
2. Examine the filters

  • Examine WHERE and JOIN clauses — what is the filtered row count?
3. Know the selectivity of tables

  • Make sure to work with the smallest possible logical set
  • Most useful for RIGHT, LEFT, OUTER joins
  • Know when the predicate is applied–should be earlier rather than later
4. Analyze query columns

  • Look for select * or scalar functions (the more data brought back, the less optimal it may be to use certain functions
  • Look for CASE, CAST, CONVERT
  • Sub-queries
  • SARG-able (make an index searchable so a full
    scan isn’t needed)
5. Review existing Keys,Constraints and Indexes

  • Know what objects exist (avoid duplicating later on)
  • What is primary key definition and is it clustered?
  • Check to see if covering indexes can be created (avoid duplication, overlapping)
6. Examine the execution plan

  • Set statistics on (set statistics IO on + set statistics time on)
  • Check the actual plan, not the estimated plan
  • Run the plan
  • Look for logical reads (fewer logical I/Os means faster query
7. Record results

  • Focus on Logical I/O (number of logical reads)
8. Adjust the quert

  • Focus on most expensive operations first.
9. Re-run the query

  • Record the results and compare
  • Make small changes as needed
10. Consider adjusting indexes

  • Look to reduce logical I/O
  • Consider covering index—an index that includes every column that satisfies the query
  • Consider a filtered index (but not if you have parameterized statements
11.Re-run the query

12.Engineer out the stupid

  • Code-first generators (EMF, LNQ, hibernate) can be mis-used and bloat the plan cache
  • Nested views that go across linked servers.
  • Scalar functions
  • Abuse of wildcards (*) — pulling back too many rows
  • Cursors and row-by-row processing.
  • Join/query/table hints