hibernate - SQL Server hangs when running query with multiple joins on empty tables -
we have experienced appears interesting bug in sql server describe you. hoping find out if bug, , if can found out more information it. if not bug, hope can explain me why not , have unwittingly done wrong. can't seem find description of similar issue , i'm not sure if should report microsoft bug or what. first describe problem briefly , provide details after.
in short, problem seems sql server chokes on statements containing many joins when (large?) portion (sorry, can't more specific here in terms of numbers) of tables being joined empty. choke, mean churns , churns away @ query, never finishing, causes sql server stop responding entirely. actually, running query once causes cpu jump 25% , stay there. attempt , cpu jumps 50%. sometimes, cpu go 75% or 100%, @ 50% mark, can't log in database server more.
the problem occurs in application have uses hibernate (via coldfusion orm) , has entity (document) sub-classed many different types of document. sql query in question generated hibernate when run query on base class document (e.g. "from document locked=1"). results in long sql query joins sub-class tables (example below) , kills server.
a few things of interest found while testing:
- it happens when there large number of empty joined tables, why such bear track down - never happened when running on our dev or testing databases because had ample data. same query runs instantaneously without issue in 1 instance , kills sql server in another.
- i thought @ first large number of selected columns in sql query might causing issue tested sql query same joins selected single column each table small amout of data being selected - no improvement, still hung.
- i ran query against db had little data , started removing joins 1 one. found point query run , run quickly. adding join point cause query run, noticeably slower, , repeating few more times cause grind halt again , kill server.
so based on above i've surmised there bug (or known fact of ignorant) in sql server whereby joins resulting in many nulls cause issue. think might bug rather known limitation because not result in error message (like, example, if attempted query more 2100 items in in clause), rather hung server. server goes la-la land joins had resulted in cartesion product, it's opposite - instead of huge amounts of data, have no data.
has else ever encountered such issue? there known limit of sql server rubbing against? have had great difficulty googling issue because hard define. should file bug microsoft this?
any insights appreciated. if more information needed me, please let me know.
thanks.
example query follows (i've removed of selected columns brevity):
select top 1 document0_.id id715_, document0_.createdon createdon715_, document0_.updatedon updatedon715_, document0_.deletedon deletedon715_, document0_.deleted deleted715_, document0_.active active715_, document0_.activatedon activate8_715_, document0_.deactivatedon deactiva9_715_, document0_.locked locked715_, document0_.completeddate complet11_715_, document0_.createuserid createu23_715_, document0_.updateuserid updateu24_715_, document0_1_.createdon createdon717_, document0_1_.updatedon updatedon717_, document0_1_.deletedon deletedon717_, document0_1_.deleted deleted717_, document0_1_.active active717_, document0_1_.activatedon activate7_717_, document0_1_.deactivatedon deactiva8_717_, document0_1_.locked locked717_, document0_1_.datereceived daterec10_717_, document0_1_.dateissued dateissued717_, document0_1_.createuserid createu12_717_, document0_1_.updateuserid updateu13_717_, document0_2_.createdon createdon733_, document0_2_.updatedon updatedon733_, document0_2_.deletedon deletedon733_, document0_2_.deleted deleted733_, document0_2_.active active733_, document0_2_.activatedon activate7_733_, document0_2_.deactivatedon deactiva8_733_, document0_2_.locked locked733_, document0_3_.createdon createdon739_, document0_3_.updatedon updatedon739_, document0_3_.deletedon deletedon739_, document0_3_.deleted deleted739_, document0_3_.active active739_, document0_3_.activatedon activate7_739_, document0_3_.deactivatedon deactiva8_739_, document0_3_.locked locked739_, document0_4_.createdon createdon754_, document0_4_.updatedon updatedon754_, document0_4_.deletedon deletedon754_, document0_4_.deleted deleted754_, document0_4_.active active754_, document0_4_.activatedon activate7_754_, document0_4_.deactivatedon deactiva8_754_, document0_4_.locked locked754_, document0_5_.createdon createdon755_, document0_5_.updatedon updatedon755_, document0_5_.deletedon deletedon755_, document0_5_.deleted deleted755_, document0_5_.active active755_, document0_5_.activatedon activate7_755_, document0_5_.deactivatedon deactiva8_755_, document0_5_.locked locked755_, document0_6_.createdon createdon759_, document0_6_.updatedon updatedon759_, document0_6_.deletedon deletedon759_, document0_6_.deleted deleted759_, document0_6_.active active759_, document0_6_.activatedon activate7_759_, document0_6_.deactivatedon deactiva8_759_, document0_6_.locked locked759_, document0_7_.createdon createdon773_, document0_7_.updatedon updatedon773_, document0_7_.deletedon deletedon773_, document0_7_.deleted deleted773_, document0_7_.active active773_, document0_7_.activatedon activate7_773_, document0_7_.deactivatedon deactiva8_773_, document0_7_.locked locked773_, document0_8_.createdon createdon774_, document0_8_.updatedon updatedon774_, document0_8_.deletedon deletedon774_, document0_8_.deleted deleted774_, document0_8_.active active774_, document0_8_.activatedon activate7_774_, document0_8_.deactivatedon deactiva8_774_, document0_8_.locked locked774_, document0_9_.createdon createdon779_, document0_9_.updatedon updatedon779_, document0_9_.deletedon deletedon779_, document0_9_.deleted deleted779_, document0_9_.active active779_, document0_9_.activatedon activate7_779_, document0_9_.deactivatedon deactiva8_779_, document0_9_.locked locked779_, document0_10_.createdon createdon780_, document0_10_.updatedon updatedon780_, document0_10_.deletedon deletedon780_, document0_10_.deleted deleted780_, document0_10_.active active780_, document0_10_.activatedon activate7_780_, document0_10_.deactivatedon deactiva8_780_, document0_10_.locked locked780_, document0_11_.createdon createdon781_, document0_11_.updatedon updatedon781_, document0_11_.deletedon deletedon781_, document0_11_.deleted deleted781_, document0_11_.active active781_, document0_11_.activatedon activate7_781_, document0_11_.deactivatedon deactiva8_781_, document0_11_.locked locked781_, document0_11_.expirydate expirydate781_, document0_11_.number number781_, document0_11_.createuserid createu12_781_, document0_11_.updateuserid updateu13_781_, document0_12_.createdon createdon784_, document0_12_.updatedon updatedon784_, document0_12_.deletedon deletedon784_, document0_12_.deleted deleted784_, document0_12_.active active784_, document0_12_.activatedon activate7_784_, document0_12_.deactivatedon deactiva8_784_, document0_12_.locked locked784_, document0_12_.createuserid createu10_784_, document0_12_.updateuserid updateu11_784_, document0_12_.surveyid surveyid784_, document0_13_.createdon createdon789_, document0_13_.updatedon updatedon789_, document0_13_.deletedon deletedon789_, document0_13_.deleted deleted789_, document0_13_.active active789_, document0_13_.activatedon activate7_789_, document0_13_.deactivatedon deactiva8_789_, document0_13_.locked locked789_, document0_14_.createdon createdon790_, document0_14_.updatedon updatedon790_, document0_14_.deletedon deletedon790_, document0_14_.deleted deleted790_, document0_14_.active active790_, document0_14_.activatedon activate7_790_, document0_14_.deactivatedon deactiva8_790_, document0_14_.locked locked790_, document0_14_.aboriginal aboriginal790_, document0_14_.disability disability790_, document0_14_.minority minority790_, document0_14_.refuse refuse790_, document0_14_.createuserid createu14_790_, document0_14_.updateuserid updateu15_790_, document0_15_.createdon createdon791_, document0_15_.updatedon updatedon791_, document0_15_.deletedon deletedon791_, document0_15_.deleted deleted791_, document0_15_.active active791_, document0_15_.activatedon activate7_791_, document0_15_.deactivatedon deactiva8_791_, document0_15_.locked locked791_, document0_15_.typeoftraining typeoft10_791_, document0_15_.location location791_, document0_15_.trainername trainer12_791_, document0_15_.[result] result13_791_, document0_15_.trainingdate trainin14_791_, document0_15_.expirydate expirydate791_, document0_15_.createuserid createu16_791_, document0_15_.updateuserid updateu17_791_, document0_16_.createdon createdon792_, document0_16_.updatedon updatedon792_, document0_16_.deletedon deletedon792_, document0_16_.deleted deleted792_, document0_16_.active active792_, document0_16_.activatedon activate7_792_, document0_16_.deactivatedon deactiva8_792_, document0_16_.locked locked792_, document0_16_.createuserid createu10_792_, document0_16_.updateuserid updateu11_792_, document0_17_.createdon createdon793_, document0_17_.updatedon updatedon793_, document0_17_.deletedon deletedon793_, document0_17_.deleted deleted793_, document0_17_.active active793_, document0_17_.activatedon activate7_793_, document0_17_.deactivatedon deactiva8_793_, document0_17_.locked locked793_, document0_17_.content content793_, document0_17_.createuserid createu11_793_, document0_17_.updateuserid updateu12_793_, document0_18_.createdon createdon795_, document0_18_.updatedon updatedon795_, document0_18_.deletedon deletedon795_, document0_18_.deleted deleted795_, document0_18_.active active795_, document0_18_.activatedon activate7_795_, document0_18_.deactivatedon deactiva8_795_, document0_18_.locked locked795_, document0_19_.createdon createdon800_, document0_19_.updatedon updatedon800_, document0_19_.deletedon deletedon800_, document0_19_.deleted deleted800_, document0_19_.active active800_, document0_19_.activatedon activate7_800_, document0_19_.deactivatedon deactiva8_800_, document0_19_.locked locked800_, document0_20_.createdon createdon802_, document0_20_.updatedon updatedon802_, document0_20_.deletedon deletedon802_, document0_20_.deleted deleted802_, document0_20_.active active802_, document0_20_.activatedon activate7_802_, document0_20_.deactivatedon deactiva8_802_, document0_20_.locked locked802_, document0_20_.startdate startdate802_, document0_20_.enddate enddate802_, document0_21_.createdon createdon807_, document0_21_.updatedon updatedon807_, document0_21_.deletedon deletedon807_, document0_21_.deleted deleted807_, document0_21_.active active807_, document0_21_.activatedon activate7_807_, document0_21_.deactivatedon deactiva8_807_, document0_21_.locked locked807_, document0_21_.createuserid createu18_807_, document0_21_.updateuserid updateu19_807_, document0_21_.educationleveltypeid educati21_807_, document0_22_.createdon createdon808_, document0_22_.updatedon updatedon808_, document0_22_.deletedon deletedon808_, document0_22_.deleted deleted808_, document0_22_.active active808_, document0_22_.activatedon activate7_808_, document0_22_.deactivatedon deactiva8_808_, document0_22_.locked locked808_, document0_22_.createuserid createu10_808_, document0_22_.updateuserid updateu11_808_, document0_23_.createdon createdon809_, document0_23_.updatedon updatedon809_, document0_23_.deletedon deletedon809_, document0_23_.deleted deleted809_, document0_23_.active active809_, document0_23_.activatedon activate7_809_, document0_23_.deactivatedon deactiva8_809_, document0_23_.locked locked809_, document0_24_.createdon createdon810_, document0_24_.updatedon updatedon810_, document0_24_.deletedon deletedon810_, document0_24_.deleted deleted810_, document0_24_.active active810_, document0_24_.activatedon activate7_810_, document0_24_.deactivatedon deactiva8_810_, document0_24_.locked locked810_, document0_24_.createuserid createu10_810_, document0_24_.updateuserid updateu11_810_, document0_25_.createdon createdon811_, document0_25_.updatedon updatedon811_, document0_25_.deletedon deletedon811_, document0_25_.deleted deleted811_, document0_25_.active active811_, document0_25_.activatedon activate7_811_, document0_25_.deactivatedon deactiva8_811_, document0_25_.locked locked811_, document0_26_.createdon createdon815_, document0_26_.updatedon updatedon815_, document0_26_.deletedon deletedon815_, document0_26_.deleted deleted815_, document0_26_.active active815_, document0_26_.activatedon activate7_815_, document0_26_.deactivatedon deactiva8_815_, document0_26_.locked locked815_, document0_27_.createdon createdon816_, document0_27_.updatedon updatedon816_, document0_27_.deletedon deletedon816_, document0_27_.deleted deleted816_, document0_27_.active active816_, document0_27_.activatedon activate7_816_, document0_27_.deactivatedon deactiva8_816_, document0_27_.locked locked816_, document0_27_.datereceived daterec10_816_, document0_27_.dateissued dateissued816_, document0_27_.createuserid createu12_816_, document0_27_.updateuserid updateu13_816_, document0_28_.createdon createdon819_, document0_28_.updatedon updatedon819_, document0_28_.deletedon deletedon819_, document0_28_.deleted deleted819_, document0_28_.active active819_, document0_28_.activatedon activate7_819_, document0_28_.deactivatedon deactiva8_819_, document0_28_.locked locked819_, document0_28_.licencedocumentnumber licence10_819_, document0_28_.demeritpoints demerit11_819_, document0_28_.meritpoints meritpo12_819_, document0_28_.expirydate expirydate819_, document0_28_.createuserid createu14_819_, document0_28_.updateuserid updateu15_819_, document0_28_.licencedocumentstatustypeid licence16_819_, document0_28_.stateprovinceid statepr17_819_, document0_29_.createdon createdon820_, document0_29_.updatedon updatedon820_, document0_29_.deletedon deletedon820_, document0_29_.deleted deleted820_, document0_29_.active active820_, document0_29_.activatedon activate7_820_, document0_29_.deactivatedon deactiva8_820_, document0_29_.locked locked820_, document0_29_.createuserid createu10_820_, document0_29_.updateuserid updateu11_820_, document0_30_.createdon createdon821_, document0_30_.updatedon updatedon821_, document0_30_.deletedon deletedon821_, document0_30_.deleted deleted821_, document0_30_.active active821_, document0_30_.activatedon activate7_821_, document0_30_.deactivatedon deactiva8_821_, document0_30_.locked locked821_, document0_30_.expirydate expirydate821_, document0_30_.licencenumber licence11_821_, document0_30_.createuserid createu12_821_, document0_30_.updateuserid updateu13_821_, document0_30_.stateprovinceid statepr14_821_, document0_31_.createdon createdon822_, document0_31_.updatedon updatedon822_, document0_31_.deletedon deletedon822_, document0_31_.deleted deleted822_, document0_31_.active active822_, document0_31_.activatedon activate7_822_, document0_31_.deactivatedon deactiva8_822_, document0_31_.locked locked822_, document0_32_.createdon createdon823_, document0_32_.updatedon updatedon823_, document0_32_.deletedon deletedon823_, document0_32_.deleted deleted823_, document0_32_.active active823_, document0_32_.activatedon activate7_823_, document0_32_.deactivatedon deactiva8_823_, document0_32_.locked locked823_, document0_33_.createdon createdon824_, document0_33_.updatedon updatedon824_, document0_33_.deletedon deletedon824_, document0_33_.deleted deleted824_, document0_33_.active active824_, document0_33_.activatedon activate7_824_, document0_33_.deactivatedon deactiva8_824_, document0_33_.locked locked824_, document0_33_.createuserid createu10_824_, document0_33_.updateuserid updateu11_824_, document0_34_.createdon createdon825_, document0_34_.updatedon updatedon825_, document0_34_.deletedon deletedon825_, document0_34_.deleted deleted825_, document0_34_.active active825_, document0_34_.activatedon activate7_825_, document0_34_.deactivatedon deactiva8_825_, document0_34_.locked locked825_, document0_35_.createdon createdon826_, document0_35_.updatedon updatedon826_, document0_35_.deletedon deletedon826_, document0_35_.deleted deleted826_, document0_35_.active active826_, document0_35_.activatedon activate7_826_, document0_35_.deactivatedon deactiva8_826_, document0_35_.locked locked826_, document0_35_.expirydate expirydate826_, document0_35_.createuserid createu11_826_, document0_35_.updateuserid updateu12_826_, document0_36_.createdon createdon827_, document0_36_.updatedon updatedon827_, document0_36_.deletedon deletedon827_, document0_36_.deleted deleted827_, document0_36_.active active827_, document0_36_.activatedon activate7_827_, document0_36_.deactivatedon deactiva8_827_, document0_36_.locked locked827_, document0_36_.expirydate expirydate827_, document0_36_.createuserid createu11_827_, document0_36_.updateuserid updateu12_827_, document0_37_.createdon createdon829_, document0_37_.updatedon updatedon829_, document0_37_.deletedon deletedon829_, document0_37_.deleted deleted829_, document0_37_.active active829_, document0_37_.activatedon activate7_829_, document0_37_.deactivatedon deactiva8_829_, document0_37_.locked locked829_, document0_37_.checkdate checkdate829_, document0_37_.createuserid createu11_829_, document0_37_.updateuserid updateu12_829_, document0_38_.createdon createdon830_, document0_38_.updatedon updatedon830_, document0_38_.deletedon deletedon830_, document0_38_.deleted deleted830_, document0_38_.active active830_, document0_38_.activatedon activate7_830_, document0_38_.deactivatedon deactiva8_830_, document0_38_.locked locked830_, document0_39_.createdon createdon831_, document0_39_.updatedon updatedon831_, document0_39_.deletedon deletedon831_, document0_39_.deleted deleted831_, document0_39_.active active831_, document0_39_.activatedon activate7_831_, document0_39_.deactivatedon deactiva8_831_, document0_39_.locked locked831_, document0_40_.createdon createdon832_, document0_40_.updatedon updatedon832_, document0_40_.deletedon deletedon832_, document0_40_.deleted deleted832_, document0_40_.active active832_, document0_40_.activatedon activate7_832_, document0_40_.deactivatedon deactiva8_832_, document0_40_.locked locked832_, document0_41_.createdon createdon833_, document0_41_.updatedon updatedon833_, document0_41_.deletedon deletedon833_, document0_41_.deleted deleted833_, document0_41_.active active833_, document0_41_.activatedon activate7_833_, document0_41_.deactivatedon deactiva8_833_, document0_41_.locked locked833_, document0_41_.createuserid createu10_833_, document0_41_.updateuserid updateu11_833_, document0_.subtype subtype715_ 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 document0_.documenttypeid=? , document0_.locked=?
thanks @taballeman, @ionic et al. comments helped me realize calculation of execution plan sucking many resources. understanding led me find this question asking execution plans taking long time generate in sql server 2014.
i should have mentioned using sql server 2014 turns out version introduced aggressive new query optimizer has trouble query in question. if set database compatibility sql server 2012, execution plan generated immediately.
i may still log bug in sql connect query may represent edge case needs looking into.
thanks all.
Comments
Post a Comment