October 29, 2018 at 2:56 pm
Hi,
we have been noticing that every time there is SQL Blocking, one of the read SPs times out. This SP is not even called from the Blocking SP. So, we are not sure why this SP is affected. When I look at SQL Sentry tool, it says that this SP is blocked with wait type as "LCK_M_S" and Wait Resource is "primary key of the table that the SP is reading". It is a simple statement where we are getting the waiting records.
Would there be a way to determine why this SP is getting blocked?
Thanks,
Sridhar.
October 29, 2018 at 5:03 pm
Sridhar-137443 - Monday, October 29, 2018 2:56 PMHi,we have been noticing that every time there is SQL Blocking, one of the read SPs times out. This SP is not even called from the Blocking SP. So, we are not sure why this SP is affected. When I look at SQL Sentry tool, it says that this SP is blocked with wait type as "LCK_M_S" and Wait Resource is "primary key of the table that the SP is reading". It is a simple statement where we are getting the waiting records.
Would there be a way to determine why this SP is getting blocked?
Thanks,
Sridhar.
You would want to get the lead blocker. Check your configurations in SQL Sentry:
Performance Analysis Blocking SQL
You can also use sp_WhoIsActive to capture blocking and the lead blocker:
sp_whoisactive: Leader of the Block
Sue
October 30, 2018 at 11:05 am
Hi Sue,
Thank you for the reply. We have Blocking SQL tab as part of SQL Sentry Subscription. I can see that all the queries / SPs that are blocked. My challenge is identifying why they are getting blocked. I usually see 3 or 4 SPs under the lead blocker. But, when I look at the query for the lead blocker, there is no reference to blocked SPs. So, I don't understand why these SPs are getting blocked because of the lead blocker.
Thanks,
Sridhar.
October 30, 2018 at 11:20 am
Sridhar-137443 - Tuesday, October 30, 2018 11:05 AMHi Sue,Thank you for the reply. We have Blocking SQL tab as part of SQL Sentry Subscription. I can see that all the queries / SPs that are blocked. My challenge is identifying why they are getting blocked. I usually see 3 or 4 SPs under the lead blocker. But, when I look at the query for the lead blocker, there is no reference to blocked SPs. So, I don't understand why these SPs are getting blocked because of the lead blocker.
Thanks,
Sridhar.
Lead blockers don't show you who they are blocking. Look at what the lead blockers are doing (they should be the green ones in SQL Sentry) and the activity of the lead blocker and what locks they have, what isolation levels, times to execute, etc. The one being blocked can't get the locks it needs due to the lead blocking holding an compatible lock. So the blocking session has to wait for the blocker to complete. Even reads take locks so you want to understand locks as well. You'll want to make sure you understand blocking pretty well to resolve those issues. Start reading up on it - here are a couple of links to get you started:
INF: Understanding and resolving SQL Server blocking problems
SQL Server Locks and Blocks
A good lock compatibility matrix:
Lock Compatibility
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply