SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query now runs forever


Query now runs forever

Author
Message
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 444
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
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 444
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
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3936 Visits: 8472
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
)
);




Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 444
Thank you.

Jim
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5356 Visits: 4076
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;-)
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 444
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
Jason L
Jason L
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 441
No comment on the query, but have you tried changing isolation level? Read committed snapshot may solve the issue for you, with appropriate testing.
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 444
I'll look into it.... Any references you recommend?

Jim
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search