April 21, 2017 at 3:45 pm
I am running SQL 2014 Ent on Windows 2012 Ent. Each VM is attached to Nimble storage with 16 cores and 64gb of ram. I am currently running AlwaysOn with 275 DBs in the AG. We have recently started noticing an issue with occasional blocking on a few DBs. The head blocker is a single record updates to a single DB which in turn is causing locking across the entire server. All 275 clients are no longer able to log into the application. When I review this in Activity Monitor I am seeing no wait type for the head blocker and cant for the life of me figure out why the lock is spanning multiple DBs.
Hardware stats seem to be in order CPU ranges in the 50-60% range. I had to kick the available threads up to 4k as it was reaching the 500 limit assigned by SQL and causing issues. PLE is 4600+ and IO is decent.
I have since removed one of the main DBs experiencing these issues from the AG to see if it had anything to do with AO and it is still happening.
Any help in troubleshooting this issue would be greatly appreciated. I will be applying the latest Sp and Cu for SQl this weekend. Hope that resolves it but if not really need a plan B for investigating.
Thanks for the help.
Jeremy
April 22, 2017 at 12:53 am
Recently I also faced issue like your's. May be there is some activity on server which is freezing Disk I/O for sometime and can break connection to application.
check log for error- I/O is frozen on Database
In my case it was third party backup tool which was freezing I/O during db backups and breaking connections to application.
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.
April 22, 2017 at 6:39 am
Quick question, can you elaborate on the backups, frequency, type etc.?
😎
April 22, 2017 at 10:26 am
drgn38 - Friday, April 21, 2017 3:45 PMI am running SQL 2014 Ent on Windows 2012 Ent. Each VM is attached to Nimble storage with 16 cores and 64gb of ram. I am currently running AlwaysOn with 275 DBs in the AG. We have recently started noticing an issue with occasional blocking on a few DBs. The head blocker is a single record updates to a single DB which in turn is causing locking across the entire server. All 275 clients are no longer able to log into the application. When I review this in Activity Monitor I am seeing no wait type for the head blocker and cant for the life of me figure out why the lock is spanning multiple DBs.Hardware stats seem to be in order CPU ranges in the 50-60% range. I had to kick the available threads up to 4k as it was reaching the 500 limit assigned by SQL and causing issues. PLE is 4600+ and IO is decent.
I have since removed one of the main DBs experiencing these issues from the AG to see if it had anything to do with AO and it is still happening.
Any help in troubleshooting this issue would be greatly appreciated. I will be applying the latest Sp and Cu for SQl this weekend. Hope that resolves it but if not really need a plan B for investigating.
Thanks for the help.
Jeremy
1) If you are having a critical issue with this (and it seems like you are) you REALLY should get a consultant on board ASAP. You can flop-and-twitch on a forum for WEEKS (I've seen it) without a resolution when a knowledgeable consultant could find and resolve the problem in hours or even minutes sometimes.
2) What is the query that is blocking? And any patterns in the ones being blocked?
3) What locks is it taking? That will be the thing that tells, assuming locking is the cause of your server locking up. Also what are the requests from the ones that are blocked?
4) How many AG databases? Have you monitored that subsystem for performance issues?
5) Did you do file IO stall and wait stats differential analyses? Mandatory.
6) Also mandatory is using sp_whoisactive to determine what is going on in real time. It can also show a wide variety of things - review the 30-day blog post series Adam Machanic did on it over on SQLBlog.com
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 28, 2017 at 11:07 am
Hey Guys,
Thanks for the responses. I have been out ill with the Flu all week so have not had a chance to look at all of your replies. I will take a look this weekend and follow up.
Thank you,
Jeremy
April 28, 2017 at 4:45 pm
Thank you for all your responses.
I have just checked for the error (I/O is frozen on Database) in the logs and did not see this at all. I was not able to find anything that corresponds to the blocking time frame in either SQL or Windows Logs.
We are currently using Veeam for backups running fulls once a week, diffs nightly, and logs hourly. There is no correpandance on time there either unless we are talking VM snapshots which I will need to reach out to my IT staff for. Not seeing anything in the logs indicating high IO times.
The query is a single record delete and all queries being blocked are for this same table. The head blocker has no wait type but hangs indefinitely until manually killed. Query (delete from table where record_id = @theSourceID). I do not see other DBs being blocked but I do see some coming in on Master due to the linked server/synonym calls which are being blocked. These appear to be being blocked by themselves but the number grows exponentially when this blocking starts to occur. Some do reference the same DB.Table and others do not. I am starting to think that its all the linked server/synonym stuff that is what causing the multi db tension issues.
Since we are a MS partner we have hours to burn. Currently we are getting paged if the blocking last longer than a minute so I thought I would put in my due diligence before reaching out. But admittedly I am reaching the end of my skill set. I would like more to learn how to identify and resolve these more than I would like to be told here is the fix. Troubleshooting linked server and synonym calls has been a pretty steep learning curve much less the blocking issues we are currently experiencing.
Thanks for any more advice you may have for me.
Jeremy
April 28, 2017 at 6:20 pm
If Microsoft can't help (and I have been brought behind them a number of times over the years), there are several good consultants on SSC.com you can engage to help you out.
Good luck with it!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply