Skip to Content
avatar image
Former Member

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):

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jan 19, 2015 at 01:48 PM

    If I recall correctly... The check sema timeout is called by the dispatcher(s) every check interval, so if you have 2 dispatchers checking every 5 seconds you'll have about 24 checks a minute. Which makes little sense considering the timeout is 300 seconds.

    The updated procedures that you refer to changes the behaviour of the semaphore handling from deleting the rows to just continously updating them with dispatcherId/timestamps. This was part of a patch or SP at some point. This reduces the number of locks used in the semaphor handling procedures and the size of the transactions. I believe there was also added a check to see if there were any expired semaphores first before running the delete, which also helps the system a bit. Not sure if those updated procedures are handed out any longer, but you should check.

    Br,

    Chris

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      @Chris: I did some research and your guess is maybe the right. We do run at all 6 dispatcher on our system (3 per system and load-balanced - so we do have 6 in sum) and a time-to-live of the semaphores of 300 seconds. I calculated all our "check-intervall" settings and came up to a number of 150 checks per minute. As we have 160 checks per minute in average at our db - and while the number can vary and this number is the average - i think this could be the hint to the right direction. I will check our development-system as soon as its back online again and will do some tests. So thanks so far! I will inform you about my researchs (hopefully i will not forget that).