Strange Blocking

Question

Strange Blocking

VastSQL

SSC-Dedicated

Points: 36097
More actions
September 21, 2016 at 11:51 pm

#400762

Hi Experts,
We have a monitorblocking setup in place and which gave below two process blocking . The first one was blocking the second one.
The strange part is there is no relation between these tables.
1.select top 200000 * from Vault order by 1 desc
2.CREATE Procedure [pcs].[sp_GetSystemConfig]
(
@ConfigKey nvarchar(1000)=null
)
AS
DECLARE @Result nvarchar(1000)
SELECT
@Result = ConfigValue
FROM
pcs.SystemConfig
WHERE
ConfigKey = @ConfigKey
SELECT @Result ConfigValue

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

What was the ACTUAL blocked resource? Are you absolutely certain it was blocking and not latch contention or something else?

I like sp_whoisactive to see lots of details about such things. If it is off-hours you could set up the blocked process monitor, although be careful because it can be a bit heavy with low-duration watching and/or capture a lot of events.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

TheSQLGuru (9/22/2016)
What was the ACTUAL blocked resource? Are you absolutely certain it was blocking and not latch contention or something else?
I like sp_whoisactive to see lots of details about such things. If it is off-hours you could set up the blocked process monitor, although be careful because it can be a bit heavy with low-duration watching and/or capture a lot of events.

Thanks Kevin,

Got this from our Blocking Monitor.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You need to check the blocked resource, there's no way to tell from what's posted what happened. There could be an explicit transaction and locks taken by earlier statements, it could be latches, it could be anything.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

It's likely that either or both of those queries are part of a larger batch that has other parts that are still holding resources. However, as Gail says, no way to tell from what you've supplied.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning