Deadlock in sql server 2014

We have a .net application hosted in iis. We have a server where we have enabled iis and installed sql server 2014.

From past few days we are facing an issue where query just hangs up, kind of like a deadlock.

We are facing this issue on regular basis, last time when the query hanged while executing we opened sql server profiler and saw many query executing on suspended state.

Right Clicking and Selecting Details nothing happens, same with Trace Process in sql server profiler .

We even enabled Sql Server Profiler to include Deadlock Graph in Locks but nothing showed when issue occurred.

if you have MS SQL and "something else" on the same server it is important to restrict the memory for SQL server otherwise, by default, it will take "everything" and that's likely to make SQL and the-other-thing thrash.

Assuming that is not the problem:

There is a very useful Stored Procedure script by Adam Machanic called sp_WhoIsActive

That will show you where one session is blocking another

The parameters take a bit of getting-your-head around, so in case helpful here are the ones I use for "Who is blocking who"

EXEC sp_WhoIsActive
	@output_column_list = '[start_time][dd%][session_id][block%][wait_info][status][open_tran_count][login_name][tasks][tran_log%][cpu%][temp%][reads%][writes%][context%][physical%][query_plan][locks][sql_command][sql_text][%]'
	, @sort_order = '[session_id] ASC,[start_time] ASC'

Longshot: We had dire performance once, with severe blocking, and we traced it to a user Defined Function which was recompiling every time it was used (and it was used everywhere :frowning: ) and the re-compile was the thing that was blocking the other users.

Similarly changing Auto Recreate Stats from SYNC to ASYNC might help - if SYNC then anytime SQL triggers an update of Stats all queries needing that Stat sit-and-wait, whereas at ASYNC they just use the existing, now-stale, Stats - which were good enough 10ms ago!! so probably still good enough for one-last-query before the new Stats update has completed.

In the first image - you have a process running a SELECT INTO that is waiting (LCK_M_S) for another process. The other process it is waiting on is SPID 77. The second image is showing a process running a SELECT statement that has a wait type of CXPACKET (and is waiting on its own process - SPID 59).

This is not a case of a deadlock - rather it appears to be an issue with someone running and update/insert/delete statement that is taking a very long time. That process - which would show up in activity monitor with a 1 in the head blocker column - is preventing the other processes from completing.

You need to identify what process is the head blocker when this occurs and figure out how to optimize that code so it doesn't take very long to execute and cause blocking issues.