patternsqlMinor
SQL Server hangs indefinitely on certain joins
Viewed 0 times
sqlindefinitelycertainserverhangsjoins
Problem
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.
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.Mappe
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.Mappe
Solution
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.
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.
Context
StackExchange Database Administrators Q#163350, answer score: 4
Revisions (0)
No revisions yet.