SQL Server hangs when running query with multiple joins on empty tables

Question

We have experienced what appears to be an interesting bug in SQL Server that I would like to describe to you. I am hoping to find out if it IS actually a bug, and if so where I can found out more information about it. If it is not a bug, I hope somebody can explain to me why it is not and what we have unwittingly done wrong. I can't seem to find description of a similar issue and I'm not sure if we should report to Microsoft as a bug or what. I will first describe the problem briefly and provide details after.

In short, the problem seems to be that SQL Server chokes on statements containing many joins when some (large?) portion (sorry, can't be more specific here in terms of numbers) of the tables being joined are empty. By choke, I mean that it churns and churns away at the query, never finishing, which causes SQL Server to stop responding entirely. Actually, running the query once causes the CPU to jump to 25% and stay there. Another attempt and CPU jumps to 50%. Sometimes, CPU would go to 75% or 100%, but at the 50% mark, you can't even log in to the database server any more.

The problem occurs in an application that we have that uses Hibernate (via Coldfusion ORM) and has an entity (Document) that is sub-classed into many different types of document. The SQL query in question is generated by Hibernate when we run a query on the base class Document (e.g. "FROM Document WHERE Locked=1"). This results in a very long SQL query that joins all the sub-class tables together (example below) and sometimes kills the server.

A few things of interest that I found while testing:

1. It ONLY happens when there are a large number of empty joined tables, which is why it was such a bear to track down - never happened when running on our dev or testing databases because they had ample data. So the same query runs instantaneously without issue in one instance and completely kills SQL server in another.
2. I thought at first that the large number of selected columns in the SQL query might be causing an issue so I tested the SQL query with the same joins but only selected a single column from each table so only a small amout of data was actually being selected - no improvement, still hung.
3. I ran the query against the DB that had little data and I started removing the joins one by one. I found I could get to a point where the query would run and run quickly. Adding a join back from this point would cause the query to run, but noticeably slower, and repeating this a few more times would eventually cause it to grind to a halt again and kill the server.

So based on the above I've surmised that there is a bug (or a known fact of which I am ignorant) in SQL Server whereby joins resulting in too many nulls cause an issue. I think it might be a bug rather than a known limitation because it does not result in an error message (like, for example, you would get if you attempted a query with more than 2100 items in an IN clause), but rather a hung server. The server goes into la-la land almost like the joins had resulted in a cartesion product, but it's the opposite - instead of huge amounts of data, I have no data.

Has anybody else ever encountered such an issue? Is there some known limit of SQL server that I am rubbing up against? I have had great difficulty googling the issue because it is hard to define. Should I file a bug with Microsoft for this?

Any insights are greatly appreciated. If any more information is needed from me, please let me know.

Thanks.

Example query follows (I've removed most of the selected columns for brevity):

select
    top 1 document0_.ID as ID715_,
    document0_.CreatedOn as CreatedOn715_,
    document0_.UpdatedOn as UpdatedOn715_,
    document0_.DeletedOn as DeletedOn715_,
    document0_.Deleted as Deleted715_,
    document0_.Active as Active715_,
    document0_.ActivatedOn as Activate8_715_,
    document0_.DeactivatedOn as Deactiva9_715_,
    document0_.Locked as Locked715_,
    document0_.CompletedDate as Complet11_715_,
    document0_.CreateUserID as CreateU23_715_,
    document0_.UpdateUserID as UpdateU24_715_,
    document0_1_.CreatedOn as CreatedOn717_,
    document0_1_.UpdatedOn as UpdatedOn717_,
    document0_1_.DeletedOn as DeletedOn717_,
    document0_1_.Deleted as Deleted717_,
    document0_1_.Active as Active717_,
    document0_1_.ActivatedOn as Activate7_717_,
    document0_1_.DeactivatedOn as Deactiva8_717_,
    document0_1_.Locked as Locked717_,
    document0_1_.DateReceived as DateRec10_717_,
    document0_1_.DateIssued as DateIssued717_,
    document0_1_.CreateUserID as CreateU12_717_,
    document0_1_.UpdateUserID as UpdateU13_717_,
    document0_2_.CreatedOn as CreatedOn733_,
    document0_2_.UpdatedOn as UpdatedOn733_,
    document0_2_.DeletedOn as DeletedOn733_,
    document0_2_.Deleted as Deleted733_,
    document0_2_.Active as Active733_,
    document0_2_.ActivatedOn as Activate7_733_,
    document0_2_.DeactivatedOn as Deactiva8_733_,
    document0_2_.Locked as Locked733_,
    document0_3_.CreatedOn as CreatedOn739_,
    document0_3_.UpdatedOn as UpdatedOn739_,
    document0_3_.DeletedOn as DeletedOn739_,
    document0_3_.Deleted as Deleted739_,
    document0_3_.Active as Active739_,
    document0_3_.ActivatedOn as Activate7_739_,
    document0_3_.DeactivatedOn as Deactiva8_739_,
    document0_3_.Locked as Locked739_,
    document0_4_.CreatedOn as CreatedOn754_,
    document0_4_.UpdatedOn as UpdatedOn754_,
    document0_4_.DeletedOn as DeletedOn754_,
    document0_4_.Deleted as Deleted754_,
    document0_4_.Active as Active754_,
    document0_4_.ActivatedOn as Activate7_754_,
    document0_4_.DeactivatedOn as Deactiva8_754_,
    document0_4_.Locked as Locked754_,
    document0_5_.CreatedOn as CreatedOn755_,
    document0_5_.UpdatedOn as UpdatedOn755_,
    document0_5_.DeletedOn as DeletedOn755_,
    document0_5_.Deleted as Deleted755_,
    document0_5_.Active as Active755_,
    document0_5_.ActivatedOn as Activate7_755_,
    document0_5_.DeactivatedOn as Deactiva8_755_,
    document0_5_.Locked as Locked755_,
    document0_6_.CreatedOn as CreatedOn759_,
    document0_6_.UpdatedOn as UpdatedOn759_,
    document0_6_.DeletedOn as DeletedOn759_,
    document0_6_.Deleted as Deleted759_,
    document0_6_.Active as Active759_,
    document0_6_.ActivatedOn as Activate7_759_,
    document0_6_.DeactivatedOn as Deactiva8_759_,
    document0_6_.Locked as Locked759_,
    document0_7_.CreatedOn as CreatedOn773_,
    document0_7_.UpdatedOn as UpdatedOn773_,
    document0_7_.DeletedOn as DeletedOn773_,
    document0_7_.Deleted as Deleted773_,
    document0_7_.Active as Active773_,
    document0_7_.ActivatedOn as Activate7_773_,
    document0_7_.DeactivatedOn as Deactiva8_773_,
    document0_7_.Locked as Locked773_,
    document0_8_.CreatedOn as CreatedOn774_,
    document0_8_.UpdatedOn as UpdatedOn774_,
    document0_8_.DeletedOn as DeletedOn774_,
    document0_8_.Deleted as Deleted774_,
    document0_8_.Active as Active774_,
    document0_8_.ActivatedOn as Activate7_774_,
    document0_8_.DeactivatedOn as Deactiva8_774_,
    document0_8_.Locked as Locked774_,
    document0_9_.CreatedOn as CreatedOn779_,
    document0_9_.UpdatedOn as UpdatedOn779_,
    document0_9_.DeletedOn as DeletedOn779_,
    document0_9_.Deleted as Deleted779_,
    document0_9_.Active as Active779_,
    document0_9_.ActivatedOn as Activate7_779_,
    document0_9_.DeactivatedOn as Deactiva8_779_,
    document0_9_.Locked as Locked779_,
    document0_10_.CreatedOn as CreatedOn780_,
    document0_10_.UpdatedOn as UpdatedOn780_,
    document0_10_.DeletedOn as DeletedOn780_,
    document0_10_.Deleted as Deleted780_,
    document0_10_.Active as Active780_,
    document0_10_.ActivatedOn as Activate7_780_,
    document0_10_.DeactivatedOn as Deactiva8_780_,
    document0_10_.Locked as Locked780_,
    document0_11_.CreatedOn as CreatedOn781_,
    document0_11_.UpdatedOn as UpdatedOn781_,
    document0_11_.DeletedOn as DeletedOn781_,
    document0_11_.Deleted as Deleted781_,
    document0_11_.Active as Active781_,
    document0_11_.ActivatedOn as Activate7_781_,
    document0_11_.DeactivatedOn as Deactiva8_781_,
    document0_11_.Locked as Locked781_,
    document0_11_.ExpiryDate as ExpiryDate781_,
    document0_11_.Number as Number781_,
    document0_11_.CreateUserID as CreateU12_781_,
    document0_11_.UpdateUserID as UpdateU13_781_,
    document0_12_.CreatedOn as CreatedOn784_,
    document0_12_.UpdatedOn as UpdatedOn784_,
    document0_12_.DeletedOn as DeletedOn784_,
    document0_12_.Deleted as Deleted784_,
    document0_12_.Active as Active784_,
    document0_12_.ActivatedOn as Activate7_784_,
    document0_12_.DeactivatedOn as Deactiva8_784_,
    document0_12_.Locked as Locked784_,
    document0_12_.CreateUserID as CreateU10_784_,
    document0_12_.UpdateUserID as UpdateU11_784_,
    document0_12_.SurveyID as SurveyID784_,
    document0_13_.CreatedOn as CreatedOn789_,
    document0_13_.UpdatedOn as UpdatedOn789_,
    document0_13_.DeletedOn as DeletedOn789_,
    document0_13_.Deleted as Deleted789_,
    document0_13_.Active as Active789_,
    document0_13_.ActivatedOn as Activate7_789_,
    document0_13_.DeactivatedOn as Deactiva8_789_,
    document0_13_.Locked as Locked789_,
    document0_14_.CreatedOn as CreatedOn790_,
    document0_14_.UpdatedOn as UpdatedOn790_,
    document0_14_.DeletedOn as DeletedOn790_,
    document0_14_.Deleted as Deleted790_,
    document0_14_.Active as Active790_,
    document0_14_.ActivatedOn as Activate7_790_,
    document0_14_.DeactivatedOn as Deactiva8_790_,
    document0_14_.Locked as Locked790_,
    document0_14_.aboriginal as aboriginal790_,
    document0_14_.disability as disability790_,
    document0_14_.minority as minority790_,
    document0_14_.refuse as refuse790_,
    document0_14_.CreateUserID as CreateU14_790_,
    document0_14_.UpdateUserID as UpdateU15_790_,
    document0_15_.CreatedOn as CreatedOn791_,
    document0_15_.UpdatedOn as UpdatedOn791_,
    document0_15_.DeletedOn as DeletedOn791_,
    document0_15_.Deleted as Deleted791_,
    document0_15_.Active as Active791_,
    document0_15_.ActivatedOn as Activate7_791_,
    document0_15_.DeactivatedOn as Deactiva8_791_,
    document0_15_.Locked as Locked791_,
    document0_15_.TypeOfTraining as TypeOfT10_791_,
    document0_15_.Location as Location791_,
    document0_15_.TrainerName as Trainer12_791_,
    document0_15_.[Result] as Result13_791_,
    document0_15_.TrainingDate as Trainin14_791_,
    document0_15_.ExpiryDate as ExpiryDate791_,
    document0_15_.CreateUserID as CreateU16_791_,
    document0_15_.UpdateUserID as UpdateU17_791_,
    document0_16_.CreatedOn as CreatedOn792_,
    document0_16_.UpdatedOn as UpdatedOn792_,
    document0_16_.DeletedOn as DeletedOn792_,
    document0_16_.Deleted as Deleted792_,
    document0_16_.Active as Active792_,
    document0_16_.ActivatedOn as Activate7_792_,
    document0_16_.DeactivatedOn as Deactiva8_792_,
    document0_16_.Locked as Locked792_,
    document0_16_.CreateUserID as CreateU10_792_,
    document0_16_.UpdateUserID as UpdateU11_792_,
    document0_17_.CreatedOn as CreatedOn793_,
    document0_17_.UpdatedOn as UpdatedOn793_,
    document0_17_.DeletedOn as DeletedOn793_,
    document0_17_.Deleted as Deleted793_,
    document0_17_.Active as Active793_,
    document0_17_.ActivatedOn as Activate7_793_,
    document0_17_.DeactivatedOn as Deactiva8_793_,
    document0_17_.Locked as Locked793_,
    document0_17_.Content as Content793_,
    document0_17_.CreateUserID as CreateU11_793_,
    document0_17_.UpdateUserID as UpdateU12_793_,
    document0_18_.CreatedOn as CreatedOn795_,
    document0_18_.UpdatedOn as UpdatedOn795_,
    document0_18_.DeletedOn as DeletedOn795_,
    document0_18_.Deleted as Deleted795_,
    document0_18_.Active as Active795_,
    document0_18_.ActivatedOn as Activate7_795_,
    document0_18_.DeactivatedOn as Deactiva8_795_,
    document0_18_.Locked as Locked795_,
    document0_19_.CreatedOn as CreatedOn800_,
    document0_19_.UpdatedOn as UpdatedOn800_,
    document0_19_.DeletedOn as DeletedOn800_,
    document0_19_.Deleted as Deleted800_,
    document0_19_.Active as Active800_,
    document0_19_.ActivatedOn as Activate7_800_,
    document0_19_.DeactivatedOn as Deactiva8_800_,
    document0_19_.Locked as Locked800_,
    document0_20_.CreatedOn as CreatedOn802_,
    document0_20_.UpdatedOn as UpdatedOn802_,
    document0_20_.DeletedOn as DeletedOn802_,
    document0_20_.Deleted as Deleted802_,
    document0_20_.Active as Active802_,
    document0_20_.ActivatedOn as Activate7_802_,
    document0_20_.DeactivatedOn as Deactiva8_802_,
    document0_20_.Locked as Locked802_,
    document0_20_.StartDate as StartDate802_,
    document0_20_.EndDate as EndDate802_,
    document0_21_.CreatedOn as CreatedOn807_,
    document0_21_.UpdatedOn as UpdatedOn807_,
    document0_21_.DeletedOn as DeletedOn807_,
    document0_21_.Deleted as Deleted807_,
    document0_21_.Active as Active807_,
    document0_21_.ActivatedOn as Activate7_807_,
    document0_21_.DeactivatedOn as Deactiva8_807_,
    document0_21_.Locked as Locked807_,
    document0_21_.CreateUserID as CreateU18_807_,
    document0_21_.UpdateUserID as UpdateU19_807_,
    document0_21_.EducationLevelTypeID as Educati21_807_,
    document0_22_.CreatedOn as CreatedOn808_,
    document0_22_.UpdatedOn as UpdatedOn808_,
    document0_22_.DeletedOn as DeletedOn808_,
    document0_22_.Deleted as Deleted808_,
    document0_22_.Active as Active808_,
    document0_22_.ActivatedOn as Activate7_808_,
    document0_22_.DeactivatedOn as Deactiva8_808_,
    document0_22_.Locked as Locked808_,
    document0_22_.CreateUserID as CreateU10_808_,
    document0_22_.UpdateUserID as UpdateU11_808_,
    document0_23_.CreatedOn as CreatedOn809_,
    document0_23_.UpdatedOn as UpdatedOn809_,
    document0_23_.DeletedOn as DeletedOn809_,
    document0_23_.Deleted as Deleted809_,
    document0_23_.Active as Active809_,
    document0_23_.ActivatedOn as Activate7_809_,
    document0_23_.DeactivatedOn as Deactiva8_809_,
    document0_23_.Locked as Locked809_,
    document0_24_.CreatedOn as CreatedOn810_,
    document0_24_.UpdatedOn as UpdatedOn810_,
    document0_24_.DeletedOn as DeletedOn810_,
    document0_24_.Deleted as Deleted810_,
    document0_24_.Active as Active810_,
    document0_24_.ActivatedOn as Activate7_810_,
    document0_24_.DeactivatedOn as Deactiva8_810_,
    document0_24_.Locked as Locked810_,
    document0_24_.CreateUserID as CreateU10_810_,
    document0_24_.UpdateUserID as UpdateU11_810_,
    document0_25_.CreatedOn as CreatedOn811_,
    document0_25_.UpdatedOn as UpdatedOn811_,
    document0_25_.DeletedOn as DeletedOn811_,
    document0_25_.Deleted as Deleted811_,
    document0_25_.Active as Active811_,
    document0_25_.ActivatedOn as Activate7_811_,
    document0_25_.DeactivatedOn as Deactiva8_811_,
    document0_25_.Locked as Locked811_,
    document0_26_.CreatedOn as CreatedOn815_,
    document0_26_.UpdatedOn as UpdatedOn815_,
    document0_26_.DeletedOn as DeletedOn815_,
    document0_26_.Deleted as Deleted815_,
    document0_26_.Active as Active815_,
    document0_26_.ActivatedOn as Activate7_815_,
    document0_26_.DeactivatedOn as Deactiva8_815_,
    document0_26_.Locked as Locked815_,
    document0_27_.CreatedOn as CreatedOn816_,
    document0_27_.UpdatedOn as UpdatedOn816_,
    document0_27_.DeletedOn as DeletedOn816_,
    document0_27_.Deleted as Deleted816_,
    document0_27_.Active as Active816_,
    document0_27_.ActivatedOn as Activate7_816_,
    document0_27_.DeactivatedOn as Deactiva8_816_,
    document0_27_.Locked as Locked816_,
    document0_27_.DateReceived as DateRec10_816_,
    document0_27_.DateIssued as DateIssued816_,
    document0_27_.CreateUserID as CreateU12_816_,
    document0_27_.UpdateUserID as UpdateU13_816_,
    document0_28_.CreatedOn as CreatedOn819_,
    document0_28_.UpdatedOn as UpdatedOn819_,
    document0_28_.DeletedOn as DeletedOn819_,
    document0_28_.Deleted as Deleted819_,
    document0_28_.Active as Active819_,
    document0_28_.ActivatedOn as Activate7_819_,
    document0_28_.DeactivatedOn as Deactiva8_819_,
    document0_28_.Locked as Locked819_,
    document0_28_.LicenceDocumentNumber as Licence10_819_,
    document0_28_.DemeritPoints as Demerit11_819_,
    document0_28_.MeritPoints as MeritPo12_819_,
    document0_28_.ExpiryDate as ExpiryDate819_,
    document0_28_.CreateUserID as CreateU14_819_,
    document0_28_.UpdateUserID as UpdateU15_819_,
    document0_28_.LicenceDocumentStatusTypeID as Licence16_819_,
    document0_28_.StateProvinceID as StatePr17_819_,
    document0_29_.CreatedOn as CreatedOn820_,
    document0_29_.UpdatedOn as UpdatedOn820_,
    document0_29_.DeletedOn as DeletedOn820_,
    document0_29_.Deleted as Deleted820_,
    document0_29_.Active as Active820_,
    document0_29_.ActivatedOn as Activate7_820_,
    document0_29_.DeactivatedOn as Deactiva8_820_,
    document0_29_.Locked as Locked820_,
    document0_29_.CreateUserID as CreateU10_820_,
    document0_29_.UpdateUserID as UpdateU11_820_,
    document0_30_.CreatedOn as CreatedOn821_,
    document0_30_.UpdatedOn as UpdatedOn821_,
    document0_30_.DeletedOn as DeletedOn821_,
    document0_30_.Deleted as Deleted821_,
    document0_30_.Active as Active821_,
    document0_30_.ActivatedOn as Activate7_821_,
    document0_30_.DeactivatedOn as Deactiva8_821_,
    document0_30_.Locked as Locked821_,
    document0_30_.ExpiryDate as ExpiryDate821_,
    document0_30_.LicenceNumber as Licence11_821_,
    document0_30_.CreateUserID as CreateU12_821_,
    document0_30_.UpdateUserID as UpdateU13_821_,
    document0_30_.StateProvinceID as StatePr14_821_,
    document0_31_.CreatedOn as CreatedOn822_,
    document0_31_.UpdatedOn as UpdatedOn822_,
    document0_31_.DeletedOn as DeletedOn822_,
    document0_31_.Deleted as Deleted822_,
    document0_31_.Active as Active822_,
    document0_31_.ActivatedOn as Activate7_822_,
    document0_31_.DeactivatedOn as Deactiva8_822_,
    document0_31_.Locked as Locked822_,
    document0_32_.CreatedOn as CreatedOn823_,
    document0_32_.UpdatedOn as UpdatedOn823_,
    document0_32_.DeletedOn as DeletedOn823_,
    document0_32_.Deleted as Deleted823_,
    document0_32_.Active as Active823_,
    document0_32_.ActivatedOn as Activate7_823_,
    document0_32_.DeactivatedOn as Deactiva8_823_,
    document0_32_.Locked as Locked823_,
    document0_33_.CreatedOn as CreatedOn824_,
    document0_33_.UpdatedOn as UpdatedOn824_,
    document0_33_.DeletedOn as DeletedOn824_,
    document0_33_.Deleted as Deleted824_,
    document0_33_.Active as Active824_,
    document0_33_.ActivatedOn as Activate7_824_,
    document0_33_.DeactivatedOn as Deactiva8_824_,
    document0_33_.Locked as Locked824_,
    document0_33_.CreateUserID as CreateU10_824_,
    document0_33_.UpdateUserID as UpdateU11_824_,
    document0_34_.CreatedOn as CreatedOn825_,
    document0_34_.UpdatedOn as UpdatedOn825_,
    document0_34_.DeletedOn as DeletedOn825_,
    document0_34_.Deleted as Deleted825_,
    document0_34_.Active as Active825_,
    document0_34_.ActivatedOn as Activate7_825_,
    document0_34_.DeactivatedOn as Deactiva8_825_,
    document0_34_.Locked as Locked825_,
    document0_35_.CreatedOn as CreatedOn826_,
    document0_35_.UpdatedOn as UpdatedOn826_,
    document0_35_.DeletedOn as DeletedOn826_,
    document0_35_.Deleted as Deleted826_,
    document0_35_.Active as Active826_,
    document0_35_.ActivatedOn as Activate7_826_,
    document0_35_.DeactivatedOn as Deactiva8_826_,
    document0_35_.Locked as Locked826_,
    document0_35_.ExpiryDate as ExpiryDate826_,
    document0_35_.CreateUserID as CreateU11_826_,
    document0_35_.UpdateUserID as UpdateU12_826_,
    document0_36_.CreatedOn as CreatedOn827_,
    document0_36_.UpdatedOn as UpdatedOn827_,
    document0_36_.DeletedOn as DeletedOn827_,
    document0_36_.Deleted as Deleted827_,
    document0_36_.Active as Active827_,
    document0_36_.ActivatedOn as Activate7_827_,
    document0_36_.DeactivatedOn as Deactiva8_827_,
    document0_36_.Locked as Locked827_,
    document0_36_.ExpiryDate as ExpiryDate827_,
    document0_36_.CreateUserID as CreateU11_827_,
    document0_36_.UpdateUserID as UpdateU12_827_,
    document0_37_.CreatedOn as CreatedOn829_,
    document0_37_.UpdatedOn as UpdatedOn829_,
    document0_37_.DeletedOn as DeletedOn829_,
    document0_37_.Deleted as Deleted829_,
    document0_37_.Active as Active829_,
    document0_37_.ActivatedOn as Activate7_829_,
    document0_37_.DeactivatedOn as Deactiva8_829_,
    document0_37_.Locked as Locked829_,
    document0_37_.CheckDate as CheckDate829_,
    document0_37_.CreateUserID as CreateU11_829_,
    document0_37_.UpdateUserID as UpdateU12_829_,
    document0_38_.CreatedOn as CreatedOn830_,
    document0_38_.UpdatedOn as UpdatedOn830_,
    document0_38_.DeletedOn as DeletedOn830_,
    document0_38_.Deleted as Deleted830_,
    document0_38_.Active as Active830_,
    document0_38_.ActivatedOn as Activate7_830_,
    document0_38_.DeactivatedOn as Deactiva8_830_,
    document0_38_.Locked as Locked830_,
    document0_39_.CreatedOn as CreatedOn831_,
    document0_39_.UpdatedOn as UpdatedOn831_,
    document0_39_.DeletedOn as DeletedOn831_,
    document0_39_.Deleted as Deleted831_,
    document0_39_.Active as Active831_,
    document0_39_.ActivatedOn as Activate7_831_,
    document0_39_.DeactivatedOn as Deactiva8_831_,
    document0_39_.Locked as Locked831_,
    document0_40_.CreatedOn as CreatedOn832_,
    document0_40_.UpdatedOn as UpdatedOn832_,
    document0_40_.DeletedOn as DeletedOn832_,
    document0_40_.Deleted as Deleted832_,
    document0_40_.Active as Active832_,
    document0_40_.ActivatedOn as Activate7_832_,
    document0_40_.DeactivatedOn as Deactiva8_832_,
    document0_40_.Locked as Locked832_,
    document0_41_.CreatedOn as CreatedOn833_,
    document0_41_.UpdatedOn as UpdatedOn833_,
    document0_41_.DeletedOn as DeletedOn833_,
    document0_41_.Deleted as Deleted833_,
    document0_41_.Active as Active833_,
    document0_41_.ActivatedOn as Activate7_833_,
    document0_41_.DeactivatedOn as Deactiva8_833_,
    document0_41_.Locked as Locked833_,
    document0_41_.CreateUserID as CreateU10_833_,
    document0_41_.UpdateUserID as UpdateU11_833_,
    document0_.Subtype as Subtype715_ 
from
    Document document0_ 
left outer join
    CustomDocument1 document0_1_ 
        on document0_.ID=document0_1_.DocumentID 
left outer join
    CustomDocument2 document0_2_ 
        on document0_.ID=document0_2_.DocumentID 
left outer join
    CustomDocument3 document0_3_ 
        on document0_.ID=document0_3_.DocumentID 
left outer join
    CustomDocument4 document0_4_ 
        on document0_.ID=document0_4_.DocumentID 
left outer join
    CustomDocument5 document0_5_ 
        on document0_.ID=document0_5_.DocumentID 
left outer join
    CustomDocument6 document0_6_ 
        on document0_.ID=document0_6_.DocumentID 
left outer join
    CustomDocument7 document0_7_ 
        on document0_.ID=document0_7_.DocumentID 
left outer join
    CustomDocument8 document0_8_ 
        on document0_.ID=document0_8_.DocumentID 
left outer join
    CustomDocument9 document0_9_ 
        on document0_.ID=document0_9_.DocumentID 
left outer join
    CustomDocument10 document0_10_ 
        on document0_.ID=document0_10_.DocumentID 
left outer join
    CustomDocument11 document0_11_ 
        on document0_.ID=document0_11_.DocumentID 
left outer join
    CustomDocument12 document0_12_ 
        on document0_.ID=document0_12_.DocumentID 
left outer join
    CustomDocument13 document0_13_ 
        on document0_.ID=document0_13_.DocumentID 
left outer join
    CustomDocument14 document0_14_ 
        on document0_.ID=document0_14_.DocumentID 
left outer join
    CustomDocument15 document0_15_ 
        on document0_.ID=document0_15_.DocumentID 
left outer join
    CustomDocument16 document0_16_ 
        on document0_.ID=document0_16_.DocumentID 
left outer join
    CustomDocument17 document0_17_ 
        on document0_.ID=document0_17_.DocumentID 
left outer join
    CustomDocument18 document0_18_ 
        on document0_.ID=document0_18_.DocumentID 
left outer join
    CustomDocument19 document0_19_ 
        on document0_.ID=document0_19_.DocumentID 
left outer join
    CustomDocument20 document0_20_ 
        on document0_.ID=document0_20_.DocumentID 
left outer join
    CustomDocument21 document0_21_ 
        on document0_.ID=document0_21_.DocumentID 
left outer join
    CustomDocument22 document0_22_ 
        on document0_.ID=document0_22_.DocumentID 
left outer join
    CustomDocument23 document0_23_ 
        on document0_.ID=document0_23_.DocumentID 
left outer join
    CustomDocument24 document0_24_ 
        on document0_.ID=document0_24_.DocumentID 
left outer join
    CustomDocument25 document0_25_ 
        on document0_.ID=document0_25_.DocumentID 
left outer join
    CustomDocument26 document0_26_ 
        on document0_.ID=document0_26_.DocumentID 
left outer join
    CustomDocument27 document0_27_ 
        on document0_.ID=document0_27_.DocumentID 
left outer join
    CustomDocument28 document0_28_ 
        on document0_.ID=document0_28_.DocumentID 
left outer join
    CustomDocument29 document0_29_ 
        on document0_.ID=document0_29_.DocumentID 
left outer join
    CustomDocument30 document0_30_ 
        on document0_.ID=document0_30_.DocumentID 
left outer join
    CustomDocument31 document0_31_ 
        on document0_.ID=document0_31_.DocumentID 
left outer join
    CustomDocument32 document0_32_ 
        on document0_.ID=document0_32_.DocumentID 
left outer join
    CustomDocument33 document0_33_ 
        on document0_.ID=document0_33_.DocumentID 
left outer join
    CustomDocument34 document0_34_ 
        on document0_.ID=document0_34_.DocumentID 
left outer join
    CustomDocument35 document0_35_ 
        on document0_.ID=document0_35_.DocumentID 
left outer join
    CustomDocument36 document0_36_ 
        on document0_.ID=document0_36_.DocumentID 
left outer join
    CustomDocument37 document0_37_ 
        on document0_.ID=document0_37_.DocumentID 
left outer join
    CustomDocument38 document0_38_ 
        on document0_.ID=document0_38_.DocumentID 
left outer join
    CustomDocument39 document0_39_ 
        on document0_.ID=document0_39_.DocumentID 
left outer join
    CustomDocument40 document0_40_ 
        on document0_.ID=document0_40_.DocumentID 
left outer join
    CustomDocument41 document0_41_ 
        on document0_.ID=document0_41_.DocumentID 
where
    document0_.DocumentTypeID=? 
    and document0_.Locked=?

Answer 1

Thanks to @TabAlleman, @Ionic et al. The comments helped me realize that it is the calculation of the execution plan that is sucking up so many resources. This understanding led me to find this SO question asking about execution plans taking a long time to generate in SQL Server 2014.

I should have mentioned that we are using SQL Server 2014 as it turns out this version introduced an aggressive new query optimizer that has trouble with the query in question. If I set the database compatibility to SQL Server 2012, the execution plan is generated immediately.

I may still log a bug in SQL Connect as this query may represent an edge case that needs looking into.

Thanks all.