SQL Server: Insert statement gets stuck some time

T.Zacks 3,936 Reputation points

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)=''          
 DECLARE @Status CHAR(1)            
 DECLARE @UserName VARCHAR(MAX)=''          

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

 SET @Status=''            

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

    SET @Status='I'            
     UPDATE tblLastCSMDelivered            
     SET Ticker=@Ticker,            
     WHERE Ticker=@Ticker AND ClientCode=@ClientCode             
     AND Earnings=@Earnings AND PrePost=@PrePost         

     SET @Status='U'         

 SELECT @Status AS Status            

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)            

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/


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,152 questions
No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,616 Reputation points

    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.


2 additional answers

Sort by: Most helpful
  1. Ronen Ariely 14,146 Reputation points MVP


    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


    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

  2. YufeiShao-msft 6,971 Reputation points

    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.