Stored Proc Excuting long time

Raj0125 511 Reputation points


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 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

    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