September 1, 2015 at 5:11 am
Good morning Everyone,
Blocking is happening due to access_methods_scan_range_generator and Latch_Ex. I see several multi-threaded sessions blocking itself. I looked into it but unable to find solution. I have attached the screenshot.Could you please help.
September 1, 2015 at 5:12 am
It's normal for sessions in parallel to 'block' each other. Your wait times are all around 15-30 ms. Why is this a problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2015 at 11:38 pm
Good morning Gila Shaw Madam,
Actually, when the app team reports slowness in fetching data from application, we see Blocking due to access_methods_scan_range_generator and Latch_Ex. This may be co-incidence or may not be the reason, but after we kill the self blocking sessions, application team is able to fetch data fast and they set the status of the issue as resolved. This happened 3-4 times. Hence, application team is under impression that when this kind of issue happens, it is due to self blocking(access_methods_scan_range_generator and Latch_Ex) and they ask us to kill the self blocking session. Please advise
September 2, 2015 at 2:52 am
I'm going to give you my usual answer.
Analyse, investigate and identify exactly what's happening. Don't guess, you're just going to waste time. Don't try stuff at random, it doesn't work. Don't look at one thing in isolation, it's seldom one thing.
Identify exactly what runs when the app team requests their data, get exact performance metrics for each step, then and only then can you identify exactly what is the problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2015 at 7:07 am
Good morning Gail Madam and everyone,
Actually each SPID is blocking itself, and there are number of SPIDs like that.
September 7, 2015 at 7:24 am
GilaMonster (9/1/2015)
It's normal for sessions in parallel to 'block' each other.
Now, maybe they shouldn't be running in parallel, maybe there are missing indexes, maybe there's badly written code. Either could cause queries to run in parallel and less efficiently than they should.
Stop looking at individual symptoms. Investigate, analyse and identify the causes of whatever problem you have, then you'll be in a good position to suggest and implement a fix for whatever problems you're having.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply