Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Semaphores on SQL-database: stored procedure mc_chk_sema_timeout

Hi there!

We do run this environment:

Microsoft Server 2008 R2

Microsoft SQL server /2008 R2 / driver 10.50.4000

Identity-Management 7.2, SP6 Patch 1

We`re discovered a (strange?) behavior in our SQL-database cache.

As we look to the top most executions per minute,the IdM stored procedure "mx_chk_sema_timeout" gets the highscore (is the number one procedure that hast most cpu, time, reads, and so on)... It is executed 161 (in average) per minute.

This is executed this often:

USE [db]
GO

/****** Object:  StoredProcedure [dbo].[mc_chk_sema_timeout]    Script Date: 13.01.2015 13:37:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[mc_chk_sema_timeout]
       @Ptimeout int,
       @Status as int OUTPUT,
       @Statustext as varchar(256) OUTPUT
AS
       Declare @Lrowcount int
       Declare @Lbuf varchar(256)

       -- LOCK
       EXEC sp_getapplock 'mc_chk_sema_timeout','Exclusive','session'

       delete from mc_semaphore where (DATEADD(s, @Ptimeout, Sematime)<getdate());
       select @Status = @@ERROR, @Lrowcount = @@ROWCOUNT
      

       -- UNLOCK
       EXEC sp_releaseapplock 'mc_chk_sema_timeout','session'
      
       IF @Status = 0
       BEGIN
             set @Statustext = 'OK'
             IF @Lrowcount > 0
             BEGIN
                    set @Lbuf = convert(varchar,@Lrowcount) + ' stale semaphores released'
                    execute mc_write_syslog @Lbuf,'',1,'',0
             END
       END
       ELSE
       BEGIN
             set @Statustext = 'Unable to release staled semaphore';
       END


GO

My questions:

1. Is this maybe a normal behavior (seen on DB time and execution) and we should not be worried?

If not, what can we do to lower the executions per minute?

I did some searchs:

The table mc_semaphore shows no special behavior. Looks good for me: Some (1-6 semaphore go in, 1-6 leave immediately). I had expected, that this table would maybe be overflowed. But it wasnt...

I also  read this SCN entry of Per Krabsetsve (at the end of the entry): Dispatchers Deadlock Issue

He talks (in case of a about contacting the SAP-Support in order to get a different (altered?)  procedure of "mc_chk_sema_timeout".

2. Maybe this would be a solution?

3. Are there any further suggestions/experiences you can give us?

Thank you for your replies!

Michael

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question