One of SP is having below code and it is running more than 2 hrs. This job is daily schedule.
Table_B is truncate and load from souce systems on daily basis.
Number of rows in Table b is morethan 33000000.
On TableA 3 non-clustered indexes are already created.
UPDATE STATISTICS dbo.Table_A
UPDATE STATISTICS dbo.Table_B
Insert into Table_A
Select columns... from Table_B B1
LEFT JOIN Table_A A1 (NOLOCK)
ON B1.[Column1] = A1.[Column1]
AND A1.[SDate] IS NULL
WHERE A1.[Column1] IS NULL
I am not sure where I need to debug this issue. While running sp I ran the Profiler on database and ran the DB Tunning Advisor also. But I got only one recommandation on one column on Table A. This column is already tide with non-clustered index . Can any one please advice..