June 26, 2014 at 7:13 am
Morning all
Got a job here that fails on the first Wednesday of every month.
It's a complex job (66 steps at last count) that takes roughly 7 hours a night. It starts at 1am, and should be finished by 8am.
The first step normally takes under 5 minutes and is one of the quicker steps
However, on the first Wednesday of every month, the first step never finishes; it'll still be running when I come in at 8.20 (ish...). Kill the job, start it again, and no matter what it will not get past step 1. The only solution is to restart the service (not the server!), and then restart the job. When we do this it then goes through in the usual less than 5 minutes.
According to all the monitoring I'm doing, the actual step is not doing anything; there doesn't appear to be any processing, no CPU usage, no reads, no writes etc. It just will not process it
I've tried clearing the cache, both ad-hoc and proc (the step runs a SP). Although I have to confess I've not tried running the SP in step 1, seeing if that works, and then run the job from step 2. Part of the issue is we need to get the process running as quickly as possible, and I don't generally have time to experiment.
Ops and Tech Support say there is nothing running on the server (servers - as it's a cluster) - no AV downloads or anything. The fact I only have to restart the service, rather than the server would imply it's a SQL thing. I have not tried a pre-emptive restart the night before yet; downtime is not appreciated...
Anyone got any ideas? We're coming up to the next First Wednesday, and it would be handy to have some excuses ideas in place.
thanks
pete
June 26, 2014 at 7:22 am
What statement does it hang on?
What does that statement do?
If you run that statement separately, what happens?
Is it waiting for a file?
What are the wait types involved?
What state is that session in?
Do you have my crystal ball?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2014 at 7:34 am
sorry Gail...
It hangs on a select into, but the step itself truncates a table, then selects data and put it back into the table.
Run the statement separately, it runs fine.
It's not waiting for a file - the data is all present and correct (hence it will run as normal as soon as the service is restarted)
There are no wait types - as I say, it doesn't appear to be processing at all. It starts, then literally does nothing. There is nothing unusual under sp_who2, activitity monitor, Adam Machanic's sp_whoisactive etc
When I run it against a script using sys.dm_os_tasks joined withdm_os_waiting_tasks, it will show the state as running, but there are no wait types, no blocking sessions, no resource_description etc
I'm afraid I've lost the crystal ball.
June 26, 2014 at 7:49 am
If it happens at roughly the same time and only on the first Wednesday of a month, then there's something scheduled or a human is running something at the same time or....
Since bouncing the service works, it could be a parameter sniffing problem (bouncing the service certainly clears cache) cause by someone running a month-end job or something similar. My first shot in the dark on this would be to add a forced RECOMPILE to the proc.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2014 at 7:54 am
funnily enough i've had both
option (OPTIMIZE FOR (@StartDate UNKNOWN, @todayinJulian UNKNOWN),MAXDOP 12)
and
option (RECOMPILE,MAXDOP 12)
on there (not at the same time, obv). It happened no matter what.
June 26, 2014 at 7:56 am
Can you post that specific select into please? The entire statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2014 at 8:10 am
here you go; fairly anonymised
INSERT INTO dbo.tblSales2
(
duedate
,gldate
,invoicedate
,PaidStatus
,OverdueStatus
,Days
,OverdueAmount
,InvoiceDate
,DueDate
,PaiementDate
,GLDate
,PayStatus
,DocCompany
,OrgID
,OrganisationDesc1
,HyperionCode
,AddressNum
,AddressBookSearchType
,CustomerID
,JDECustDesc
,EDBCustDesc
,DocNumber
,Doctype
,PayItem
,PayItemNumber
,PayItemDocType
,GrossAmount
,openamount
,TaxableAmount
,NonTaxableAmount
,Tax
,RPCRCDCurrencyCode
,CurrencyID
,MonthXRate
,ConstantXrate
,OpenAmountUSDMonthRateValue
,OpenAmountUSDConstantRateValue
,BankAccount
,ObjectAccount
,Subsidiary
,DateOfLastSentReminder
,InvoiceNumber
,remark
,NoOfPayments
,AlphaName
,ItemNumberShort
,LastUpdatedBy
,DateTimeLastUpdated
,Deleted
)
SELECT
F.duedate
,F.gldate
,F.invoicedate
,CASE
WHEN F.openamount <> 0 THEN 'Not yet Paid'
ELSE 'Paid'
END AS PaidStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'
ELSE 'Not yet Overdue'
END
END)
END AS OverdueStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian
--Overdue
THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate
--Paid but late
THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate
-- Paid on time or earlier
THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
--'Not yet Overdue'
DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
END
END)
END AS Days
,CASE
WHEN F.openamount <> 0 THEN F.openamount
ELSE F.GrossAmount*-1
END AS OverdueAmount
,CASE WHEN F.invoicedate <> 0
THEN
DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)
+DATEADD(yy, LEFT(F.invoicedate,
CASE WHEN LEN(F.invoicedate)=5
THEN 2
ELSE 3
END
)+0,0)
END AS InvoiceDate
,CASE
WHEN F.duedate <> 0
THEN
DATEADD(dd,RIGHT(F.duedate,3)-1,0)
+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS DueDate
,CASE
WHEN F.checkitemdate <> 0
THEN
DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)
+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS PaiementDate
,CASE
WHEN F.gldate <> 0
THEN
DATEADD(dd,RIGHT(F.gldate,3)-1,0)
+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS GLDate
,F.PayStatus
,F.DocCompany
--,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)
,F.OrgID
,O.OrganisationDesc1
,O.HyperionCode
,F.AddressNum
,CS.[Ext/Int]
,C.CustomerID
,CS.CustomerName
,C.CustomerName
,F.DocNumber
,F.Doctype
,F.PayItem
,F.PayItemNumber
,F.PayItemDocType
,F.GrossAmount
,F.openamount
,F.TaxableAmount
,F.NonTaxableAmount
,F.Tax
,CURR.Currency--F.RPCRCDCurrencyCode
,O.CurrencyID
,E.MonthXRate
,E.ConstantXrate
,F.openamount * E.MonthXRate
,F.openamount * E.ConstantXrate
,F.BankAccount
,F.ObjectAccount
,F.Subsidiary
,F.DateOfLastSentReminder
,F.InvoiceNumber
,F.remark
,F.NoOfPayments
,F.AlphaName
,F.ItemNumberShort
,'sales2' AS LastUpdatedBy
,GETDATE() AS DateTimeLastUpdated
,0 AS Deleted
FROM
dbo.sales AS F WITH (NOLOCK)
LEFT OUTER JOIN customersource CS WITH (NOLOCK)
ON CS.ERPID = 1
AND F.AddressNum = CS.ERPCustomerCode
INNER JOIN customer C WITH (NOLOCK)
ON CS.CustomerID = C.CustomerID
INNER JOIN organisation O WITH (NOLOCK)
ON F.OrgID = O.OrgID
INNER JOIN currency CURR WITH (NOLOCK)
ON O.CurrencyID = CURR.CurrencyID
LEFT OUTER JOIN exchangerate E WITH (NOLOCK)
ON E.XrateScenarioTypeID = 1
AND E.Deleted = 0
AND E.XRateTypeID = 1
AND CURR.CurrencyID = E.XrateCurrID
AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear
AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth
WHERE
(F.gldate > @StartDate OR F.gldate = 0) AND (F.duedate > @StartDate OR F.duedate = 0)
AND F.Deleted = 0
AND O.Deleted = 0
UNION
SELECT
F.duedate
,F.gldate
,F.invoicedate
,CASE
WHEN F.openamount <> 0 THEN 'Not yet Paid'
ELSE 'Paid'
END AS PaidStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'
ELSE 'Not yet Overdue'
END
END)
END AS OverdueStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian
--Overdue
THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate
--Paid but late
THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate
-- Paid on time or earlier
THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
--'Not yet Overdue'
DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
END
END)
END AS Days
,CASE
WHEN F.openamount <> 0 THEN F.openamount
ELSE F.GrossAmount*-1
END AS OverdueAmount
,CASE WHEN F.invoicedate <> 0
THEN
DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)
+DATEADD(yy, LEFT(F.invoicedate,
CASE WHEN LEN(F.invoicedate)=5
THEN 2
ELSE 3
END
)+0,0)
END AS InvoiceDate
,CASE
WHEN F.duedate <> 0
THEN
DATEADD(dd,RIGHT(F.duedate,3)-1,0)
+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS DueDate
,CASE
WHEN F.checkitemdate <> 0
THEN
DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)
+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS PaiementDate
,CASE
WHEN F.gldate <> 0
THEN
DATEADD(dd,RIGHT(F.gldate,3)-1,0)
+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS GLDate
,F.PayStatus
,F.DocCompany
--,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)
,F.OrgID
,O.OrganisationDesc1
,O.HyperionCode
,F.AddressNum
,CS.[Ext/Int]
,C.CustomerID
,CS.CustomerName
,C.CustomerName
,F.DocNumber
,F.Doctype
,F.PayItem
,F.PayItemNumber
,F.PayItemDocType
,F.GrossAmount
,F.openamount
,F.TaxableAmount
,F.NonTaxableAmount
,F.Tax
,CURR.Currency--F.RPCRCDCurrencyCode
,O.CurrencyID
,E.MonthXRate
,E.ConstantXrate
,F.openamount * E.MonthXRate
,F.openamount * E.ConstantXrate
,F.BankAccount
,F.ObjectAccount
,F.Subsidiary
,F.DateOfLastSentReminder
,F.InvoiceNumber
,F.remark
,F.NoOfPayments
,F.AlphaName
,F.ItemNumberShort
,'sales2' AS LastUpdatedBy
,GETDATE() AS DateTimeLastUpdated
,0 AS Deleted
FROM
dbo.sales_historicalAS F WITH (NOLOCK)
LEFT OUTER JOIN customerSource CS WITH (NOLOCK)
ON CS.ERPID = 1
AND F.AddressNum = CS.ERPCustomerCode
INNER JOIN customer C WITH (NOLOCK)
ON CS.CustomerID = C.CustomerID
INNER JOIN organisation O WITH (NOLOCK)
--ON ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID) = O.OrgID
ON F.OrgID = O.OrgID
INNER JOIN currency CURR WITH (NOLOCK)
ON O.CurrencyID = CURR.CurrencyID
LEFT OUTER JOIN exchangerate E WITH (NOLOCK)
ON E.XrateScenarioTypeID = 1
AND E.Deleted = 0
AND E.XRateTypeID = 1
AND CURR.CurrencyID = E.XrateCurrID
AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear
AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth
WHERE
(F.gldate <= @StartDate OR F.duedate <= @StartDate)
AND (F.openamount <> 0 AND duedate < @TodayinJulian)
AND F.Deleted = 0
AND O.Deleted = 0
UNION
SELECT
F.duedate
,F.gldate
,F.invoicedate
,CASE
WHEN F.openamount <> 0 THEN 'Not yet Paid'
ELSE 'Paid'
END AS PaidStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'
ELSE 'Not yet Overdue'
END
END)
END AS OverdueStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian
--Overdue
THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate
--Paid but late
THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate
-- Paid on time or earlier
THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
--'Not yet Overdue'
DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
END
END)
END AS Days
,CASE
WHEN F.openamount <> 0 THEN F.openamount
ELSE F.GrossAmount*-1
END AS OverdueAmount
,CASE WHEN F.invoicedate <> 0
THEN
DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)
+DATEADD(yy, LEFT(F.invoicedate,
CASE WHEN LEN(F.invoicedate)=5
THEN 2
ELSE 3
END
)+0,0)
END AS InvoiceDate
,CASE
WHEN F.duedate <> 0
THEN
DATEADD(dd,RIGHT(F.duedate,3)-1,0)
+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS DueDate
,CASE
WHEN F.checkitemdate <> 0
THEN
DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)
+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS PaiementDate
,CASE
WHEN F.gldate <> 0
THEN
DATEADD(dd,RIGHT(F.gldate,3)-1,0)
+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS GLDate
,F.PayStatus
,F.DocCompany
--,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)
,F.OrgID
,O.OrganisationDesc1
,O.HyperionCode
,F.AddressNum
,CS.[Ext/Int]
,C.CustomerID
,CS.CustomerName
,C.CustomerName
,F.DocNumber
,F.Doctype
,F.PayItem
,F.PayItemNumber
,F.PayItemDocType
,F.GrossAmount
,F.openamount
,F.TaxableAmount
,F.NonTaxableAmount
,F.Tax
,CURR.Currency--F.RPCRCDCurrencyCode
,O.CurrencyID
,E.MonthXRate
,E.ConstantXrate
,F.openamount * E.MonthXRate
,F.openamount * E.ConstantXrate
,F.BankAccount
,F.ObjectAccount
,F.Subsidiary
,F.DateOfLastSentReminder
,F.InvoiceNumber
,F.remark
,F.NoOfPayments
,F.AlphaName
,F.ItemNumberShort
,'sales2' AS LastUpdatedBy
,GETDATE() AS DateTimeLastUpdated
,0 AS Deleted
FROM
sales AS F WITH (NOLOCK)
LEFT OUTER JOIN customerSource CS WITH (NOLOCK)
ON CS.ERPID = 1
AND F.AddressNum = CS.ERPCustomerCode
INNER JOIN customer C WITH (NOLOCK)
ON CS.CustomerID = C.CustomerID
INNER JOIN organisation O WITH (NOLOCK)
ON F.OrgID = O.OrgID
INNER JOIN currency CURR WITH (NOLOCK)
ON O.CurrencyID = CURR.CurrencyID
LEFT OUTER JOIN exchangerate E WITH (NOLOCK)
ON E.XrateScenarioTypeID = 1
AND E.Deleted = 0
AND E.XRateTypeID = 1
AND CURR.CurrencyID = E.XrateCurrID
AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear
AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth
WHERE
(F.gldate <= @StartDate OR F.duedate <= @StartDate)
AND (F.openamount <> 0 AND duedate < @TodayinJulian)
AND F.Deleted = 0
AND O.Deleted = 0
OPTION (MAXDOP 12)
June 26, 2014 at 8:13 am
The function - as I'm sure you're going to ask, is solely
Select @Result=
Case When @JulianDateToConvert <> 0
THEN
dateadd(dd,right(@JulianDateToConvert,3)-1,0)
+dateadd(yy,left(@JulianDateToConvert,
case
when len(@JulianDateToConvert)=5
then 2
else 3
end
)+0,0)
ELSE NULL
END
I have wondered about this function, and whether that's the cause...but functions don't get cached do they?
June 26, 2014 at 8:32 am
Also,the two indexes on the table are dropped at the start of the SP, and recreated in a further step.
It gets past the drop index, and truncate easily (we have a debug system in place, so i know where it gets to in the SP)
June 26, 2014 at 9:00 am
Just out of curiosity -
Have you ever tested with a Date / Year / Month / Julian table? And maybe one with current date populated daily?
We used this, and found having a arbitrary date for 0 to be very useful.
This simplified code in many places, and performed very well.
I'd see if a trace at the start of the job shows anything.
See if there is any memory pressure, or unexpected IO happening.
I assume you are not running SSAS on the same server.
June 26, 2014 at 9:15 am
nope - the Cube is on another box
As to the whole Julian malarky - it's a pain, but entire system is set up like that. The fact the SP normally speeds through in 5 minutes implies it's okay.
Traces - no. Unfortunately. We've set up proper monitoring for next week, but obviously it'll be handy if it's not needed!
June 26, 2014 at 9:54 am
It would be useful to capture the execution plan when the query is running fast, and capture it again when the query is slow.
Sounds like it is not locking as there is no wait resource reported. Have you tried updating the database statistics while the job is running on the first Wednesday of the month.
Does the job normally run daily?
Does sp_whoisactive show an extremely high number of logical/physical reads?
Let us know.
Thanks
June 26, 2014 at 10:32 am
peter.cox (6/26/2014)
nope - the Cube is on another boxAs to the whole Julian malarky - it's a pain, but entire system is set up like that. The fact the SP normally speeds through in 5 minutes implies it's okay.
Traces - no. Unfortunately. We've set up proper monitoring for next week, but obviously it'll be handy if it's not needed!
Speeds through in 5 minutes - you have an 8 hour process. If it saves processing time here, might be a lot more savings.
So I would still create a quick test and measure, if for nothing else, just to make sure.
Julian is different to deal with, we just seemed to find handling it more like I suggested performed very well, and had the additional benefit of making the code a bit friendlier.
As far as monitoring, we would write to the event log, and use SCOM to look for the event. If a process hung, we were looking at it before we came in. Sounds like your users basically are a day behind when this happens.
June 26, 2014 at 1:15 pm
You say the cube is on another box...so that implies more than one server is involved. Is there maintenance being done the first Wednesday of the month? Or Tuesday evening? I've seen Domain Controller patching interfere with processes since it 'takes down' the Active Directory and connections can't be made. Check to see if any network work is being done.
-SQLBill
July 1, 2014 at 6:20 am
sorry for the delay in replying.
on the Friday morning we had the same issue; so the first wednesday of the month was a red herring.
On checking there was nothing that really looked out of order. At all.
So i rebooted the server at lunch - and it didn't solve the problem.
In the end we left it the cube production for that day as something else came up. However, on the Saturday morning all went through fine; as if nothing had happened.
Slightly slower maybe, but then it did have a extra day's processing to do.
I have changed the first step, so it no longer uses the function to work out the Gregorian from Julian date; I know have a table that lists everything from 2000-2050 (by then I'd like to think we'll have a better solution).
So we'll see what happens tomorrow morning. Hopefully nothing!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply