Occasional mysterious time outs on SQL Server db table

Question

Occasional mysterious time outs on SQL Server db table

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 1

Sergiy - Thursday, September 14, 2017 4:52 AM
You mentioned "edit/update spatial data."And then you said "no locks". Sorry, it cannot be true.It simply not possible.UPDATEs always incure locks. Always.Even if you have (NOLOCK) hint in the statement - it's ignored and exclusive locks are applied anyway.What you describe appears to be a deadlocking issue.And, quite possible, due to the way Java tools access the data, you may have deadlocking on schema.I would suggest to look for BEGIN TRANSACTION instructions in Java.If a connection with explicit transaction executed an update statement and failed to COMMIT (lost connection, timed out, etc.) then the locks will be held forever, until all open connections are rolled back due to the server restart.Look for locks, X and Sch-M ones in particular.

Either that or an "illegal/unsupported" form of the UPDATE statement where the update has a join and the target of the update doesn't appear in the FROM clause. I've only seen it a half dozen or so times but the effects are very much like those described.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24699 More actions · Answer 2

I was re-reading the problem and discussing it outside of this thread a little bit, and I'm not sure if the current path that we are following will lead us to a good solution.
I think a better path to follow would be a blocking related path as deadlocks don't usually cause locking on the object for hours (unless the rollback is horrendous) and stale statistics shouldn't cause hours of blocking either.

I think a better place to look is for uncommitted transactions via "DBCC OPENTRAN" AFTER you notice this blocking. That will likely not provide you with much useful information until the problems start kicking in.
The other thing to look at would be blocking. 2 good DMV's for this are:
SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.sql_handle) AS t WHERE blocking_session_id <> 0

SELECT * FROM sys.dm_os_waiting_tasks WHERE blocking_session_id <> 0

The first one will show you the blocker and if you look at the column text, what they were running; while the second will show you what is being blocked by the blocker.

Is there a predictable pattern when things get stuck? That is, does the problem occur every 24 hours after you restart or when a specific user does something or when anyone does a specific thing? Finding a reproducable pattern will help with diagnosing the problem for you as well as then you can reproduce the problem in a test system and it'll be easier to work on a solution without interrupting end users. If the problem is unpredictable, seeing what is causing the blocking (which is my best guess as to the problem and can be identified in those 2 queries I provided)) will be beneficial.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

joshua.james.1979 SSC Veteran Points: 211 More actions · Answer 3

Thanks bmg002. I will definitely be using these queries when it happens again and hopefully we will get to the bottom of it.
I'll make sure to update this discussion when we finally find the cause.

Thanks again.

Josh

joshua.james.1979 SSC Veteran Points: 211 More actions · Answer 4

One thing I'll add is when we did query for blocks there were none showing so I imagine the two queries you show will return no results.

Mr. Brian Gale SSC-Insane Points: 24699 More actions · Answer 5

Did this happen again? If so, did you figure anything out?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 6

During those periods where readers appear to be blocked, have you run sp_who2 to confirm blocked spids?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

joshua.james.1979 SSC Veteran Points: 211 More actions · Answer 7

It hasn't as yet but no doubt will. It could a few weeks away or a few months, but as soon as it does I'll be using the advice from these posts.

Mr. Brian Gale SSC-Insane Points: 24699 More actions · Answer 8

Thanks for the follow up.

I'm curious what the cause of the problem is and am hoping that we can be of some help in resolving it.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.