1

We have an INSERT statement coming from an Entity Framework application that has been causing pretty large blocking chains. The Wait Time and Wait Type in our monitoring tools for that process is empty, so it doesn't look like it's waiting on anything. This makes me believe that the application is opening a transaction, doing an insert and just not committing it.

This has been an intermittent issue so it's been hard to troubleshoot. Development team is looking into the code but insistent that nothing has changed. What else can I look at on the SQL Server side?

SQL Server 2012 SP2

  • I would suggest you use the blocked process report or use sp_whoisactive .. invoke it when blocking happens and capture the output to a table. Also look at sqlskills.com/help/waits/lck_m_u – Kin Shah Sep 20 '16 at 18:53
  • Brent O had an article on this, specifically with EF and it's, sometimes stupid, handling of connections: brentozar.com/archive/2015/07/… This may be applicable to you, it may not, but I've seen EF commit some borderline atrocities with connections and not getting out of SQL server gracefully. – Kris Gruttemeyer Sep 20 '16 at 19:11
  • Yeah, I am heading down this road. Unfortunately it is an intermittent problem and of course it's not happening right now. – cjsommer Sep 20 '16 at 19:39
1

Do you know exactly what the INSERT statement is and what line of EF code generates it? Just wrap whatever calls that around some logging code and log all the SQL to a text file (preferably using log4net, nlog, ETW etc.

Just wrap the code that calls the insert code with a call to set and unset the delegate described here.

e.g.

 ctx.Database.Log = (dbLog => log.Debug(dbLog));
// Do the insert
ctx.Database.Log = null;

Honestly, if you can deal with a slight slowdown in performance, and have the disk space just log everything. I log all the SQL in my Always Encrypted Sample app.

Assuming your INSERTS are wrapped around transactions, you should be able to see that instantly with this. Make sure your logging has timestamps so you can see how long the transaction remains open for.

|improve this answer|||||
  • They don't have a lot of logging around the process and it is a heavily used statement. I will make the suggestion to DEV. Thanks Justin! – cjsommer Sep 20 '16 at 19:29
0

From a SQL perspective I would look at running Profiler/Extended Event capture. I've been investigating a few of these types of issue recently and they can be a pain to troubleshoot. I've found Profiler/Extended Events helpful in these situations. This can give you the whole history of the connection. I suspect the EF is using implicit transaction which requires the application commit (or rollback) the transaction to release the locks. In one case the application had to perform other work outside of SQL before the commit was issued. To resolve this we removed the outside work outside of the SQL transaction.

Another one I found recently is where the application was doing repeated inserts in a single transaction. This was difficult to investigate using blocking scripts as the blocking always showed the same query, even though the application was repeated running the same query. It was only with profiler that I was able to identify the repeated queries.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.