SQL Server Query never completes when filtered by specific value

user_3798837 0
Viewed 800

I have a table named inventory_transaction in a SQL Server 2008 R2 production environment. The table and indexes, as well as the maintenance package to maintain them, were developed by a professional vendor. We have had no problems for almost 10 years since implementing the program that uses this table, and there is no fragmentation above 8% on any of the indexes for the table.

However, I am having some trouble now. When I try to filter this table by one specific value, the query will not complete. Any other value will make the query execute in < 1 sec.

Here is the specific query that will not complete:

DECLARE @BASE_ID VARCHAR(30) = 'W46873'    

SELECT * 
FROM PROD.DBO.INVENTORY_TRANS  
WHERE WORKORDER_BASE_ID = @BASE_ID

I let it run for 35 minutes before cancelling it. I never receive an error message. If I put literally any other value in @BASE_ID, it will execute in <1 sec. If I change the equal operator on WORKORDER_BASE_ID to use LIKE and change @BASE_ID to @BASE_ID = 'W4687%', it will work. All values in this column are the same format (W + 5 numbers).

The data type for @BASE_ID is the same as the column WORKORDER_BASE_ID on the table. There is a nonclustered index that includes this column which is being used by in other query plans that are completing for other values. Since this one won't complete, I'm not sure what the actual execution plan it is using is.

Any ideas what could be causing this or what I could do to fix it? I can provide more information as needed. This issue is causing timeouts within the program, which creates a lot of problems.

EDIT1: Running the query with OPTION(RECOMPILE) doesn't help.

DECLARE @BASE_ID VARCHAR(30) = 'W46873'    

SELECT * 
FROM VMFGTEST.DBO.INVENTORY_TRANS  
WHERE WORKORDER_BASE_ID = @BASE_ID
OPTION (RECOMPILE)
sql-server-2008-r2 sql-server sql
1 Answers

Your query may have been blocked by another process. While running your query in SQL Server Management Studio (SSMS), check the tab title. The parenthesized number at the end is the Server Process ID (SPID):

enter image description here

While your query is still executing, execute the following command in another window to check if your query is being blocked:

/* Replace 70 with actual SPID */
SELECT * FROM sys.sysprocesses WHERE spid = 70

If the blocked column contains a positive number, this is the SPID of the process blocking your query. For more details about this process, execute the following command:

/* Replace 62 with the actual SPID of the process in question */
DBCC INPUTBUFFER(62)
SELECT * FROM sys.sysprocesses WHERE spid = 62

The EventInfo may hint you on what is being executed in this connection, while login_time, hostname, program_name and other columns could pinpoint the connected user. For example another transaction may still be active.

user_86493930
Related