This is a quick blog
1. Check for Tables+Row counts
- Make sure you are working with tables (not views)
- Make certain you know the row count
- Examine WHERE and JOIN clauses — what is the filtered row count?
- 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
- 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
- SARG-able (make an index searchable so a full
scan isn’t needed)
- 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)
- 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
- Focus on Logical I/O (number of logical reads)
- Focus on most expensive operations first.
- Record the results and compare
- Make small changes as needed
- 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
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