Post reply

Counting the locks held by a procedure

  • February 11, 2009 at 4:16 am

    #71857

    Hi All,

    I want to know the lock count held by a procedure. How should i know it ? i dont want to find it by using any third party tools.

    Say for example,

    create table emp

    (

    eno int,

    ename varchar(30)

    )

    create procedure p1

    as

    insert into emp

    select 1,'AA'

    union

    select 2,'BB'

    union

    union 3,'CC'

    select * from emp

    update emp

    set eno = eno * 10

    select * from emp

    insert into emp

    select 10,'ZZZ'

    select * from emp

    update emp

    set eno = eno * 100

    select * from emp

    end -- end of proc

    in the procedure, we have

    2 - insert statements

    2 - update statements

    4 - select statements

    my question is how to find the lock count ?

    i tried

    1) sp_sysmon, but it is asking 'sa' role to run it. i dont have 'sa' role. i do have only 'dbo' role.

    2) begin trans & end trans

    3) Proc is using 2 cursors, i looked the cursor, those are declared with READ ONLY option.

    i dont know whether i looked the correct things or not.

    some of my friends told me to increse the number of locks parameter.

    is it a good way to add the number of locks parameter? will it affect the system performance ?

    will it occupy more disk space?

    Actually i am new to this issue, i am facing this first time. so i dont know from where i have to start to look at the lock count.

    Inputs are welcome!

    karthik

  • February 11, 2009 at 4:58 am

    #942006

    Procedures don't hold locks.

    Transactions do.

    _____________
    Code for TallyGenerator

  • February 11, 2009 at 5:05 am

    #942009

    karthikeyan (2/11/2009)

    I want to know the lock count held by a procedure.

    Why?

    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

  • February 12, 2009 at 1:53 am

    #942652

    Sergiy,

    Procedures don't hold locks.

    you mean the above statements within the procedure wont hold locks ?

    I think i have phrased my question wrongly, may be if i ask like locks held by the statement which resides inside a procedure thats what i wanted to know.

    Gila,

    why ?

    While running a Data stage job which internally call one SP throwed

    "Ran out of locks" error.

    so DBA asked me to look at the procedure to know the lock count held within that procedure.

    Basically when and why this error message will raise?

    karthik

  • February 12, 2009 at 7:25 am

    #942842

    Any inputs?

    karthik

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

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