Stored Proc Excuting long time

Raj0125 511 Reputation points
2022-09-08T13:38:19.8+00:00

Hi,

I am excutiong below Stored proc in database having nearly 300 tables to delete data based on parameter passed.But its running long hrs.Initially i used cursor same time its taking.so i chnaged to variable instead of cursor still its taking 3 hrs.

The data should delete based on the parameter passed (Approx.deletion row count in Millions)

Code as below:

Please suggest any improvments in terms of performance.

Thanks in Advance.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,102 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 103.8K Reputation points MVP
    2022-09-08T21:31:25.207+00:00

    You could be running into blocking that stalls your script. But it could also be that some table is big and takes a long time to process.

    On my web site, you find beta_lockinfo which is a free stored procedures, which will reveal both blocking and what statement the process is currently running.


0 additional answers

Sort by: Most helpful