I have the complicated stored procedure which reads the data from Oracle database and writes into SQL Server tables. The procedure working time was increase more than 3 times. Number of processed records more or less the same.

How to find the weak point? How to identify which part of the procedure creates the problems now?

I am not familiar with DBA tools for performance analysis. I have only SQL Server Management Studio.

  • Welcome to DBA.SE, Kindly include details from stored procedure and include the plan of the same on pastetheplan.com. Only after having more details on issue, someone can help. – Learning_DBAdmin Oct 14 at 10:53
  • I understand that this is general question and there are not clear answer on it but I don't know where should I start from. I will use the answer below and will start from Query Store. – Andrei Sibircevs Oct 16 at 5:54
  • As Learning_DBAdmin mentioned, please include the actual execution plan. You can do this with SQL Server Management Studio by following the steps outlined in this Microsoft doc up to step #4 docs.microsoft.com/en-us/sql/relational-databases/performance/… then right click anywhere in the white space of the execution plan that is displayed and click "Show Execution Plan XML" then select the entire XML, copy it, and paste it into the textbox on the site pastetheplan.com and add the link it creates of your plan to your post. – J.D. Oct 17 at 3:51
  • Don't spend the effort looking into the Query Store yet. That's going to require more effort for you than it's worth at this point. The Actual Execution Plan will give us most of the information we need to solve the issue, most likely. – J.D. Oct 17 at 3:53

There is a relatively new feature in SQL Server called Query Store. If this procedure that is being called originates from a database where Query Store has been turned on, you can use the built in reports, or a custom query to find this stored procedure and then see its performance over time. Query Store provides things like the various query plans involved and comparing these may help you find an answer to the longer run time.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.