SQL server 2014 locks up on insert query, while SQL server 2000 runs fine

Question

One of the applications I am supporting, is running into a weird issue. In a particular page, there is an insert query.

INSERT INTO TBL_Parts_In_Arrival_Details ( PIAD_Parts_In_Arrival_Detail_ID, 

PIAD_Parts_In_Request_ID, PIAD_Warehouse_ID, PIAD_Location_ID, PIAD_Qty_Received, 

PIAD_WAP_Price, PIAD_WAP_Currency, PIAD_Arrival_Date, PIAD_SupplierSent_Invoice_No, 
PIAD_Invoice_No, Use_Count, PIAD_CreatedDate, PIAD_CreatedBy, PIAD_ModifiedDate, PIAD_ModifiedBy ) 


VALUES ( @PIAD_Parts_In_Arrival_Detail_ID, @PITM_Parts_In_Request_ID, @PIAD_Warehouse_ID, 

@PIAD_Location_ID, @PIAD_Qty_Received, @PIAD_WAP_Price, @PIAD_WAP_Currency, @PIAD_Arrival_Date, 

@PIAD_SupplierSent_Invoice_No, @PIAD_Invoice_No, 0, GETDATE(), 'prism.dev', GETDATE(), 'prism.dev' )

This query was working fine in SQL server 2000. But, since the server upgrade to 2014, it locks up the database. This query is run as a transaction. If I run this query manually using query analyzer, it works wonderfully.

Now, if there is any problem with the query, normally it should rollback the transaction. But, in this particular case, it doesn't. So, I end up with some 5-6 instances of same process in SQL server activity monitor, all of them in suspended state. At that point, server stops responding to queries, and only thing I can do is kill all processes.

Since, I am pretty much a noob in this, and out of depth here. I want to understand what might be causing this, so I can try to fix this. Basically, just looking to understand possible reasons.

I am not sure, what details, I should provide to make this more question more readable. If you feel there is something I missed, do let me know, and I will add it promptly.

Here is the result of SELECT status, wait_type, blocking_session_id FROM sys.dm_exec_requests;:

+------------+------------------------------------+---------------------+
|   status   |             wait_type              | blocking_session_id |
+------------+------------------------------------+---------------------+
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| background | BROKER_EVENTHANDLER                |                   0 |
| background | BROKER_TRANSMITTER                 |                   0 |
| background | SLEEP_TASK                         |                   0 |
| background | HADR_FILESTREAM_IOMGR_IOCOMPLETION |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| sleeping   | NULL                               |                   0 |
| running    | NULL                               |                   0 |
+------------+------------------------------------+---------------------+

Update:

So, I was looking more into this. Turns out, this query locks up, because right after this query, we have another SQL query in same transaction.

        string strSQL
        = " SELECT"
        + " COUNT(1)"
        + " FROM"
        + " TBL_Parts_In_Txn_Master"
        + " WHERE"
        + "     PITM_Related_Request_No = @MRPR_MR_No"
        + " AND PITM_Continuation_No    = @MRPR_Continuation_No"
        + " AND PITM_Seq_No             = @MRPR_Seq_No"
        + " AND PITM_Parts_Spec_ID      = @MRPR_Return_Faulty_Part_Spec"
        + " AND PITM_Part_Type_ID       = 19"
        + " AND PITM_Exchange_Flag      = 'Y'"
        + " AND PITM_Status_ID          = 25";

    return (Convert.ToInt32(this.GetSingleValue(strSQL, this.CreateParameterArray(objParam), CommandType.Text)) > 0) ? true : false;

If I comment out this code, everything works fine, and the insert query doesn't lock up at all. But, if this query is part of the transaction, then it gets locked in perpetuity.

Below is the screenshot of the activity monitor for this DB.

Parameters used by select query:

MRPR_MR_No -- M29284100

MRPR_Continuation_No -- 0

MRPR_Seq_No -- 0 (1 at the second run)

MRPR_Return_Faulty_Part_Spec -- ""

Edit:

Well, I spent some 2 days trying to resolve this. But, till now no luck. The only thing I can find is that, if the select query is ran more than once in the transaction, db locks up. Weird thing is, it locks up only if I select the quantity in insert query (PIAD_Qty_Received) as zero.

If the select query is ran only one time, or the quantity selected is not zero there is no lock.

Answer 1

Ok, so I finally solved it, thanks to the help of my senior manager. Turns out, he had seen similar problem happening earlier.

Anyway, to solve this, all I had to do was create an index for the select query, using the exact column order as where clause. Apparently, the select query was taking too much time, so the second run of the same query was getting locked. Using view, the first query runs successfully in milliseconds, so everything works fine.