March 11, 2022 at 10:12 pm
Hello,
There is an article below that discusses what causes it. https://techcommunity.microsoft.com/t5/sql-server-blog/negative-blocking-session-ids-5-latch-any-task-releasor/ba-p/3061302
Running SQL Managed Instance environment. Is the issue too much I/O pressure? Suggestions for monitoring/preventing.
Thanks,
Daniel
March 12, 2022 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 14, 2022 at 9:36 pm
I was just reviewing the link you provided and it basically tells you what to do, just not in plain terms. Basically, determine what is causing the latch, then determine what can be done to correct it.
Now, that being said, to me the article makes it sound like these are normal operation events and should be safe to ignore as long as they are not causing problems. As for how to PREVENT them, I imagine that would be as easy as trying to prevent locks from happening on an UPDATE - that is impossible. What you can do though is REDUCE the duration they occur. So step 1 is to determine what type of latch it is; that is if it is an I/O based latch, what type of I/O operation is causing the latch to hold for so long?
My biggest take away from the link you provided is "A blocking session id of -5 alone does not indicate a performance problem".
So, rather than trying to remove those latches (which you cannot do), I would be trying to improve the performance of slow running queries that MAY be related to those latches.
More I/O may solve the problem, or it may postpone the problem and it will happen again in the future, or you may be trying to solve a problem that doesn't exist. If these session ID's only exist for fractions of a second, you are likely trying to solve a non-problem. Now, if the latches are happening for seconds, or minutes, or hours, or days, or ..., then you probably want to do something to address them, but what to fix is a tough ask. It COULD be your disk I/O is too slow, or the network may be too slow, or the CPU, or you may not have enough memory and it is paging to disk... but trying to "fix" page latches means you are likely looking at the wrong piece of the puzzle - you should be looking at the slow queries. Are any of them being reported as slow? Can you verify the slowness and replicate it to a test system? If so, tune the query. It may be rewriting the query to be more efficient, it may be updating statistics, it may be more or less indexes, it may be you need more hardware for the workload. Us knowing that you have SOME latches doesn't really tell us how to help you. Heck, that could be a thing that popped up once and held a latch for 0.001 seconds and you just happened to run a query that made it pop up at that time in which case it is not a problem...
The TL;DR version - what problem are you trying to solve and did you read the article you linked where it states "A blocking session id of -5 alone does not indicate a performance problem"? You may be trying to fix something that isn't a problem...
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.
March 14, 2022 at 10:26 pm
Thank you for your response and totally agree with it. Always goes back to improving slow running queries and to decrease the amount of logical reads. I haven't seen -5 before running in SQL MI so was just more curious on why it was occurring. It only lasts for a brief time (second or a few).
The bigger issue that was investigating was around performance and deadlocks. We have some code running under 'Read Uncommitted' Transaction Isolation Level and although it would have dirty reads, it would prevent escalation of locks but I think we are seeing an increase in wait locks which is impacting other code when it runs. And the code that is running longer, because of 'waits', and running as normal 'Read Committed' is then causing some deadlocks.
March 15, 2022 at 3:43 pm
If the code is running longer due to waits, I would review which wait is causing the slowest performance of the query and go from there. If it is something like a cx packet wait, that is USUALLY caused by parallelism, and you can remove that wait by forcing your query to use a MAX DOP of 1. Might not help your query in terms of performance, but it might.
Personally, I have rarely found that waits are the primary culprit in my slow queries. USUALLY it is related to indexes, OR a cross database/cross instance query that is poorly written, or cursors, or a bad configuration, or something wrong on MY code, not specifically due to waits. That being said, I have had a case where I had to give the server more memory to make things work well, but again, that would fall under poor configuration. I inherited my SQL instances and wasn't around at initial config time, but the max memory on all instances was set to 2 TB (default), so that was causing issues. That, and due to licensing restrictions, we had our SSIS, SSRS, and the SQL Instance all on the same physical box. We now have SSIS and SSRS on different boxes, but they are still shared with the SQL instances, so optimal memory configuration is a bit of a crap shoot with a lot of trial and error and it changes as our ETL memory requirements just keep going up (data keeps increasing!).
Now, as for read committed causing deadlocks, that is strange to me. Long running code isn't what causes deadlocks, that is what causes blocking. Deadlocks occur because 2 queries have unresolvable conflicts on an object. Like if query A is trying to update table Z with data from table Y, and thus taking out a shared lock on Y and an exclusive lock on Z, and query B is trying to update Y with data from Z, and thus taking out a shared lock on Z and an exclusive lock on Y. Since both queries have an exclusive lock on an object required by the other query, one of them must be killed in order for the query to complete - thus deadlock. Read Uncommitted removes those shared locks and it will just read from the table with the data that is in there at the time - which can be changing while you are reading hence why read uncommitted is not recommended.
Based on my experience, if you are getting a lot of deadlocks, it MAY make more sense to modify your queries to prevent these types of blocking. One method that you can do (depending on the size of the table/data you need) is to pull your READ ONLY tables into temporary tables or table variables. Uses more space in tempdb unfortunately, but if the tables are not too large, the performance hit on your queries is usually pretty light. The advantage to this is that your shared lock tables are going to then be sitting in temporary objects and you won't have deadlocks due to those tables. Now, depending on the query, this may not be a reasonable option and as you are looking to improve performance, it's not going to make things faster, it will make things slower.
From a strictly performance standpoint, indexes are likely to be your best friend. Now, this may be more indexes (to improve SELECTS) or less indexes (to improve INSERT, UPDATE, and DELETE). Mind you, you MUST be careful with indexes as fixing your ONE query MAY impact others.
It may also be you need to fix your queries. For example, if you have a query with a cursor (or worse - multiple cursors) you are VERY VERY likely to have performance problems. When I say cursors, I am including any loop related syntax as you are then working on a row based operation rather than a set based operation.
EDIT - another thing that can impact performance is bandwidth. If you were on-prem previously and are now in the cloud, you MAY have had a 1 Gbps (or faster) connection to the server, so pulling in millions of rows was fast. BUT since you are in the cloud now, your ISP is possibly slower than 1 Gbps, or your SQL instance may be hosted at a slower speed than 1 Gbps, so you may need to work on providing reduced data sets.
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.
April 20, 2022 at 11:05 am
fyi: Negative Blocking Session Ids
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2022 at 1:47 pm
That's the same link that the Op provided in the original post on this thread. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2022 at 7:32 am
My bad ( I must have overlooked it because it shows as regular text in stead of a link )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply