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.
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?
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.
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.