Slow performing Stored Procedure

Question

Hello,

I have a stored procedure which is running very very slow.

It fetches data from a very large table of about 10 million rows.

Issue is the SP used to take less time weeks before but now the execution time has increased to 4 fold.

Any suggestion how to improve the performance of this?

Thanks,
Mohan

Answer 1

Good day,

It fetches data from a very large table of about 10 million rows.

10 million rows is far from being "very large table" for SQL Server

I have a stored procedure which is running very very slow.

Unfortunately we cannot read minds or connect your server and saying that you have an issue does not provide any information about the issue.

If you have stored procedure or any other entity which is relevant to the question, then we need to see it or better to reproduce it i our server for testing and discussion about improvement.

Issue is the SP used to take less time weeks before but now the execution time has increased to 4 fold.

Your database changes over time and probably the data as well. It is perfectly normal for example that if you have more data and poor code and/or poor design then over time your system will work worse when you will have more data

Any suggestion how to improve the performance of this?

Yes, start with reading the stored procedure and if you need our help then we have the same need

Please provide (1) the full code of the stored procedure, (2) DDL+DML for any relevant table including indexes, constraints, triggers, relations with other tables and any entity which might impact performance, (3) the actual Execution Plan in your server when you execute the SP - we need the XML full execution plan and not only the graphical image.

Note: if you do not know how to provide one or more of the information then feel free to ask. We are here to help you but we do need the tools and the information which only you can provide

Answer 2

As Ronen says, with the amount of information you have provided, we cannot help you much.

I like to point out that ff you are on SQL 2016 or later, and you have Query Store active for the database, you are in a lot better position, as you can find old query plans for the query, and maybe even force them.

Else, well, as Ronen says, start with posting the store procedure so we know what you are working with. Then we will ask for more information once we have seen the procedure.

Answer 3

Hi @MOHAN CHHAPADIA ，

There are many factors to speeding up stored procedures, simple changes can be made to the code to optimize.
This document can give you some tips:
https://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/