12 Steps for Tuning SQL Server query performance
This is a quick blog
[read: steps] on how to tune the SQL Server query performance. Assuming you have the knowledge of the tech jargons associated with SQL Server and related things, let’s begin with the first things first.
12.Engineer out the stupid
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
- Sub-queries
- 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
