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. Could you please help.
Technology Tips and News
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. Could you please help.
1. It's self-blocking, but does it eventually complete?
2. How many cores do you have and what is your maxdop?
3. Have you captured the SQL that is causing this? You may want to examine your data model to see if an additional index or other change, including rewriting the query, might be the real fix.
Josh
This happens when a parallel plan is processed with a range scan. It's simply a synchronization process for all parallel scans. This is not "blocking" in the thought that this session is blocking another query from running; it's just the sync process itself.
Things you may want to look into:
I hope that
1. It's self-blocking, but does it eventually complete?
2. How many cores do you have and what is your maxdop?
3. Have you captured the SQL that is causing this? You may want to examine your data model to see if an additional index or other change, including rewriting the query, might be the real fix.
Josh
1. It's self-blocking, but does it eventually complete?
2. How many cores do you have and what is your maxdop?
3. Have you captured the SQL that is causing this? You may want to examine your data model to see if an additional index or other change, including rewriting the query, might be the real fix.
Josh
Dear Jinu,
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
Dear JRStern sir,
1. It eventually completes, but takes time
2. Total number of cores is 8 and Number Of Cores In Each CPU is 4. MAXDOP is 0.
3. They are stored procedures.
Dear JRStern sir,
1. It eventually completes, but takes more time
2. Total number of cores is 8 and Number Of Cores In Each CPU is 4. MAXDOP is 0.
3. They are stored procedures.
Dear Daniel sir,
How much should i set the value for cost threshold for parallelism?How to calculate the value?
Dear Daniel sir,
How much should i set the value for cost threshold for parallelism?How to calculate the
1. It eventually completes, but takes more time
2. Total number of cores is 8 and Number Of Cores In Each CPU is 4. MAXDOP is 0.
3. They are stored procedures.
As Dinu and Daniel point out, some of this self-blocking pretty much always occurs for parallel plans, so just seeing a little bit on a SPID display is not an immediate cause for concern. You need to start looking at wait statistics and see what the average and total times are for these waits. And it is best if you can focus down the inspection not just to the stored procedure level but to the individual statement level.
Eight cores is not that many, these issues can get much more serious as the number of cores gets higher. Depending on your total server workload, maybe things are just about perfect right now, one of the reasons to have lots of cores is to use them just when you need them, and parallel workloads tend to need them all for a short time, then have to wait for the last one to finish, and that's just how it goes.
You say it eventually completes, so that's good, but you say it "takes more time". More time than what? If you really want to see it take more time, change your settings to maxdop=1 and see how long the same SPs take to run (actually, you might try maxdop=4 or other small values as well). Then you may appreciate the magic of parallel plans and why it is just fine if that leaves some cores blocked for a few milliseconds now and then.
But if you explore this and there is a *lot* of waiting, then you need to start looking at the data model, indexing, and other issues. You can try different settings for the threshold for parallelism (instead of maxdop settings). And sometimes really, the best thing is just to set up your monitoring with profiler, event monitor, perfmon, wait stats, etc, and try different values. Then you just choose the one that works the best.
Josh
1. It eventually completes, but takes more time
2. Total number of cores is 8 and Number Of Cores In Each CPU is 4. MAXDOP is 0.
3. They are stored procedures.
As Dinu and Daniel point out, some of this self-blocking pretty much always occurs for parallel plans, so just seeing a little bit on a SPID display is not an immediate cause for concern. You need to start looking at wait statistics and see what the average and total times are for these waits. And it is best if you can focus down the inspection not just to the stored procedure level but to the individual statement level.
Eight cores is not that many, these issues can get much more serious as the number of cores gets higher. Depending on your total server workload, maybe things are just about perfect right now, one of the reasons to have lots of cores is to use them just when you need them, and parallel workloads tend to need them all for a short time, then have to wait for the last one to finish, and that's just how it goes.
You say it eventually completes, so that's good, but you say it "takes more time". More time than what? If you really want to see it take more time, change your settings to maxdop=1 and see how long the same SPs take to run (actually, you might try maxdop=4 or other small values as well). Then you may appreciate the magic of parallel plans and why it is just fine if that leaves some cores blocked for a few milliseconds now and then.
But if you explore this and there is a *lot* of waiting, then you need to start looking at the data model, indexing, and other issues. You can try different settings for the threshold for parallelism (instead of maxdop settings). And sometimes really, the best thing is just to set up your monitoring with profiler, event monitor, perfmon, wait stats, etc, and try different values. Then you just choose the one that works the best.
Josh
Dear Daniel sir,
Here is the result of the query:
Dear Josh sir,
Thanks for the wonderful explanation. The world is blessed with good people like you helping beginners like me in the forums. Thanks sir
Dear Daniel sir,
Here is the result of the query:
Dear Daniel sir,
Hats off for the crystal clear explanation. I feel very happy seeing experts like you helping beginners like me. The world is truly blessed with great people like you. I will run the queries and tell you the result.
Dear Daniel sir,
The cost threshold for paralleism value is 5.
I ran the below query:
The result of the query is 16
Dear Daniel sir,
The cost threshold for paralleism value is 5.
I ran the below query:
The result of the query is 16