Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query now runs forever Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 8:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1498391
Posted Wednesday, September 25, 2013 9:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1498431
Posted Wednesday, September 25, 2013 9:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:00 AM
Points: 2,433, Visits: 7,501
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
)
);




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1498434
Posted Wednesday, September 25, 2013 9:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
Thank you.

Jim
Post #1498462
Posted Thursday, September 26, 2013 4:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1498755
Posted Thursday, September 26, 2013 8:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
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
Post #1498879
Posted Tuesday, October 1, 2013 6:33 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, June 1, 2014 8:05 PM
Points: 535, Visits: 430
No comment on the query, but have you tried changing isolation level? Read committed snapshot may solve the issue for you, with appropriate testing.
Post #1500373
Posted Monday, October 7, 2013 9:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
I'll look into it.... Any references you recommend?

Jim
Post #1502233
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse