How to speed up SQL stored-procedures performance?

you can use (set nocount on ) in the store procedures .. it will help a lot to increase your speed

and also ... put the min. memory per query in the sitting.

Try below steps

Check the execution plan , validate the cost of Execution plan , if any of the Process costing more , try to re-right the stored procedure , or wright simple code with same business logic and try to reduce the cost of that certain process.

1) Use SET NOCOUNT ON inside ALL your Stored Procedures

2)Don’t Name any of Your Stored Procedures with ‘sp_’ at the start

3)Adjust the Index Fillfactor setting

4) Adjust the Min Memory Per Query setting

Configure Index Fillfactor setting

Configure Memory Per Query setting

Usage of SET NOCOUNT ON on your Stored Procedures

Exclude the naming of stored procedures with ‘sp_’ at the beginning

1. Lower your number of joins.

2. Only use most relevant joins 

3. Check Execution Plan it will describe everything to you. 

Still getting issue please share your query i will let you know.

Thanks and regards

Use SET NOCOUNT ON inside ALL your Stored Procedures

Don’t Name any of Your Stored Procedures with ‘sp_’ at the start

When joining tables using inner joins, you always want to join the smallest table first.

With a large procedure, it may be likely you also have the same query being executed multiple times. If this is the case, then instead of performing the same query multiple times, dump the results into a temp table and reference the temp table in the rest of your queries.

Limit the Select list

Returning too many columns can have a drastic effect on your query. Not only will it increase you chances for bookmark lookups (or key lookups), but the network and disk latency add to the query. Not to mention you will be squeezing more data into your buffer cache.

Use the Least Amount of Tables to Compile Your Select List

An example would be let’s say you need to join on 2 tables in order to get your result set. If one of the tables contains all the fields needed for the select list, but you are also able to get the same field(s) from the other table, always go with only returning the values from the one table. Doing so will limit the number of IO operations necessary to give you your result.

You must optimize the stored procedure by reducing the individual query execution time. Try to replace all in, not in clauses by joins. Remove loops from the Stored procedure and replace them with functions and use goto statements to call those functions. Try to eliminate all udf's from the stored procedure and instead do manual query writing as possible.

creating a stored procedure to run in a database other than the Master database, don’t use the prefix “sp_” in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly

If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is “dbo”. Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don’t name any of your stored procedures with the prefix “sp_”

You can start by adding SET NOCOUNT ON; This should speed it up quite a bit.

Use SET NOCOUNT ON inside ALL your Stored Procedures

• Use SET NOCOUNT ON inside ALL your Stored Procedures
• Adjust the Min Memory Per Query setting
• Adjust the Index Fillfactor setting

First we need to check the estimated exedcution plan.