I have a stored procedure building a fact table from a data model. I'm generally using the technique described here as I found it gives a significant performance boost.
However, I'm running into situations where SQL Sever 2016 (SP1, CU1, Developer edition) just hangs. One such permutation it hung for 5 days and 23 hours (I went on vacation and let it run to see what would happen). Normal performance for this SP is about a minute. This particular section of the SP is the slowest part at about 40 seconds.
By reducing the joins one at a time, I was able to find a culprit and engineer around this particular problem, however, now I'm expanding my testing and the problem is back (I kill it after 10 minutes, but nothing indicates to me that there is any reason to expect it to be done in 11. sp_whoisactive shows me no locks and no waits. CPU time simply ticks up and up.
The joins look like this:
SELECT
--Most everything here is commented out for my testing, it doesn't make a difference.
INTO #GL
FROM #GLT GL
INNER JOIN TransactionDimension TD ON TD.TransactionId = GL.TransactionId AND TD.ClientId = GL.ClientId
INNER JOIN TransactionLineDimension TLD ON TLD.TransactionLineId = GL.TransactionLineId AND TLD.ClientId = GL.ClientId
INNER JOIN SourceChartOfAccountsDimension SCOAD ON SCOAD.AccountFullName = GL.QBAccountFullName AND SCOAD.ImportFileId = GL.ImportFileId AND SCOAD.ClientId = GL.ClientId
INNER JOIN GeneralLedgerDetailSubstitutionDimension GLDSD ON GLDSD.TransactionLineId = GL.TransactionLineId AND GLDSD.ClientId = GL.ClientId
INNER JOIN HoldingCompanyDimension HCD ON HCD.HoldingCompanyId = GL.HoldingCompanyId AND HCD.ClientId = GL.ClientId
INNER JOIN ClassNameDimension NoClass ON NoClass.JoinClassName = N'(no class)' + nchar(8203) AND NoClass.ClientId = GL.ClientId
INNER JOIN ChartOfAccountsMappingDimension COAMD ON
COAMD.JoinAccountFullName = GL.JoinAccountFullName
AND COAMD.MappedAccountFullName = GL.MappedAccountFullName
AND COAMD.ClientId = GL.ClientId
INNER JOIN ChartOfAccountsCategoryDimension COACD ON COACD.ChartOfAccountsCategoryId = COAMD.ChartOfAccountsCategoryId AND COACD.ClientId = GL.ClientId
INNER JOIN ImportFileDimension IFD ON IFD.ImportFileId = GL.ImportFileId AND IFD.ClientId = GL.ClientId
INNER JOIN SplitDimension SD ON SD.AccountFullName = GL.Split AND SD.ClientId = GL.ClientId
INNER JOIN ConstructionProjectDimension CpCoa ON CpCoa.ConstructionProjectId = COAMD.ConstructionProjectId AND CpCoa.ClientId = COAMD.ClientId
INNER JOIN ConstructionProjectDimension CpClass ON CpClass.ConstructionProjectId = GL.ClassConstructionProjectId AND CpClass.ClientId = GL.ClientId
INNER JOIN InvestorDimension InvestorNameD ON InvestorNameD.InvestorId = GL.NameInvestorId AND InvestorNameD.ClientId = GL.ClientId
INNER JOIN InvestorDimension InvestorCoaD ON InvestorCoaD.InvestorId = COAMD.InvestorId AND InvestorCoaD.ClientId = COAMD.ClientId
INNER JOIN InvestorDimension InvestorClassD ON InvestorClassD.InvestorId = GL.ClassInvestorId AND InvestorClassD.ClientId = GL.ClientId
INNER JOIN InvestorDimension InvestorNullD ON InvestorNullD.InvestorId = 0 AND InvestorNullD.ClientId = GL.ClientId
INNER JOIN HoldingCompanyDimension TransferNameHCD ON TransferNameHCD.HoldingCompanyId = GL.NameHoldingCompanyId AND TransferNameHCD.ClientId = GL.ClientId
INNER JOIN HoldingCompanyDimension TransferCoaHCD ON TransferCoaHCD.HoldingCompanyId = COAMD.TransferToHoldingCompanyId AND TransferCoaHCD.ClientId = COAMD.ClientId
INNER JOIN HoldingCompanyDimension TransferClassHCD ON TransferClassHCD.HoldingCompanyId = GL.ClassHoldingCompanyId AND TransferClassHCD.ClientId = GL.ClientId
INNER JOIN HoldingCompanyDimension TransferNullHCD ON TransferNullHCD.HoldingCompanyId = 0 AND TransferNullHCD.ClientId = GL.ClientId
INNER JOIN InvestmentDimension InvestmentNameD ON InvestmentNameD.InvestmentId = GL.NameInvestmentId AND InvestmentNameD.ClientId = GL.ClientId
--INNER JOIN InvestmentDimension InvestmentCoaD ON InvestmentCoaD.InvestmentId = COAMD.InvestmentId AND InvestmentCoaD.ClientId = COAMD.ClientId
--INNER JOIN InvestmentDimension InvestmentClassD ON InvestmentClassD.InvestmentId = GL.ClassInvestmentId AND InvestmentClassD.ClientId = GL.ClientId
--INNER JOIN InvestmentDimension InvestmentFileD ON InvestmentFileD.InvestmentId = IFD.InvestmentId AND InvestmentFileD.ClientId = IFD.ClientId
--INNER JOIN InvestmentDimension InvestmentHcD ON InvestmentHcD.InvestmentId = HCD.InvestmentId AND InvestmentHcD.ClientId = HCD.ClientId
--This caused SQL Server to break(!) and literally run forever (as in 5 days 23 hours before being killed).
--INNER JOIN InvestmentDimension InvestmentNullD ON InvestmentNullD.InvestmentId = 0 AND InvestmentNullD.ClientId = GL.ClientId
--INNER JOIN PropertyDimension PD ON PD.PropertyId = GL.PropertyId AND PD.ClientId = GL.ClientId
--INNER JOIN PropertyDimension NullPD ON NullPD.PropertyId = 0 AND NullPD.ClientId = GL.ClientId
In this particular case the last join on the list not commented out is what causes it. I also left in the original problematic join. Yes, I am joining to the same table over and over again with different parameters on purpose.
When the problem was the first issue I figured I could work around it. But now this second appearance of the issue suggests to me that the whole procedure isn't reliable. In the case I am currently having a problem with, there are no record in #GLT. Zero. And there are two in the InvestmentDimension table. The original one under test has about 200,000 in #GLT.
Running
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
before running the test changes nothing (not that this would be a good solution, but perhaps it eliminates some possible causes).
Where can I look further at what the problem is and how to work around it?
Further insight:
I rewrote this to chunk it into one insert and three updates. This had the advantage of reducing some code complexity in the select field list, so although it impacts performance, it isn't a terrible compromise for now - I'm still investigating that. However, after I did that and continued my experimentation, suddenly the population of #GTL started hanging forever.
It doesn't have anything like that join list, but it did have one type of join in common, and removing that immediately solved the problem. It was this line:
INNER JOIN InvestorDimension InvestorNullD ON InvestorNullD.InvestorId = 0 AND InvestorNullD.ClientId = GL.ClientId
Basically it is linking to the record where a secondary (non-unique) id is a hard coded number and the other criteria is from a temporary table. Although this line generally works, when the join list gets large enough, it seems to send the compiler into a death spiral when it is there. The reason it started failing for #GLT is that this table now had two records with an InvestorId of 0, one for each ClientId. Previous experiments hadn't gotten that far.
Insights into how to understand this more directly (rather than the hunt and peck of pulling out joins one by one) are still appreciated.
Adding OPTION (RECOMPILE) did not help. Trace flags and database scoped configurations are basically the defaults. I did try turning on optimize for ad hoc workloads but that didn't change anything. There are no trace flags active.
