Performance tuning Part 1

 
Hi Friends,
 
Some of the Performance tuning tips (good for Developers also).
 
  1. Substring function: SARG cannot be produced and an index cannot be used (ignores cluster index scan)
  2. Queries: what is good today may not be good tomorrow.
  3. Operate a small result sets, limit that number of column on select list
  4. Use highly selective where clause.
  5. Using index effectively as we did some query on the feature already.
  6. Important is to avoid non index able search condition
  7. Index able where clause: >, <, >=, between, %
  8. Non index able where clause: !=, !>,!<, not exist, not in , not like, or, like, %%
  9. Avoid arithmetic operator
  10. Substring vs. like (like would be good)
  11. Avoid optimizer hints
  12. Avoid implicit conversation.
  13. Union vs. union all (union all would be good)
  14. Sub query vs. joins (sub query would be good)
  15. Store procedure should have: set nocount on… set nocount off
  16. Don’t use function in where clause

reference:

http://www.sql-server-performance.com

https://microsoft.com/sql

others…

 

 

 

 

 

Advertisements
This entry was posted in Performance Tuning and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s