SQL Server: Insert statement gets stuck some time

Question

I have a store procedure which insert or update data into table. some time when we call that SP from .net winform application then application spinning and at the end timeout error occured.

here is my procedure details

CREATE PROC USP_SaveLastCSMData            
(            
 @Ticker VARCHAR(10),            
 @ClientCode VARCHAR(10),            
 @EarningTickerID INT,            
 @AllDateMapName VARCHAR(100),            
 @Earnings VARCHAR(10),            
 @PrePost VARCHAR(10),            
 @QCViewAllContent xml,            
 @QCCommentsContent xml,          
 @IsFinalCSM CHAR(1)='N',        
 @UserID VARCHAR(10)=''          
)            
AS            
BEGIN            
 DECLARE @Status CHAR(1)            
 DECLARE @UserName VARCHAR(MAX)=''          

 IF @UserID<>''          
 BEGIN          
 SELECT @UserName=FirstName+space(1)+LastName from tbluser where LoginID=@UserID          
 END          

 SET @Status=''            


 IF @IsFinalCSM = 'Y'      
 BEGIN        
  IF NOT EXISTS(SELECT * FROM tblLastCSMDelivered WHERE Ticker=@Ticker AND ClientCode=@ClientCode         
  AND Earnings=@Earnings AND PrePost=@PrePost)            
 BEGIN            
    INSERT INTO tblLastCSMDelivered (Ticker,ClientCode,EarningTickerID,AllDateMapName,Earnings,PrePost,[QC-ViewAllContent],QCCommentsContent,InsertedOn,[Action],UserName,LastCSMDeliveredDate)            
    VALUES(@Ticker,@ClientCode,@EarningTickerID,@AllDateMapName,@Earnings,@PrePost,@QCViewAllContent,@QCCommentsContent,GetDate(),'I',@UserName,GetDate())            

    SET @Status='I'            
  END      
  ELSE      
   BEGIN      
     UPDATE tblLastCSMDelivered            
     SET Ticker=@Ticker,            
     ClientCode=@ClientCode,            
     EarningTickerID=@EarningTickerID,            
     AllDateMapName=@AllDateMapName,            
     Earnings=@Earnings,            
     PrePost=@PrePost,            
     [QC-ViewAllContent]=@QCViewAllContent,            
     QCCommentsContent=@QCCommentsContent,            
     [Action]='U',          
     UserName=@UserName,        
     LastCSMDeliveredDate=GetDate()        
     WHERE Ticker=@Ticker AND ClientCode=@ClientCode             
     AND Earnings=@Earnings AND PrePost=@PrePost         

     SET @Status='U'         
   END      
  END        

 SELECT @Status AS Status            
END

From the above SP my insert statement gets called

---

DECLARE @QCViewAllContent XML=N'My sample xml'
DECLARE @QCCommentsContent XML=N'My sample xml'

INSERT INTO tblLastCSMDelivered (Ticker,ClientCode,EarningTickerID,AllDateMapName,Earnings,PrePost,[QC-ViewAllContent],QCCommentsContent,InsertedOn,[Action],UserName,LastCSMDeliveredDate)            
VALUES('ABB','IVZ',97974,'ABB~4Q2021~Pre~AllDateMapping.xml','4Q2021','Pre',@QCViewAllContent,@QCCommentsContent,GetDate(),'I','ADM',GetDate())  

the SP called from .net application and .net application some time giving timeout error but most of the time dotnet application call this SP and data gets inserted.

so when data is not getting inserted then i am not able to know what exactly happening at db level which keeps waiting my insert statement for long time and i am getting time out from dotnet application.

please guide me how can i investigate this issue step by step.

1) from any session my table tblLastCSMDelivered gets locked ? if yes then how could i see that issue a query? which session and from which PC placing the lock? how do i know.

how to capture the reason for which insert statement getting hold and after few minute same SP could insert the data. please guide me. i am sql developer and not have DBA knowledge but i have to capture this issue to report a team.

i got this hind https://blog.sqlauthority.com/2020/04/20/sql-server-blocking-tree-identifying-blocking-chain-using-sql-scripts/

thanks

Answer 1

This is almost certainly a blocking issue. Something else is preventing you from inserting into the table. You will need to catch it when it is happening and look at the wait state of the process to determine what is blocking it.

https://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server/

Answer 2

Hi,

some time when we call that SP from .net winform application then application spinning and at the end timeout error occured.

Please check the discussion and my answer in the following thread

https://learn.microsoft.com/en-us/answers/questions/707356/option-available-for-individual-query-or-sproc-lev.html

In general you can try to reduce the execution time or raise the timeout configuration in the client side. We cannot help in improving the query in order to reduce the time, since you did not provided a full scenario which we can reproduce (queries to create the relevant entities and insert some sample data). Check the above link

Answer 3

Hi @T.Zacks ,

One idea, try to control it with CommandTimeOut property

command.CommandTimeout = 120;  

check the execution plan to see where is it wasting or hogging db resources

To use SQL Profiler, start the trace to find out what is happening

It is very likely to have a parameter sniffing problem

Or there is an error in your connection string, please check it

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.