Tuning quick tips
- Getting the entire record before apply condition. -> Solution: Apply condition and paging before select.
- Using sub-query -> Solution: Turn it into join
- 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