SQL Server Performance

spid or session id for current processing running a stored procedure

Discussion in 'SQL Server 2005 General DBA Questions' started by WingSzeto, Feb 10, 2009.

  1. WingSzeto Member

    I am trying to limit only one stored procedure can be ran at any given time in SQL server 2005 or 2008. I am thinking if I can track the spid that is running that sp, I should be able to accomplish that. First, I like to know if I can find out what sp is current running and what spid they are using. Secondly, if there is another way to limit one sp running at any given time, please let me know as well.
    wingman
  2. gbd77rc New Member

    Hi
    You can detect which spid is running which stored procedure. My question would be why would you?
    Its like force only one user at time to be able to use SQL.
    Can you give us more information why you need this.
    Regards
    Richard....
  3. WingSzeto Member

    We have a sp that is resource intensive and we won't want anyone to run that sp more than one at any given time. We are controlling this with our web application so if the second user tries to run that sp, the web application will queue it up until the first one is done.
    wingman
  4. Adriaan New Member

    You could use the old trick with a global temp table ...
    --at the start of your sp ...
    DECLARE @oid INT
    SELECT @oid = object_id('tempdb..##tokenforsp')
    IF @oid IS NULL
    create table ##tokenforsp (token int)
    ELSE
    RETURN
    --body of your sp ...
    DROP TABLE ##tokenforsp
    --end of sp
  5. WingSzeto Member

    Is there another way without modifying existing sp? Can I query the dynamic view for it?
  6. satya Moderator

    You are saying its a resource intensive SP then make sure to optimize the query and keepup the statistics on the involved tables to avoid all these other processes that will not make up any better to the existing issue.
  7. WingSzeto Member

    Yes, that sp has been optimized and statistics are updated every day. We are not trying to avoid other processes. We are trying to have some control on one speical sp. We like to detect and queue that speical sp from our web application if there is the same sp is currently running already. Is there a way?
  8. satya Moderator

    Any chance to post the SP here if not a problem with the confidentiality?
    Also have you checked the execution plan for the queries executed in that SP?
    How about the indexes on those tables that are called up in this SP?
  9. WingSzeto Member

    Sorry, it does have some confidential info there and it would take me time to recreate it anmiously. Indexing and execution plan look good as well.
  10. satya Moderator

    Do you happen to see any DEADLOCKS during this SP execution by more than 1 user>
  11. FrankKalis Moderator

    Not sure if I understood you correctly, but when you say you control the sp via some web gui, you also might want to have a look at sp_setapplock in BOL.
  12. WingSzeto Member

    In BOL, there is no such sp_setapplock. Where can I find some documentation about this sp?
    All I want to know is that if there is a way for me to know if one particular stored procedure is currently running in SQL server at the time of interest.
  13. moh_hassan20 New Member

    Application locks (as suggested by Frank) simply lock a part of T-SQL code, and not locking data, so no two concurrent users can run the same code in the same time. There are 3 stored procedure that are used for this:
    1. sp_getapplock : Places a lock on an application resource.
    2. sp_releaseapplock: Releases a lock on an application resource.
    3. APPLOCK_TEST: Returns information about application lock
    you can find an example in BOL:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e43d4917-77f1-45cc-b231-68ba7fee3385.htm

  14. FrankKalis Moderator

    [quote user="WingSzeto"]
    In BOL, there is no such sp_setapplock. Where can I find some documentation about this sp?
    All I want to know is that if there is a way for me to know if one particular stored procedure is currently running in SQL server at the time of interest.
    [/quote]
    You're right.
    This was a typo. I meant to write sp_Getapplock. Sorry!

Share This Page