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.
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.
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