September 25, 2013 at 8:17 am
Any idea how this might run forever? It is running through 174,000 rows, but that usually takes sub-second. Would this indicated corruption of some sort? I've made no changes to the query (as a view...) in weeks. "AllLaborInput" is imported weekly...yesterday.
SELECT [tblWorkOrders].[ID] AS WOID
, Sum(round([Reg Hrs], 2)) AS StdHrs
, Sum(round([OT Hrs], 2)) AS OvtHrs
, Sum((CAST(round([Line Cost], 2) AS money))) AS InternalLaborCost
FROM
([AllLaborInput]
INNER JOIN [tblWorkOrders]
ON [AllLaborInput].[WO #] = [tblWorkOrders].[WONbr])
LEFT JOIN [tblUsers]
ON [AllLaborInput].[WORKER NO] = [tblUsers].[UserID]
WHERE ((([AllLaborInput].[VENDOR NAME]) IS NULL) AND (([tblUsers].[UserLaborExclusion]) = 0
OR ([tblUsers].[UserLaborExclusion]) IS NULL))
OR (
(([AllLaborInput].[VENDOR NAME]) IS NULL) AND
(([AllLaborInput].[START DATE]) > CONVERT(DATETIME, '2010-07-12T00:00:00.000', 126)) AND
(([tblUsers].[UserPassword]) = 'nfox')
)
GROUP BY [tblWorkOrders].[ID]
Jim
September 25, 2013 at 9:04 am
Well, I waded into the issue and decided it had to be a lock problem. I found (after a long period of blind scrambling...) that my backup job from the previous night (4 AM) was locking something (Page Lock?) that prevented this query from running. Don't really know why, especially since the command running was a DBCC database integrity check. I killed it, and everything came back to life. Is there a script somewhere that would have found this issue more quickly for me?
Jim
September 25, 2013 at 9:05 am
Cadavre (9/24/2013)
One of these queries may help.
--== Locks held in database ==--SELECT request_session_id AS [spid], DB_NAME(resource_database_id) AS [dbname],
CASE WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_NAME(p.object_id) END AS [entity_name], index_id, resource_type AS [resource],
resource_description AS [description], request_mode AS mode, request_status AS [status]
FROM sys.dm_tran_locks t
LEFT OUTER JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id
WHERE t.resource_type <> 'DATABASE';
--== Blocking queries in database ==--SELECT DES.Session_ID AS [Root Blocking Session ID], DER.STATUS AS [Blocking Session Request Status],
DES.Login_Time AS [Blocking Session Login Time], DES.Login_Name AS [Blocking Session Login Name],
DES.Host_Name AS [Blocking Session Host Name], Coalesce(DER.Start_Time, DES.Last_Request_Start_Time) AS [Request Start Time],
CASE WHEN DES.Last_Request_End_Time >= DES.Last_Request_Start_Time THEN DES.Last_Request_End_Time ELSE NULL END AS [Request End Time],
Substring(TEXT, DER.Statement_Start_Offset / 2, CASE WHEN DER.Statement_End_Offset = - 1 THEN DataLength(TEXT) ELSE DER.Statement_End_Offset / 2 END) AS [Executing Command],
CASE WHEN DER.Session_ID IS NULL THEN 'Blocking session does not have an open request and may be due to an uncommitted transaction.'
WHEN DER.Wait_Type IS NOT NULL THEN 'Blocking session is currently experiencing a ' + DER.Wait_Type + ' wait.'
WHEN DER.STATUS = 'Runnable' THEN 'Blocking session is currently waiting for CPU time.'
WHEN DER.STATUS = 'Suspended' THEN 'Blocking session has been suspended by the scheduler.'
ELSE 'Blocking session is currently in a ' + DER.STATUS + ' status.'
END AS [Blocking Notes]
FROM Sys.DM_Exec_Sessions DES(READUNCOMMITTED)
LEFT JOIN Sys.DM_Exec_Requests DER(READUNCOMMITTED) ON DER.Session_ID = DES.Session_ID
OUTER APPLY Sys.DM_Exec_Sql_Text(DER.Sql_Handle)
WHERE DES.Session_ID IN (SELECT Blocking_Session_ID
FROM Sys.DM_Exec_Requests(READUNCOMMITTED)
WHERE Blocking_Session_ID <> 0 AND Blocking_Session_ID
NOT IN (SELECT session_id
FROM Sys.DM_Exec_Requests(READUNCOMMITTED)
WHERE Blocking_Session_ID <> 0
)
);
September 25, 2013 at 9:54 am
Thank you.
Jim
September 26, 2013 at 4:24 am
JimS-Indy (9/25/2013)
I found (after a long period of blind scrambling...) that my backup job from the previous night (4 AM) was locking something (Page Lock?)
So you are saying that your query runs n times every day . and do you experience blocking only when backup job runs ? and if the problem persists through out the day then there could be another issues too .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 26, 2013 at 8:19 am
I will watch it. I suspect the AllLaborInput table, which is partially replaced every week, my be causing problems. The proximate issue is solved, however.
Thanks
Jim
October 1, 2013 at 6:33 am
No comment on the query, but have you tried changing isolation level? Read committed snapshot may solve the issue for you, with appropriate testing.
October 7, 2013 at 9:54 am
I'll look into it.... Any references you recommend?
Jim
July 14, 2020 at 6:50 am
May i know how do u find that the backup JOB is locking it. any query used or any other way u have checked to find the SPID to kill it.
July 14, 2020 at 6:52 am
can you please quickly explain, i can see some entity name rest or n/a. can you explain from the lock abd blocking query how find which SPID causing locks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply