The performance of any application largely depends upon the backend process which extracts data. If it is faster then the application will be also responsive. The designing of the stored procedure is very critical and need to be careful to remove any  We can consider the following points while designing stored procedure :

1. Variables: We should minimize the use of variables as it is stored in the cache.
2. Dynamic queries: We should minimize the use of dynamic query as dynamic query gets recompiled every time parameter gets changed.
3. The stored procedure should be always called with fully qualified names database_name.schema_name.sp_name , this AVOIDS the search of an execution plan in the procedure cache.
4. We should always use SET NOCOUNT ON which suppresses rows affected to improve the performance of the query. This is very critical in case SP is called frequently as not USING the above syntax can load the network.
5. We should not use sp_ in stored procedure name as it is used for system procedure so it causes an extra trip to the master database to see if any system procedure matches with the user-defined procedure
6. We can use sp_executeSQL or KEEPFIXED PLAN to get away with recompilation of stored procedures even if we have parameterized dynamic queries.

1. We should be careful in choosing WHERE condition as it triggers index seek. Sometimes we might END up doing full table SCANS if were conditions not CHOSEN carefully.

1. We should avoid using IN operators as it checks for NULL values as well. We should use EXISTS as it does not consider NULL values. The below query second will give faster results as compared to the first one.

1. If not necessary we should avoid using DISTINCT and ORDER BY clause as it is an additional load on the database engine.

1. We should avoid CURSORS. Instead of CURSORS, we should use temp tables /table variables inside the loop to achieve the desired result set.