.Net SqlClient Data Provider Queries "Stuck

Question

We started to notice that a bunch of client-side exe apps that run simple SQL queries (Insert, delete) are stuck.

When we copy the query and run it manually it completes in seconds, but it seems like all these jobs are stuck. Any ideas why and/or what to do about this?

Answer 1

Attached Screenshot

Answer 2

Hi,

Your screenshot does not show any blocking, it looks normal.

you can also query the sys.dm_os_waiting_tasks (Returns information about the wait queue of tasks that are waiting on some resource).

Answer 3

These does not at all seem to be "stuck". Can you explain what you mean by "stuck"? Is it the same thing as somebody is waiting for a query to finish?

What you posted show a few sleeping connections. this is normal, a client connected and at the moment it isn't executing a query. Just as when you connect with SSMS and don't execute a query.

If you have a problem that you have many many sleeping connections and this doesn't seem normal to you, then talk to the developers if your application. There might be some issue with them not closing the connection when it should be closed. But that is an issue with the client code, and nothing that SQL server can do anything about.

Answer 4

We have some .NET code that writes to a SQL DB. The exe was getting "stuck". When we started to add debug write to file we saw that the code entered the "SQL Query" phase and was stuck there. When subsequently going into Management Studio we see all these queries there doing nothing, including the potential query that was supposed to be executed from the "stuck" workstation.

So I don't think it's that the connection wasn't closed, in as much as the query never ran.

Basically, the code is:
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = query;
myCommand.ExecuteNonQuery();
//Write to log

The code never get's to the //write to log line.

Answer 5

The connections are doing nothing, they are sleeping. At least what I can see from the screenshot you posted. Assuming that those are indeed the ones created by your code. Did you verify that?

I can think of two things:

You never make it to SQL server because you can't find your SQL Server and you have a long connection timeout, hence you consider them "stuck". But since you never make it to SQL Server, you won't see them as sessions in SQL Server. I.e., the sessions you posed in the screenshot are some other sessions.

You make it to SQL Server, and then your query takes a long time. Either because of blocking or because long execution time (inefficient query/large table etc). But, again, that doesn't match the ones you posted in your screenshot, since those are sleeping.

Start by not using Activity Monitor, but instead query the DMVs directly.

sys.dm_exec_sessions will show you all sessions, including those that are sleeping.
sys.dm_exec_requests will show you all currently executing sessions - not including those that are sleeping.

Based on above you are further troubleshoot what is going on. Do you make it to SQL Server? If the query takes along time, you can dig deeper to see if it is blocked, what it is waiting on etc.