Blocking due to access_methods_scan_range_generato r and Latch_Ex

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.

September 1st, 2015 7:14am

LATCH_XX  is contention for some non-page structure inside SQL Server so not related to I/O or data at all. These can be hard to figure out and will have to use the DMV sys.dm_os_latch_stats. Please see Paul's Post
Free Windows Admin Tool Kit Click here and download it now
September 1st, 2015 7:54am

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


  • Edited by JRStern 15 hours 16 minutes ago
September 1st, 2015 11:57am

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

Free Windows Admin Tool Kit Click here and download it now
September 1st, 2015 12:07pm

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


  • Edited by JRStern Tuesday, September 01, 2015 3:52 PM
September 1st, 2015 3:51pm

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


  • Edited by JRStern Tuesday, September 01, 2015 3:52 PM
Free Windows Admin Tool Kit Click here and download it now
September 1st, 2015 3:51pm

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

September 2nd, 2015 2:06am

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.

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 2:14am

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.

September 4th, 2015 3:46pm

Dear Daniel sir,

How much should i set the value for cost threshold for parallelism?How to calculate the value?

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 3:50pm

Dear Daniel sir,

How much should i set the value for cost threshold for parallelism?How to calculate the

September 4th, 2015 8:42pm

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


  • Edited by JRStern Friday, September 04, 2015 9:36 PM
Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 9:32pm

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


  • Edited by JRStern Friday, September 04, 2015 9:36 PM
September 4th, 2015 9:32pm

Dear Daniel sir,

Here is the result of the query:

Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 3:57am

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

September 5th, 2015 4:00am

Dear Daniel sir,

Here is the result of the query:

Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 10:33am

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.

September 5th, 2015 10:49am

Dear Daniel sir,

The cost threshold for paralleism value is 5.

I ran the below query:

The result of the query is 16

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 2:10am

Dear Daniel sir,

The cost threshold for paralleism value is 5.

I ran the below query:

The result of the query is 16

September 7th, 2015 11:14am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics