blocked process

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

blocked process

Postby xdai » Wed Feb 27, 2013 4:16 pm

Hi,

Frequently, we see the sql fragment on blocked process alert. The blocking process shows this, what does this mean?

thanks


Blocking process:

BusinessObjects Enterprise


Process ID:

132

create procedure sys.sp_stored_procedures
(
@sp_name nvarchar(390) = null, -- Wildcard pattern matching is supported.
@sp_owner nvarchar(384) = null, -- Wildcard pattern matching is supported.
@sp_qualifier sysname = null,
@fUsePattern bit = 1 -- To allow users to explicitly disable all pattern matching.
)
as
declare @full_sp_name nvarchar(775) -- 384 + 1 + 390
declare @sp_id int
declare @use_system bit

if @sp_qualifier is not null
begin
if db_name() <> @sp_qualifier
begin
if @sp_qualifier = ''
begin
-- in this case, we need to return an empty result set
-- because the user has requested a database with an empty name
select @sp_name = ''
select @sp_owner = ''
end
else
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end
end

if @sp_name = '%'
select @sp_name = null
if @sp_owner = '%'
select @sp_owner = null

if @sp_name is not null
begin
if (@sp_owner is null) and (charindex('%', @sp_name) = 0)
begin
if exists (
select *
from
sys.spt_all_procedures pro
where
pro.schema_id = schema_id() and
pro.name = @sp_name
)
begin
select @sp_owner = schema_name()
end
end
end

select @full_sp_name = isnull(quotename(@sp_owner), '') + '.' + isnull(quotename(@sp_name), '')
select @sp_id = object_id(@full_sp_name)

if (@fUsePattern = 1) -- Does the user want it?
begin
if ((isnull(charindex('%', @full_sp_name),0) = 0) and
(isnull(charindex('_', @full_sp_name),0) = 0) and
(@sp_id <> 0))
begin
select @fUsePattern = 0 -- not a single wild char, so go the fast way.
end
xdai
 
Posts: 69
Joined: Wed Dec 31, 2008 11:50 pm

Postby Brian Donahue » Fri Mar 01, 2013 4:57 pm

Hello,

SQL Monitor will tell you that a process has a wait put on it for longer than a specified threshhold. When the blocking condition is over, SQL Monitor will mark the alert as ended.

Blocking could be because of resource problems on the server or an object that is locked.

SQL Monitor is telling you that the BusinessObjects Enterprise process is trying to create a stored procedure. It should also show the blocking process, which should hopefully give a clue what the issue could be.

If something is creating a schema object in a database, it could be that the query is using a transaction isolation level like SERIALIZABLE - I know from experience that SQL Compare does this to prevent schema corruption so possibly the BusinessObjects process is doing something similar.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests