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.

Tools

https://www.sentryone.com/plan-explorer

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.

Tip

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

https://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why

https://stackoverflow.com/questions/40432794/what-are-the-main-differences-between-optionoptimize-for-unknown-and-optionre/40437749#40437749

Leave a comment

Your email address will not be published. Required fields are marked *