@Chamu, as Mr.Sheonarayan said "There is not sure shot answer of this as stored procedure is not an entity on its own, it contains SQL statement that runs when you call the stored procedure". Which is completely true. However, you can follow the below steps to improve the performance of your query in addition to was what Mr.Sheonarayan has already mentioned. -
a) Never name the user defined store procedures with "sp_". They are system defined and mostly resides under the master db. So if we write a user defined stored procedure by the name "sp_" the query engine will first search the sp indside the master db and if not found then it will search in the current session db. This brings unnecessary round trip. Better to use some other naming convention as "usp_".
b) Avoid * in SELECT statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.
c) Use EXISTS instead of IN clause since EXISTS is faster than IN Clause
d) Avoid using user defined functions. Instead use Apply (Outer/Cross) clauses
e) Try to maintain as small clustered index as much as possible since the fields used in clustered index may also used in nonclustered index.Data in the database is also stored in the order of clustered index.So, maintaining a huge clustered index on a table with a large number of rows increases the size drastically. You may be interested in reading this article "Effective Clustered Indexes" ( https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/ )
f) Avoid cursor since they are very slow in performance. Instead try to write SET based TSQL queries and instead of a Row by Row approach, the Set based approach submits the batch at a time to the query engine there by boosts the query performance.
g) Try to use less number of Temp tables.Instead use Common table expressions.Though it depends.
h) Never write a recursive Stored procedure. Instead use a Recursive Common Table Expression.
i) Try to avoid writing co-related subquery since for every outer row, the inner row gets evaluated.
j) Use TRY-CATCH for handling errors in T-SQL statements.Or if you are using SQL Server Version 2012 onwards, you can use the Throw statement, which is even better.
k) If possible perform a filter by using a where clause and then perform JOIN and not the reverse.
m) Instead of writing Select col1,col2 from TableName write Select col1,col2 from TableName with NOLOCK as the later solves Share lock issue. However, please don't use it for DML statements like (INSERT/UPDATE/DELETE/MERGE).
n) Use UNION ALL in place of UNION. UNION sort the result set for distinguished values but not UNION ALL hence it is faster.
o) Use schema naming convention as it helps the SQL Server for finding that object in a specific schema.
p) Choose appropriate FILL factors
q) If you need to perform a huge join, avaoid it. Create views and apply the Projection on that.
r) Use cluetered index while designing the DB design and that won't yield HEAP tables.
s) Try to avoid NCHAR/NVARCHAR and use CHAR/VARCHAR as the former will unnecessary cosumes 2 bytes more as compared to the later since the former is meant for unicode characters storage.
t) Avoid using VARCHAR(MAX) because we cannot apply indexing on that.
u) Try to keep small transactions since it locks the processing tables and may sometime also cause deadlocks.
v) Avoid using Loop(While). The reason is mentioned in point (f)
w) Instead of creating several non-clustered index, try using Covering Index because it can satisfy all requested columns in a query without performing a further lookup into the clustered index.If all the columns requested in the select list of query, are available in the index, then the query engine doesn't have to lookup the table again which can significantly increase the performance of the query. Since all the requested columns are available with in the index, the index is covering the query. It enhances the query performance by avoiding unnecessary lookups. (Read More: https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/ )
x) Avaoid distinct clause for limiting rows.Better use the Ranking functions like ROW_NUMBER ( http://www.dotnetfunda.com/articles/show/1925/10-reasons-for-which-we-should-learn-and-use-rownumber-function-of-sql ).
y) For pagination, you can use Fetch/Offset ( http://beyondrelational.com/modules/2/blogs/80/posts/10632/offset-and-fetch-firstnext-clause-extension-to-order-by-clause-in-sql-11code-name-denali-sql-server-.aspx) instead of ROW_NUMBER. The Fetch/Offset is a better choice as mentioned in the article link provided.
z) Use judiciously Force Index Query Hints ( http://blog.sqlauthority.com/2009/02/07/sql-server-introduction-to-force-index-query-hints-index-hint/ )
These are only small tips for writing better queries and not the sure short guide but yes, this way of writing the Queries/SP's will definitely improve the query performance. I will make another post to highlight some more effective points.
Chamu, if this helps please login to Mark As Answer. | Alert Moderator