I have a batch process that runs every night. Once the process completes, I see that there are some records which has not processed completely. I think I might be encountering a locking scenario while running multiple updates on a single table.
What is the best way to troubleshoot locking in the database using DMVs? Is there any particular DMV, that I can run in the morning(after the batch has completed) - which will provide me information on the locked processes encountered by the SQL engine,during the nightly run.
Contains information about processes that are running on an instance of SQL Server.
The below command will gets you the blocked processes
select * from sys.sysprocesses where blocked > 0