Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Currently I'm facing an issue in a SQL Server retrieval procedure.

Stored procedure returns unexpected result when executed by many user (No. of users > 40) concurrently for the same parameter. For No of users <= 20 it shows expected result. Error rate is 0.07-0.10 % measured using jMeter.

The following SQL statement is expected to return one row. But under heavy load it sometimes returns no rows.

exec RetrievingNode @Keys='one,two,three'

Here is the procedure:

CREATE PROCEDURE [dbo].[RetrievingNode]
    @Keys nvarchar(max)
    as
    Begin

        SET NOCOUNT ON;
        --***************************************
        --***************************************
        -- Turn On or Off updation of LastAccessDateTime 
        -- To Turn on set @TurnOnUpdation=1
        -- To Turn Off set @TurnOnUpdation=0
        declare @TurnOnUpdation as bit 
        set @TurnOnUpdation=1
        --***************************************
        --***************************************

        declare @Variable as table(Value nvarchar(max),KeyedNodes_Id int,KeyNodeValue nvarchar(max),IsParent bit, IsReadOnly bit )-- this table will hold ids against which LastAccessDateTime can be updated.      
        declare @tbl_Keys table(id int identity(1,1),Value nvarchar(max))
        --following will take all the keys into @tbl_Keys
        insert into @tbl_Keys(Value)select Data from Split(@Keys,',')

        DECLARE @count as int
        DECLARE @counter as int
        SET @counter=1
        SELECT @count=COUNT(*) FROM @tbl_Keys
        --Declare @ChildId as int
        declare @ChildNodes table(id int,Value nvarchar(max),ParentNode_id int)
        declare @ParentId as int
        WHILE(@counter<=@count)
        BEGIN
            DECLARE @Key as nvarchar(max)
            SELECT @Key=Value FROM  @tbl_Keys WHERE id=@counter
            if(@counter=1)
            begin
                if not exists(select * from keyednodes(nolock) KN where KN.Value=@Key and KN.ParentNode_Id is null)
                BEGIN
                        ---node does not exists
                        Break;
                END
                declare @ValueExistsForFirstKey as bit
                set     @ValueExistsForFirstKey=0
                insert into @ChildNodes
                select k2.Id,k2.Value,k2.ParentNode_Id from keyednodes(nolock) k1 inner join keyednodes(nolock) k2 on k1.id=k2.ParentNode_id  where K1.value=@Key and k1.ParentNode_Id is null
                if(@count=1)
                BEGIN   


                    IF EXISTS(
                                select GV.Value,GV.KeyedNodes_Id,KN.Value from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV 
                                on KN.Id=GV.KeyedNodes_Id and KN.Value=@Key and KN.ParentNode_Id is null
                            )
                    BEGIN -- If value of node exists
                        SET @ValueExistsForFirstKey=1
                        insert into @Variable  
                        select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV 
                        on KN.Id=GV.KeyedNodes_Id and KN.Value=@Key and KN.ParentNode_Id is null


                    END
                    ELSE IF NOT EXISTS(select * from @ChildNodes)
                    BEGIN
                            insert into @Variable select -1,-1,-1,0,0 --Node exists but No value or children exists
                            GOTO ExitFromProcedure
                    END

                    IF(@counter=@count)
                    BEGIN
                        if exists(select Id,Value,ParentNode_Id from keyednodes(nolock) KN where KN.Value=@Key and KN.ParentNode_Id is null)                        
                        BEGIN
                            if(@ValueExistsForFirstKey=0)and (not exists(select * from @ChildNodes))
                            BEGIN
                                insert into @Variable select -1,-1,-1,0,0 
                                GOTO ExitFromProcedure
                            END

                        END
                    END
                END
            end
            else 
            begin

                declare @KeyIsChild as int
                set @KeyIsChild=0
                if exists(select * from @ChildNodes where Value=@Key)
                begin
                        set @KeyIsChild=1
                End
                ELSE
                BEGIN
                        --Key path does not exist
                        --print 'key path does not exist'
                        GOTO ExitFromProcedure
                ENd

                if not exists(select * from @ChildNodes where Value=@Key)
                BEGIN
                        set @ParentId=0
                END 
                ELSE
                BEGIN
                        select @ParentId=Id from @ChildNodes where Value=@Key
                ENd
                delete @ChildNodes

                if(@KeyIsChild=1)
                begin
                        insert into @ChildNodes
                        select k2.Id,k2.Value,k2.ParentNode_Id from keyednodes(nolock) k1 inner join keyednodes(nolock) k2 on k1.id=k2.ParentNode_id  where k2.ParentNode_Id=@ParentId
                end
            end
            SET @counter=@counter+1

        END


        if exists(select * from @ChildNodes)
        begin
            IF EXISTS
            (
                select GV.Value,CN.id,CN.Value from @ChildNodes CN left outer join GlobalVariables(nolock) GV on CN.Id=GV.KeyedNodes_Id --children
                union all
                select GV.Value,GV.KeyedNodes_Id,KN.Value  from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV 
                on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId--children

            )
            BEGIN

                insert into @Variable  
                select GV.Value,CN.id,CN.Value,0,GV.ReadOnly from @ChildNodes CN left outer join GlobalVariables(nolock) GV on CN.Id=GV.KeyedNodes_Id --children
                union all
                select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly  from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV 
                on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId--children
            END
            ELSE
            BEGIN

                    if(@count <>1)
                    insert into @Variable select -1,-1,-1,0,0

                    if(@count=1) and not exists(select * from @Variable)
                    insert into @Variable select -1,-1,-1,0,0
            END



        end
        else
        Begin
            if (@KeyIsChild=1)
                BEGIN
                    IF EXISTS
                    (
                        select GV.Value,GV.KeyedNodes_Id,KN.Value from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV 
                        on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId
                    )
                    BEGIN
                        ---IF value of the node exists

                        insert into @Variable  
                        select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV 
                        on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId --node
                    END
                    ELSE
                    BEGIN

                            insert into @Variable select -1,-1,-1,0,0 --Node exists but No value or children exists
                    END
                END
        End

        ----
        ExitFromProcedure:
        select KeyedNodes_Id,KeyNodeValue,Value,IsParent,Isnull(IsReadOnly,0)as IsReadOnly  from @Variable order by IsParent desc, KeyNodeValue asc
        --update LastAccessDateTime

        IF(@TurnOnUpdation=1)
        BEGIN
            IF EXISTS(select * from @Variable)
            BEGIN 
                UPDATE GlobalVariables SET LastAccessDateTime=getdate() 
                WHERE KeyedNodes_Id IN (SELECT KeyedNodes_Id FROM @Variable)
            END
        END

    End
Go

CREATE FUNCTION [dbo].[Split]
(
    @RowData nvarchar(max),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(max)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END
Go
share|improve this question
1  
my initial guess would be lock contention, are you getting any deadlocks during the heavy load? –  Andrew Bickerton Feb 7 '12 at 15:25
    
Does the problem persist regardless of whether @TurnOnUpdation is 1 or 0? When running your test, are there any other processes interacting with the database? –  Mark Storey-Smith Feb 7 '12 at 17:31
    
Use of NOLOCK hints can lead to unexpected results: sqlmag.com/article/sql-server/… –  datagod Feb 7 '12 at 18:03
1  
It looks like you've abandoned this question, but for better help, follow these guidelines when getting help with a slow query: brentozar.com/archive/2009/03/getting-help-with-a-slow-query –  Brent Ozar May 16 '12 at 9:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.