This is a summary of the problem todate.
There are a number of stored proc, at least 2, that never complete.
If I restart SQl Server, they take about 30 secs to run.
The sp's are in a job that updates a datawarehouse overnight.
Its approx 3 hours into the processing that these 2 sp never complete.
There is no other activity at the time the job is running.
There is no deadlocks.
There are no othere apps running
The same queries run on the prod server fine.
Today I changed the sp, by removing a redundant 'where not exists' statement from the sp's, and re ran the query(without restarting sql server) and they ran in about 30 sec.
My issues is whats going on with sql server that the sp will not complete after a long run, however the same sp will complete when SQL Server is restarted.
Sure I can modify the query to make it always work, but its not behaving consistantly.
Any ideas where to start looking?
Thanks
Fred
Someone is bound to ask about the sp, and here is a snapshot of the code.
Look at the last couple of lines for the redundant "where not exists" clause. Its redundant because the table gets truncated at the begining of the sp.
(This code is autogenerated from a datawarehouse design tool that I use, BPW. The new version of this software doesn't generate the redundant "where not exists" clause)
I'm trying to focus on SQL server, not the sp, as to why after a restart the query runs fine unaltered?
Code:
SET NOCOUNT ON
----------------------------------------------------------
-- Declare local variables to store statistics information
----------------------------------------------------------
DECLARE @RowsInserted Integer,
@RowsUpdated Integer,
@StartTime datetime,
@FinishedTime datetime
SET @StartTime = getdate()
-------------------------------------------------------------
-- Full Refresh of [TTemp_MCHEAD_Costing_Tmpl3] - All data will be replaced
-------------------------------------------------------------
TRUNCATE TABLE [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl3]
----------------------------------------------------
-- UPDATE existing rows --
----------------------------------------------------
UPDATE DestObject
SET
DestObject.[Product structure type] = T3.[Product structure type],
DestObject.[Configuration identity] = T3.[Configuration identity]
--abreviated code
WHERE
-- Natural key(s) --
DestObject.[Company] = T3.[Company] AND
DestObject.[Facility] = T3.[Facility] AND
DestObject.[Item number] = T3.[Item number]
SET @RowsUpdated = @@Rowcount
----------------------------------------------------
-- INSERT new rows --
----------------------------------------------------
INSERT [LiveBPW_Catalog].dbo.[TTemp_MCHEAD_Costing_Tmpl3]
(
[Company],
[Facility],
[Item number],
[Product structure type],
--**abbreviated code
)
SELECT
T3.[Company],
T3.[Facility],
T3.[Item number],
T3.[Product structure type],
--*** abreviated code
FROM
[LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl2] AS T3
LEFT OUTER JOIN [LiveBPW_Catalog].[dbo].[Temp_MITBAL_MITFAC2] AS T1 ON
T3.[Company] = T1.[Company] AND
T3.[Facility] = T1.[Facility] AND
T3.[Item number] = T1.[Item number]
LEFT OUTER JOIN [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl1] AS T2 ON
T3.[Company] = T2.[Company] AND
T3.[Facility] = T2.[Facility] AND
T3.[Item number] = T2.[Item number] AND
T2.[Company] = T1.[Company] AND
T2.[Facility] = T1.[Facility] AND
T2.[Item number] = T1.[Item number] AND
T3.[Company] = T1.[Company] AND
T3.[Facility] = T1.[Facility] AND
T3.[Item number] = T1.[Item number]
--When this code is deleted, the sp runs fine
WHERE NOT EXISTS -- Check that the rows not already exist
(SELECT 1 FROM [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl3] AS DestObject
WHERE
DestObject.[Company] = T3.[Company] AND
DestObject.[Facility] = T3.[Facility] AND
DestObject.[Item number] = T3.[Item number]
)
]
