Question :
This is one thing I have been trying to read from few days. I am not getting any satisfactory explanation anywhere. Tried surfing some blogs and videos in youtube.
Appreciate if anyone one can throw some light on this.
-
What are the query optimization techniques in SQL Server or how to optimize a SQL query in SQL Server?
-
If we have a query which is 2-3 pages long; what are the first steps to take for optimizing the query?
Faced this in one of the interviews and couldn’t give a proper answer other than indexing.
Answer :
Indexing is always the first port of call. Ensure that you have the proper indexes in place before anything else.
Next run the query and study the actual execution plan which will give some much needed pointers.
http://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx
Finally these tricks can give a well needed performance boost and most if not all are good practice anyway:
- Ensure All tables have a primary Key.
- Ensure All tables have a relevant clustered Index.
- Ensure All Tables have the appropriate non clustered index. Specifically all columns referenced in the Where / Order By and Join clauses should be covered.
- Avoid Views where possible.
- Do not use SELECT *
- Ensure all of your query is relevant especially where it joins to other tables.
- Avoid Cursors unless absolutely necessary.