How to take care blocking or deadlock issue in SQL Server?

To manage blocking  in sql server , identify the blocking process using activity monitor or dynamic management views , then resolve the issues by optimizing queries, shortening transactions, or killing the blocking process if necessary.

detail breakdown:

Identify blocking processes;

SSMS activity monitor :

Connect to your  sql server instances in ssms.

Right click the instance name and select activity monitor.

Expand the processes section and identify the blocking sessions.

we can also use the blocking processes view within activity monitor.

Dynamic management view ( DMV`s):

use DMVs like 

sys.dm_exec_requests

and

sys.dm_os_wait_stats

to identify blocking sessions and wait types.

DEADLOCK;

Use sql server profiler to identify the cause of a deadlock . A deadlock occurs when there is a cyclic dependency between two or more threads , or processes, for some set of resources within sql server . using sql server profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

A dead lock is broken by aborting and restarting a process, releasing  all resources held by the previous process. When using the dead lock detection and recovery method: there would be no resources access or process execution . when possible, processes are given requested resources.

After a deadlock is detected , the sql server database engine ends a deadlock by choosing one of the threads as a deadlock victim . the sql sever database engine terminates the current batch being executed for the thread, rolls back the transaction of deadlock victim, and returns a 1205 error to the application.

Launch sql server profiler and create a new trace. in the trace properties dialog, select the events selection tab. select the locks category and the lock : time out and lock: deadlock events. run the trace and monitor  the events to detect any lock timeouts or deadlocks.