4

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.

4

You mentioned that you're using SQL Server 2016, which means you can use Live Query Plans.

In SSMS, click Query, Include Live Query Statistics, and run your query. You'll be able to see exactly which part of the query it's working on.

Keep in mind that it's possible the query is only compiling, not actually executing. With really ugly queries - and the one you've posted just might qualify - you can see compilations that take hours.

3

I reported this to Microsoft Support. They have concluded that it is a problem with the New Cardinality Estimation and that setting this to the old one removes the delay.

In terms of workarounds, they suggest to use OPTION(QUERYTRACEON 9476) or the ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS hint (see Update introduces USE HINT query hint argument in SQL Server 2016 for more details). Another alternative is to revert to the old cardinality estimator altogether.

Update: Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) seems to fix the problem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.