SQL query performance tuning

Tuning quick tips

  1. Getting the entire record before apply condition. -> Solution: Apply condition and paging before select.
  2. Using sub-query -> Solution: Turn it into join
  3. Using Table Variable being used with large data -> Solution: use Temp Table

When a query performs badly, 3 things above are the most common causes I came across. After fixed with the solutions above, it performs at an acceptable speed even with a relatively complex query and large data volumes.



Sentryone plan explore is freeware and helps to figure out the bottleneck from a complex query. I don’t understand why the SSMS plan presents the execution plan so badly. If you are not happy with SSMS’s execution plan like me, I strongly recommend Sentry One plan explorer.


From SQL 2014, Execution plan assumptions changed which leads to better performance.

This is reason to upgrade SQL Server and it’s compatibility level to > 120.

Tuning Questions

These are the questions worth we should ask when tuning an SQL query.

  • Stats missing?
  • Stats stale?
  • Sampling adequate?
  • Multi-column stat help?
  • Parameter sniffing?
  • Table variable/MSTVF?
  • Only querying the most recent rows?
  • Date type conversion issue?
  • Comparing columns from the same table?
  • Remote data sources?
  • Predicates being buried in complexity?
  • Too much in a single query?

About Parameter sniffing



Leave a comment

Your email address will not be published.