R

upgraded one of our servers to sql 2005 SP1 a week ago and we have been troubleshooting performance problems ever since.

so far we have narrowed it down to 2 queries.

this one spikes our 8 900MHz CPU Compaq server to 100% for 10 minutes at a time.

runs in seconds on sql 2000 on much older and slower hardware. sql 2k execution plan is all index seeks on the right indexes and sql 2005 is an index scan on the PK.

MS is saying it's by design, but i've never seen an index scan do this.

they said that they changed the optimizer to do this to avoid stack overflows at high usage.

select ordUidID,ordDtmUpdated,ordDtmCreated,ordUidParentOrder,ordCdeOrderType,ordUidCustomer,ordYnNeedsReview,ordYnCanArchive,ordCdeLEC,ordVchPON,

ordVchVersion,ordCdeTranClass,ordVchTranType,ordVchLastTran,ordTnBTN,ordTnWTN,ordSetSynchronization,ordCdeFinalResult,ordCdeStatus,ordVchFileName,

ordDtmOrdered,ordDtmClosed,ordDteDueDateRequested,ordDteDueDateAssigned,ordDteInstalled,ordIntConnectionUsed,ordVchLastMessage,ordDtmMiscDate0,

ordDtmMiscDate1,ordDtmMiscDate2,ordDtmMiscDate3,ordCdeMiscCode0,ordCdeMiscCode1,ordCdeMiscCode2,ordCdeMiscCode3,ordVchMiscData0,ordVchMiscData1,

ordVchMiscData2,ordVchMiscData3,ordVchMiscData4,ordVchMiscData5,ordVchMiscData6,ordVchMiscData7,ordVchUserID,ordCdeUserType,ordUIDIncidentID,

ordVchIncidentType,ordIntRecordUpdateCount,ordIntRecordProcessedCount,ordVchSubTrans,ordUidResellerId from tblLECOrder where ( ordTnBTN in ('000WLK0000','2122283937','2122344229','2122810515','2122818210','2122837617','2122858679','2122893175','2123162861','2123693789','2124107190','2124263225',

'2124278119','2125295465','2125343989','2125447195','2125671073','2125677808','2125678098','2125680722','2125683939','2125684463','2125687396','2125687508','

2125689745','2125689820','2126638480','2126784974','2126904871','2127228356','2127243395','2127813907','2127816515','2127817636','2127955370','2127955862',

'2127957834','2129239577','2129270685','2129273060','2129274728','2129275933','2129276137','2129276468','2129276845','2129277476','2129278126','2129279249',

'2129424067','2129873669','2129879069','2152210772','2152280240','2152321190','2152711041','2152882261','2152910690','2152919646','2153240243','2153333119',

'2154235057','2154235142','2154239175','2154239616','2154250293','2154266674','2154267635','2154268312','2154269081','2154271883','2154571323','2154572414','

2154688652','2156340253','2156342734','2157393712','2157441452','3155364195','5162920290','5163331988','5163778609','5164884993','5165055606','5165360859',

'5165381376','5165384630','5165466810','5165617488','5166239609','5168295375','5184894605','5184922523','5184991914','6102688232','6102728614','6102775286',

'6103721456','6103723790','6103724604','6103729470','6103732079','6103732752','6103735651','6103742625','6103754045','6103765831','6103766395','6104324238',

'6104325988','6104327258','6104335863','6104346703','6104372198','6104391609','6104445140','6104448194','6107749810','6108207582','6108612952','6108662412',

'6108697936','6108699214','6109250219','6109250316','6313852195','6314211097','6314231374','6314778227','6314778291','6315491328','6315493804','6315494720',

'6315498235','6316736552','6316738351','6317544591','6318594984','7168530368','7168562635','7182200643','7182201079','7182203949','7182206032','7182206124',

'7182209706','7182252502','7182353598','7182381315','7182383716','7182520828','7182560894','7182630419','7182711657','7182743419','7182770168','7182772714',

'7182774379','7182774726','7182775216','7182930531','7182931421','7182932314','7182934615','7182935547','7182936379','7182936489','7182944188','7182950607',

'7182953419','7182954264','7182955065','7182955913','7182957192','7182957980','7182967315','7183222109','7183287582','7183293615','7183299730','7183330763',

'7183493722','7183533459','7183645849','7183646794','7183647088','7183647571','7183657547','7183664851','7183672666','7183679345','7183780195','7183783144',

'7183784405','7183785086','7183786437','7183795271','7183820524','7183833545','7183860947','7183863787','7183971152','7184100414','7184102691','7184181232',

'7184184534','7184186657','7184188365','7184188485','7184189682','7184244219','7184260613','7184263054','7184297079','7184352078','7184415451','7184431673',

'7184431978','7184438072','7184452145','7184461815','7184531441','7184533981','7184534089','7184538743','7184553469','7184563259','7184563916','7184763834',

'7184865457','7184866489','7184920615','7184922149','7184971269','7184971876','7184975709','7184977375','7185058171','7185196413','7185196550','7185233417',

'7185238042','7185258323','7185263859','7185265019','7185266713','7185271890','7185280091','7185291314','7185339073','7185372582','7185378203','7185381866',

'7185382967','7185383271','7185383694','7185383794','7185385027','7185387693','7185388697','7185389418','7185392568','7185421930','7185423664','7185426571',

'7185427232','7185428637','7185428742','7185428953','7185430852','7185450174','7185470274','7185475970','7185476028','7185478451','7185491560','7185610521',

'7185611519','7185613654','7185615696','7185615863','7185617340','7185618094','7185618219','7185618486','7185621326','7185621640','7185629364','7185629692',

'7185632531','7185633531','7185733564','7185746632','7185844011','7185844746','7185844836','7185881074','7185886683','7185889899','7185890125','7185894623',

'7185896208','7185900541','7185900828','7185904091','7185904858','7185994193','7186090497','7186171937','7186172711','7186205203','7186332470','7186347884',

'7186392938','7186451907','7186472645','7186472713','7186477636','7186521715','7186522908','7186552248','7186558625','7186682396','7186683433','7186686857',

'7186689161','7186689547','7186711542','7186720486','7186721229','7186721434','7186721469','7186721952','7186722551','7186722592','7186722870','7186723055',

'7186723233','7186724169','7186724835','7186724975','7186725062','7186725799','7186725920','7186726040','7186726621','7186726740','7186726948','7186728309',

'7186728420','7186729408','7186729697','7186756401','7186756779','7186756942','7186771445','7186773330','7186777645','7186778437','7186779455','7186800316',

'7186802749','7186803348','7186803405','7186804038','7186805162','7186805387','7186805459','7186805590','7186806846','7186807136','7186808476','7186808816',

'7186810934','7186811454','7186811648','7186812053','7186812065','7186813478','7186814281','7186815360','7186815361','7186815575','7186816570','7186818268',

'7186818325','7186818403','7186819353','7186861065','7186861543','7186862969','7186866117','7186866617','7186866937','7186866947','7186867179','7186867334',

'7186920497','7186922776','7186927257','7186932954','7186937158','7186949409','7186980457','7186980920','7186980940','7186981811','7186984233','7186986257',

'7186986612','7186987684','7186990841','7186990905','7186991184','7186991329','7186992379','7186992734','7186992876','7186994389','7186995289','7186995341',

'7186997162','7186997361','7186999147','7187034172','7187067578','7187068410','7187068465','7187068471','7187141153','7187144035','7187144291','7187146328',

'7187146758','7187146810','7187149845','7187161933','7187161941','7187162663','7187165245','7187165380','7187206825','7187206887','7187207200','7187207345',

'7187207350','7187210139','7187210350','7187210458','7187210608','7187210643','7187210949','7187211269','7187211638','7187211805','7187211810','7187211904',

'7187211938','7187212099','7187212723','7187214662','7187214693','7187214974','7187215018','7187215202','7187215260','7187215726','7187216790','7187217078',

'7187217415','7187217652','7187230780','7187260513','7187261057','7187261364','7187261847','7187262398','7187262519','7187262573','7187262870','7187264887',

'7187265185','7187265424','7187265551','7187266259','7187266489','7187268008','7187271050','7187277766','7187278594','7187280271','7187280664','7187281954',

'7187282837','7187282877','7187283342','7187284730','7187285104','7187285215','7187285669','7187286478','7187287353','7187287542','7187287593','7187287825',

'7187288005','7187288372','7187288686','7187288808','7187290106','7187291778','7187293062','7187293310','7187293841','7187295612','7187295869','7187297627',

'7187299179','7187299911','7187310861','7187312426','7187313202','7187314421','7187317164','7187317694','7187331410','7187331609','7187332806','7187333146',

'7187333536','7187333765','7187334323','7187334420','7187335228','7187337393','7187337642','7187338725','7187382564','7187382750','7187383647','7187384962',

'7187386413','7187387307','7187388430','7187389395','7187393233','7187397938','7187400244','7187400271','7187401751','7187422142','7187424409','7187426079',

'7187429032','7187429353','7187430854','7187431763','7187431944','7187432321','7187432587','7187432612','7187432646','7187433542','7187433646','7187434553',

'7187435268','7187435814','7187436289','7187436603','7187436874','7187437597','7187438465','7187438527','7187439834','7187450063','7187450844','7187451504',

'7187451728','7187451821','7187453029','7187453212','7187453629','7187453861','7187453964','7187454062','7187454287','7187454354','7187454674','7187455407',

'7187455579','7187456854','7187456970','7187457167','7187457320','7187457946','7187458410','7187460042','7187460871','7187460962','7187461205','7187461252',

'7187463048','7187463553','7187464128','7187464172','7187464384','7187465007','7187465669','7187465760','7187466080','7187468291','7187469464','7187473042',

'7187475607','7187476117','7187476139','7187480944','7187481208','7187482091','7187482464','7187483068','7187483147','7187483659','7187483796','7187483952',

'7187485133','7187486602','7187489896','7187520567','7187521815','7187529750','7187568064','7187590580','7187590925','7187591230','7187599425','7187599662',

'7187600238','7187600614','7187600733','7187600849','7187600873','7187601138','7187601334','7187601527','7187601549','7187601895','7187602377','7187602868',

'7187603285','7187603550','7187603876','7187604804','7187604845','7187605728','7187607406','7187607473','7187608939','7187613931','7187614612','7187615683',

'7187615717','7187616184','7187617711','7187618557','7187620539','7187622317','7187625542','7187628146','7187633771','7187635291','7187650203','7187650597',

'7187670343','7187670370','7187671309','7187671376','7187671516','7187672036','7187672363','7187672473','7187672773','7187672896','7187673849','7187674365',

'7187676549','7187679158','7187681055','7187682238','7187682254','7187687428','7187690619','7187690849','7187691545','7187692238','7187692502','7187693026',

'7187693125','7187693569','7187693707','7187693771','7187694311','7187694751','7187697063','7187697380','7187697880','7187698011','7187698379','7187698981',

'7187699471','7187714204','7187719081','7187730463','7187740128','7187762371','7187770041','7187770758','7187771640','7187771692','7187772414','7187772781',

'7187772868','7187773933','7187775504','7187775560','7187775724','7187775866','7187777576','7187780477','7187781954','7187790469','7187790618','7187790847',

'7187790865','7187791095','7187792267','7187792367','7187792948','7187793777','7187793872','7187794198','7187794582','7187794615','7187794960','7187795132',

'7187795222','7187795508','7187796043','7187796415','7187796844','7187797622','7187797681','7187797758','7187797835','7187798578','7187798794','7187798952',

'7187799293','7187820677','7187820716','7187820757','7187821174','7187821493','7187822103','7187822479','7187822492','7187822540','7187822702','7187823024',

'7187823204','7187823405','7187823581','7187823668','7187823989','7187824599','7187824778','7187825558','7187826351','7187826894','7187826983','7187827057',

'7187827815','7187827862','7187828313','7187829749','7187829884','7187830214','7187830643','7187838148','7187841041','7187842714','7187842877','7187860154',

'7187860348','7187861638','7187861863','7187861990','7187863444','7187864397','7187868894','7187870959','7187871492','7187880717','7187883321','7187884417',

'7187887402','7187889092','7187892980','7187895061','7187895240','7187895769','7187923459','7187924158','7187924204','7187926134','7187926760','7187929594',

'7187930707','7187933729','7187935623','7187935874','7187936547','7187939188','7187949210','7187960476','7187962195','7187968979','7187970769','7187971374',

'7187975362','7187983651','7187983994','7187984130','7187984495','7187989773','7188030981','7188031494','7188031811','7188032538','7188032905','7188033271',

'7188033606','7188033753','7188038130','7188050721','7188051083','7188051246','7188051305','7188051359','7188051875','7188054625','7188055706','7188057438',

'7188057519','7188150474','7188159197','7188161327','7188165502','7188167467','7188169894','7188180836','7188200638','7188211316','7188211496','7188211845',

'7188211878','7188211913','7188212220','7188212592','7188213779','7188214285','7188214610','7188214615','7188215109','7188215236','7188215646','7188216180',

'7188216483','7188216485','7188218034','7188218915','7188219076','7188220092','7188222805','7188225839','7188226323','7188230537','7188234855','7188236617',

'7188240437','7188241859','7188245305','7188260628','7188262346','7188263856','7188270971','7188271302','7188272895','7188273061','7188273870','7188274792',

'7188275066','7188275605','7188275716','7188276649','7188277330','7188277350','7188278145','7188278293','7188278323','7188278492','7188280950','7188285516',

'7188285973','7188288197','7188292735','7188293942','7188297135','7188298399','7188299337','7188300715','7188320402','7188321706','7188321916','7188321926',

'7188322075','7188322262','7188323006','7188323319','7188323354','7188324683','7188324870','7188325182','7188326340','7188329048','7188331311','7188331714',

'7188331730','7188331742','7188332505','7188333556','7188333610','7188333736','7188334762','7188336049','7188336374','7188338980','7188346173','7188351089',

'7188352162','7188352666','7188353017','7188354168','7188360337','7188360435','7188360917','7188361221','7188361278','7188361587','7188364811','7188367087',

'7188367642','7188368733','7188369328','7188369418','7188370515','7188371222','7188371429','7188371963','7188372318','7188372461','7188373207','7188374629',

'7188375153','7188375708','7188375788','7188378334','7188378631','7188378678','7188379737','7188379850','7188420088','7188420108','7188420487','7188421012',

'7188421940','7188422392','7188423238','7188423841','7188424418','7188425904','7188426274','7188427455','7188428037','7188428341','7188433641','7188433675',

'7188435308','7188435869','7188436064','7188436720','7188439150','7188451248','7188452660','7188452896','7188453751','7188455377','7188457478','7188458214',

'7188460012','7188460153','7188460335','7188460478','7188460793','7188462324','7188462862','7188463861','7188463905','7188464068','7188464123','7188464589',

'7188464839','7188464912','7188464983','7188465257','7188465355','7188465383','7188465390','7188465479','7188467469','7188467561','7188467943','7188470216',

'7188470403','7188470427','7188471608','7188472683','7188472734','7188472842','7188473326','7188474837','7188475347','7188477441','7188478426','7188478513',

'7188478664','7188478881','7188479756','7188479790','7188479921','7188479966','7188480420','7188481534','7188483836','7188491672','7188491721','7188492080',

'7188492801','7188492812','7188492904','7188494752','7188495512','7188497501','7188499599','7188500283','7188501571','7188502036','7188502046','7188502162','7188502915','7188503409','7188503490','7188506568','7188507608','7188508397','7188510494','7188512384','7188512517','7188512752','7188512775','7188513642','7188513659','7188513966','7188514081','7188514201','7188514567','7188514652','7188514756','7188516488','7188517267','7188520478','7188522314','7188523971','7188524628','7188525619','7188526316','7188527171','7188527903','7188528148','7188530195','7188530207','7188530519','7188531095','7188531825','7188532481','7188533620','7188533809','7188534256','7188534346','7188535269','7188535693','7188535846','7188536243','7188536264','7188536374','7188536626','7188536787','7188537175','7188537215','7188538059','7188538090','7188538206','7188538298','7188538749','7188538787','7188538804','7188538870','7188539238','7188539773','7188540693','7188540809','7188540910','7188540972','7188541032','7188541275','7188541328','7188541434','7188542399','7188543016','7188543290','7188543545','7188543699','7188543961','7188544302','7188544428','7188544567','7188544745','7188546417','7188547687','7188548831','7188549408','7188552037','7188555438','7188560453','7188561870','7188563263','7188566710','7188568218','7188568343','7188568547','7188571368','7188590157','7188592193','7188592547','7188592974','7188593932','7188595748','7188597452','7188597627','7188599351','7188599774','7188600033','7188600721','7188600815','7188601038','7188601292','7188604393','7188604940','7188606172','7188606349','7188606724','7188606919','7188611747','7188611982','7188613718','7188614261','7188618091','7188618488','7188618711','7188619607','7188630365','7188630429','7188630857','7188632954','7188635415','7188636764','7188637062','7188637288','7188637596','7188637637','7188638440','7188639551','7188680267','7188710727','7188710817','7188711820','7188712965','7188713314','7188713874','7188716101','7188716296','7188717109','7188717438','7188718310','7188753424','7188754140','7188757634','7188760654','7188765210','7188765337','7188810912','7188813925','7188818175','7188824340','7188826326','7188841344','7188842157','7188845525','7188860274','7188860707','7188861957','7188862130','7188862730','7188863048','7188865992','7188869528','7188880096','7188889015','7188911470','7188911724','7188912513','7188913924','7188914378','7188914489','7188914526','7188915048','7188915266','7188916510','7188917352','7188918005','7188918473','7188918491','7188919282','7188919420','7188919452','7188920591','7188922806','7188922867','7188925345','7188929381','7188930361','7188930765','7188930769','7188930781','7188931739','7188934587','7188936794','7188936844','7188936920','7188937595','7188937987','7188939204','7188940023','7188940245','7188940583','7188941210','7188942021','7188942576','7188942739','7188944359','7188944923','7188945419','7188945542','7188947098','7188962071','7188964074','7188965090','7188965674','7188968370','7188971265','7188973159','7188973325','7188973396','7188973472','7188973791','7188973798','7188974660','7188974948','7188976532','7188979451','7188980374','7188980469','7188980983','7188981086','7188981279','7188982197','7188982843','7188983040','7188983432','7188983705','7188983752','7188985279','7188985767','7188986009','7188986040','7188986610','7188987079','7188987865','7188988085','7188988198','7188988573','7188988864','7188989665','7188990103','7188990119','7188990156','7188990751','7188991615','7188992085','7188993414','7188994676','7188995413','7188996403','7188997089','7188997463','7188998036','7188998062','7188999089','7188999457','7188999616','7189010817','7189010869','7189010950','7189011842','7189012184','7189013604','7189015201','7189015670','7189040568','7189180251','7189190849','7189191573','7189191811','7189192171','7189192762','7189193202','7189193619','7189195840','7189198817','7189211632','7189213149','7189215906','7189216319','7189221817','7189311472','7189312569','7189314304','7189314578','7189314783','7189315109','7189320273','7189320839','7189321812','7189321813','7189321823','7189323181','7189323583','7189323947','7189324076','7189324711','7189325322','7189325424','7189325629','7189327421','7189329017','7189330115','7189330291','7189333740','7189336140','7189336406','7189337682','7189338394','7189339274','7189339377','7189340348','7189340996','7189341326','7189341345','7189341549','7189342218','7189342516','7189342833','7189342979','7189343378','7189344788','7189346184','7189346884','7189347947','7189348049','7189348614','7189371547','7189371984','7189372573','7189374692','7189376179','7189390029','7189390987','7189391505','7189393244','7189393318','7189395509','7189395651','7189396250','7189396727','7189400327','7189400688','7189401746','7189402512','7189404481','7189404650','7189412860','7189414356','7189414503','7189417093','7189417241','7189422285','7189422525','7189422650','7189430175','7189430196','7189430199','7189430288','7189430314','7189430371','7189430372','7189435996','7189436410','7189437530','7189450538','7189451102','7189451257','7189451832','7189453608','7189453710','7189455023','7189455172','7189455865','7189456189','7189457131','7189457501','7189457510','7189459661','7189459708','7189460560','7189461476','7189462124','7189462189','7189462805','7189463256','7189463486','7189463656','7189464256','7189464494','7189464791','7189466104','7189467038','7189467307','7189468229','7189470546','7189470582','7189470585','7189470597','7189470605','7189470607','7189470613','7189470615','7189470628','7189470642','7189470644','7189470658','7189470669','7189470675','7189470682','7189470698','7189470710','7189470721','7189470727','7189470728','7189470730','7189470732','7189470733','7189470749','7189470751','7189470752','7189470757','7189470758','7189470760','7189470762','7189470767','7189470780','7189470783','7189470786','7189470794','7189470796','7189470797','7189470807','7189470826','7189470828','7189470830','7189470834','7189470838','7189470840','7189470841','7189470843','7189470845','7189470873','7189470876','7189470884','7189470887','7189470889','7189470907','7189470916','7189470925','7189470926','7189470928','7189470932','7189470936','7189470939','7189470957','7189470963','7189470964','7189470969','7189471052','7189471057','7189471058','7189471059','7189471082','7189471098','7189471105','7189471138','7189471145','7189471151','7189471154','7189471244','7189471251','7189471255','7189471261','7189471262','7189471275','7189471278','7189471282','7189471292','7189471328','7189471362','7189471363','7189471364','7189471365','7189471386','7189471392','7189471393','7189471422','7189471433','7189471461','7189471464','7189471491','7189471494','7189471570','7189471606','7189471765','7189485548','7189486706','7189492489','7189494656','7189511069','7189512276','7189512277','7189517838','7189518087','7189518760','7189518884','7189518984','7189519303','7189519836','7189530975','7189560093','7189560231','7189560238','7189560320','7189560503','7189561005','7189561047','7189561048','7189561272','7189561348','7189561556','7189561794','7189562168','7189563981','7189564479','7189564486','7189564528','7189565627','7189565739','7189565788','7189565843','7189566347','7189566542','7189567186','7189567558','7189568321','7189568330','7189568564','7189569490','7189610502','7189612956','7189619150','7189630168','7189630788','7189631570','7189632013','7189632074','7189632976','7189633192','7189633751','7189633822','7189634004','7189639794','7189650315','7189651103','7189651884','7189652083','7189661111','7189662418','7189665849','7189667528','7189687057','7189688964','7189689587','7189690185','7189690885','7189692709','7189693115','7189693601','7189699311','7189721139','7189721219','7189721390','7189721450','7189721459','7189722897','7189722954','7189722960','7189723002','7189723146','7189723182','7189723779','7189723819','7189723829','7189724498','7189724815','7189725504','7189726549','7189727009','7189727643','7189728304','7189792450','7189793350','7189794053','7189794743','7189798380','7189800198','7189800622','7189800792','7189805913','7189811154','7189812789','7189813418','7189815888','7189820166','7189825750','7189827859','7189828035','7189829217','7189831741','7189838329','7189838380','7189841062','7189842845','7189846914','7189848552','7189870577','7189874293','7189875789','7189876222','7189879213','7189879681','7189911639','7189912579','7189913073','7189913094','7189913873','7189915321','7189915984','7189916941','7189918551','7189919151','7189920641','7189920735','7189921121','7189921509','7189921734','7189921834','7189922143','7189923580','7189924081','7189925319','7189925891','7189925905','7189928280','7189929503','7189930243','7189930975','7189933109','7189934464','7189934547','7189934572','7189935734','7189937486','7189944713','7189946524','7189960318','7189960545','7189960549','7189960625','7189960729','7189961235','7189961586','7189962444','7189962713','7189963977','7189964152','7189964676','7189965292','7189965389','7189966035','7189966784','7189967140','7189968101','7189968183','7189968213','7189968741','7189970170','7189971087','7189976952','7189977314','7189979058','7189979624','7189981124','7189982008','7189982855','7189982959','7189986214','7189988389','7189988460','8452259089','8452468943','8452791399','8452925045','8453521785','8453522864','8453523813','8453524211','8453524484','8453527924','8453528034','8453528172','8453562773','8453565029','8453567444','8453580273','8453580760','8453585911','8453712440','8453712620','8453716119','8454250613','8454251421','8454254079','8454254784','8454254854','8454261269','8454262599','8454266457','8454267459','8454291796','8454340773','8454341805','8454344789','8454542436','8454625953','8454837658','8455284094','8455343415','8455616294','8455651283','8455666970','8455690205','8455697032','8455730144','8456213863','8456265009','8456265515','8456271377','8456473742','8456474340','8457271173','8457277745','8457532472','8457866080','8457867348','8457886780','8457916472','8457918483','8457940405','8458964868','8459477094','9142321846','9142351023','9142355342','9142356043','9142378590','9142411651','9142412108','9142412336','9142412908','9142414382','9142421759','9142500167','9142500271','9142500272','9142713418','9142767666','9142767667','9143321390','9143324572','9143324785','9143328172','9143371090','9143472664','9143750640','9143750681','9143752606','9143752878','9143752980','9143755930','9143756064','9143759018','9143761319','9143761966','9143763954','9143765804','9143765899','9143767153','9143769526','9143789171','9143817430','9144222671','9144233363','9144233917','9144234068','9144234593','9144234721','9144234761','9144237450','9144237869','9144725032','9144725297','9144763768','9144764828','9144769317','9144769580','9144769844','9144780514','9144783532','9144785083','9144787557','9144791298','9145240559','9145280867','9145286518','9145762553','9145763182','9145763890','9145763975','9145764004','9145765023','9145765828','9145768225','9145769186','9145919586','9146311708','9146322303','9146324887','9146328092','9146329447','9146331515','9146333927','9146360297','9146541848','9146632612','9146643301','9146650671','9146657046','9146678126','9146684242','9146684762','9146830825','9146841842','9146847041','9146869786','9146901033','9146909817','9146931130','9146931542','9146982426','9146987134','9147090115','9147120857','9147349202','9147362948','9147367630','9147369786','9147371431','9147378154','9147379064','9147382217','9147388319','9147391652','9147398481','9147472024','9147473258','9147477573','9147612357','9147620328','9147623299','9147631377','9147632100','9147693136','9147773590','9147885645','9148332108','9149210097','9149229331','9149237593','9149238364','9149340694','9149342309','9149349245','9149375812','9149379796','9149390280','9149391344','9149399174','9149399237','9149417746','9149447059','9149464715','9149485639','9149492004','9149493137','9149615666','9149629652','9149632051','9149636257','9149650453','9149650712','9149652521','9149653445','9149663621','9149680803','9149682051','9149682130','9149682392','9149684868','9149687023','9149690418','9149690661','9149693021','9149693852','9149695716','9173268901','9174922134','9174929446','9175211440','9175212118','9175212147','9175217228','9175217252','NEW1007818','NEW1009805','NEW1009942','NEW1009946','NEW1010040','NEW1010090') or ordUidCustomer = 277173336)

I have seen similar performance problems in 2005 when using huge lists of IN values in the where clause. I am not able to change the SQL or the length of the list because it is generated by a report writer and I have no control over what the user selects.

Try 2005 SP2 (on a test machine) and see if that helps.
|||

Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.

Dan

|||SQL2005 do have performance problem for IN and NOT IN. Here is another thread talking about this issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

You can use left join or right join to implement same behavior with IN and NOT IN. That will improve your query performance.
|||

>>Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.<<

This is what I was thinking. I have a query like this but it passes it as a delimited an I run a split function on it like this:

Code Snippet

declare @.listIds varchar(max)
set @.listIds = '1,2,3,4,5,6,7,8,9'

;with digits as(
select 1 as i union all select 2 as i union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 0),

sequence as (
SELECT distinct D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i) + (100000*D6.i) as i
FROM digits AS D1, digits AS D2,
digits AS D3,digits as D4, digits as D5, digits as D6
),
list as (
SELECT substring(',' + @.listIds + ',', i + 1,
charindex(',', ',' + @.listIds + ',', i + 1) - i - 1) AS responseActivityWorkId
FROM sequence
WHERE i <= len(',' + @.listIds + ',') - 1
AND substring(',' + @.listIds + ',', i, 1) = ',')
select *
from list

The select * from list would just be replaced by your query with a join to this list CTE, and remove the IN (big old list). Then format the list as 'value,value,value' and go for it. I support 999999 as the max length of the parameter, but just adding a D7 (following the pattern) and you could go even higher. (I would like to do your data, but the rotation around the screen would take me a long time to reformat

If you have CTE performance issues, just put the results of the list query into a temp table, add an index and try that as a join or IN clause. Then it should be as fast as you can get then.

If you would like to read more about the subject of arrays and lists in SQL Server, please try the excellent article: http://www.sommarskog.se/arrays-in-sql-2005.html

|||

they got around this by using temp tables. i tried this on some new 64 bit servers we just bought with 20GB RAM and same result. 100% CPU spike but execution time is around 30 seconds instead of 10 minutessql

R

upgraded one of our servers to sql 2005 SP1 a week ago and we have been troubleshooting performance problems ever since.

so far we have narrowed it down to 2 queries.

this one spikes our 8 900MHz CPU Compaq server to 100% for 10 minutes at a time.

runs in seconds on sql 2000 on much older and slower hardware. sql 2k execution plan is all index seeks on the right indexes and sql 2005 is an index scan on the PK.

MS is saying it's by design, but i've never seen an index scan do this.

they said that they changed the optimizer to do this to avoid stack overflows at high usage.

select ordUidID,ordDtmUpdated,ordDtmCreated,ordUidParentOrder,ordCdeOrderType,ordUidCustomer,ordYnNeedsReview,ordYnCanArchive,ordCdeLEC,ordVchPON,

ordVchVersion,ordCdeTranClass,ordVchTranType,ordVchLastTran,ordTnBTN,ordTnWTN,ordSetSynchronization,ordCdeFinalResult,ordCdeStatus,ordVchFileName,

ordDtmOrdered,ordDtmClosed,ordDteDueDateRequested,ordDteDueDateAssigned,ordDteInstalled,ordIntConnectionUsed,ordVchLastMessage,ordDtmMiscDate0,

ordDtmMiscDate1,ordDtmMiscDate2,ordDtmMiscDate3,ordCdeMiscCode0,ordCdeMiscCode1,ordCdeMiscCode2,ordCdeMiscCode3,ordVchMiscData0,ordVchMiscData1,

ordVchMiscData2,ordVchMiscData3,ordVchMiscData4,ordVchMiscData5,ordVchMiscData6,ordVchMiscData7,ordVchUserID,ordCdeUserType,ordUIDIncidentID,

ordVchIncidentType,ordIntRecordUpdateCount,ordIntRecordProcessedCount,ordVchSubTrans,ordUidResellerId from tblLECOrder where ( ordTnBTN in ('000WLK0000','2122283937','2122344229','2122810515','2122818210','2122837617','2122858679','2122893175','2123162861','2123693789','2124107190','2124263225',

'2124278119','2125295465','2125343989','2125447195','2125671073','2125677808','2125678098','2125680722','2125683939','2125684463','2125687396','2125687508','

2125689745','2125689820','2126638480','2126784974','2126904871','2127228356','2127243395','2127813907','2127816515','2127817636','2127955370','2127955862',

'2127957834','2129239577','2129270685','2129273060','2129274728','2129275933','2129276137','2129276468','2129276845','2129277476','2129278126','2129279249',

'2129424067','2129873669','2129879069','2152210772','2152280240','2152321190','2152711041','2152882261','2152910690','2152919646','2153240243','2153333119',

'2154235057','2154235142','2154239175','2154239616','2154250293','2154266674','2154267635','2154268312','2154269081','2154271883','2154571323','2154572414','

2154688652','2156340253','2156342734','2157393712','2157441452','3155364195','5162920290','5163331988','5163778609','5164884993','5165055606','5165360859',

'5165381376','5165384630','5165466810','5165617488','5166239609','5168295375','5184894605','5184922523','5184991914','6102688232','6102728614','6102775286',

'6103721456','6103723790','6103724604','6103729470','6103732079','6103732752','6103735651','6103742625','6103754045','6103765831','6103766395','6104324238',

'6104325988','6104327258','6104335863','6104346703','6104372198','6104391609','6104445140','6104448194','6107749810','6108207582','6108612952','6108662412',

'6108697936','6108699214','6109250219','6109250316','6313852195','6314211097','6314231374','6314778227','6314778291','6315491328','6315493804','6315494720',

'6315498235','6316736552','6316738351','6317544591','6318594984','7168530368','7168562635','7182200643','7182201079','7182203949','7182206032','7182206124',

'7182209706','7182252502','7182353598','7182381315','7182383716','7182520828','7182560894','7182630419','7182711657','7182743419','7182770168','7182772714',

'7182774379','7182774726','7182775216','7182930531','7182931421','7182932314','7182934615','7182935547','7182936379','7182936489','7182944188','7182950607',

'7182953419','7182954264','7182955065','7182955913','7182957192','7182957980','7182967315','7183222109','7183287582','7183293615','7183299730','7183330763',

'7183493722','7183533459','7183645849','7183646794','7183647088','7183647571','7183657547','7183664851','7183672666','7183679345','7183780195','7183783144',

'7183784405','7183785086','7183786437','7183795271','7183820524','7183833545','7183860947','7183863787','7183971152','7184100414','7184102691','7184181232',

'7184184534','7184186657','7184188365','7184188485','7184189682','7184244219','7184260613','7184263054','7184297079','7184352078','7184415451','7184431673',

'7184431978','7184438072','7184452145','7184461815','7184531441','7184533981','7184534089','7184538743','7184553469','7184563259','7184563916','7184763834',

'7184865457','7184866489','7184920615','7184922149','7184971269','7184971876','7184975709','7184977375','7185058171','7185196413','7185196550','7185233417',

'7185238042','7185258323','7185263859','7185265019','7185266713','7185271890','7185280091','7185291314','7185339073','7185372582','7185378203','7185381866',

'7185382967','7185383271','7185383694','7185383794','7185385027','7185387693','7185388697','7185389418','7185392568','7185421930','7185423664','7185426571',

'7185427232','7185428637','7185428742','7185428953','7185430852','7185450174','7185470274','7185475970','7185476028','7185478451','7185491560','7185610521',

'7185611519','7185613654','7185615696','7185615863','7185617340','7185618094','7185618219','7185618486','7185621326','7185621640','7185629364','7185629692',

'7185632531','7185633531','7185733564','7185746632','7185844011','7185844746','7185844836','7185881074','7185886683','7185889899','7185890125','7185894623',

'7185896208','7185900541','7185900828','7185904091','7185904858','7185994193','7186090497','7186171937','7186172711','7186205203','7186332470','7186347884',

'7186392938','7186451907','7186472645','7186472713','7186477636','7186521715','7186522908','7186552248','7186558625','7186682396','7186683433','7186686857',

'7186689161','7186689547','7186711542','7186720486','7186721229','7186721434','7186721469','7186721952','7186722551','7186722592','7186722870','7186723055',

'7186723233','7186724169','7186724835','7186724975','7186725062','7186725799','7186725920','7186726040','7186726621','7186726740','7186726948','7186728309',

'7186728420','7186729408','7186729697','7186756401','7186756779','7186756942','7186771445','7186773330','7186777645','7186778437','7186779455','7186800316',

'7186802749','7186803348','7186803405','7186804038','7186805162','7186805387','7186805459','7186805590','7186806846','7186807136','7186808476','7186808816',

'7186810934','7186811454','7186811648','7186812053','7186812065','7186813478','7186814281','7186815360','7186815361','7186815575','7186816570','7186818268',

'7186818325','7186818403','7186819353','7186861065','7186861543','7186862969','7186866117','7186866617','7186866937','7186866947','7186867179','7186867334',

'7186920497','7186922776','7186927257','7186932954','7186937158','7186949409','7186980457','7186980920','7186980940','7186981811','7186984233','7186986257',

'7186986612','7186987684','7186990841','7186990905','7186991184','7186991329','7186992379','7186992734','7186992876','7186994389','7186995289','7186995341',

'7186997162','7186997361','7186999147','7187034172','7187067578','7187068410','7187068465','7187068471','7187141153','7187144035','7187144291','7187146328',

'7187146758','7187146810','7187149845','7187161933','7187161941','7187162663','7187165245','7187165380','7187206825','7187206887','7187207200','7187207345',

'7187207350','7187210139','7187210350','7187210458','7187210608','7187210643','7187210949','7187211269','7187211638','7187211805','7187211810','7187211904',

'7187211938','7187212099','7187212723','7187214662','7187214693','7187214974','7187215018','7187215202','7187215260','7187215726','7187216790','7187217078',

'7187217415','7187217652','7187230780','7187260513','7187261057','7187261364','7187261847','7187262398','7187262519','7187262573','7187262870','7187264887',

'7187265185','7187265424','7187265551','7187266259','7187266489','7187268008','7187271050','7187277766','7187278594','7187280271','7187280664','7187281954',

'7187282837','7187282877','7187283342','7187284730','7187285104','7187285215','7187285669','7187286478','7187287353','7187287542','7187287593','7187287825',

'7187288005','7187288372','7187288686','7187288808','7187290106','7187291778','7187293062','7187293310','7187293841','7187295612','7187295869','7187297627',

'7187299179','7187299911','7187310861','7187312426','7187313202','7187314421','7187317164','7187317694','7187331410','7187331609','7187332806','7187333146',

'7187333536','7187333765','7187334323','7187334420','7187335228','7187337393','7187337642','7187338725','7187382564','7187382750','7187383647','7187384962',

'7187386413','7187387307','7187388430','7187389395','7187393233','7187397938','7187400244','7187400271','7187401751','7187422142','7187424409','7187426079',

'7187429032','7187429353','7187430854','7187431763','7187431944','7187432321','7187432587','7187432612','7187432646','7187433542','7187433646','7187434553',

'7187435268','7187435814','7187436289','7187436603','7187436874','7187437597','7187438465','7187438527','7187439834','7187450063','7187450844','7187451504',

'7187451728','7187451821','7187453029','7187453212','7187453629','7187453861','7187453964','7187454062','7187454287','7187454354','7187454674','7187455407',

'7187455579','7187456854','7187456970','7187457167','7187457320','7187457946','7187458410','7187460042','7187460871','7187460962','7187461205','7187461252',

'7187463048','7187463553','7187464128','7187464172','7187464384','7187465007','7187465669','7187465760','7187466080','7187468291','7187469464','7187473042',

'7187475607','7187476117','7187476139','7187480944','7187481208','7187482091','7187482464','7187483068','7187483147','7187483659','7187483796','7187483952',

'7187485133','7187486602','7187489896','7187520567','7187521815','7187529750','7187568064','7187590580','7187590925','7187591230','7187599425','7187599662',

'7187600238','7187600614','7187600733','7187600849','7187600873','7187601138','7187601334','7187601527','7187601549','7187601895','7187602377','7187602868',

'7187603285','7187603550','7187603876','7187604804','7187604845','7187605728','7187607406','7187607473','7187608939','7187613931','7187614612','7187615683',

'7187615717','7187616184','7187617711','7187618557','7187620539','7187622317','7187625542','7187628146','7187633771','7187635291','7187650203','7187650597',

'7187670343','7187670370','7187671309','7187671376','7187671516','7187672036','7187672363','7187672473','7187672773','7187672896','7187673849','7187674365',

'7187676549','7187679158','7187681055','7187682238','7187682254','7187687428','7187690619','7187690849','7187691545','7187692238','7187692502','7187693026',

'7187693125','7187693569','7187693707','7187693771','7187694311','7187694751','7187697063','7187697380','7187697880','7187698011','7187698379','7187698981',

'7187699471','7187714204','7187719081','7187730463','7187740128','7187762371','7187770041','7187770758','7187771640','7187771692','7187772414','7187772781',

'7187772868','7187773933','7187775504','7187775560','7187775724','7187775866','7187777576','7187780477','7187781954','7187790469','7187790618','7187790847',

'7187790865','7187791095','7187792267','7187792367','7187792948','7187793777','7187793872','7187794198','7187794582','7187794615','7187794960','7187795132',

'7187795222','7187795508','7187796043','7187796415','7187796844','7187797622','7187797681','7187797758','7187797835','7187798578','7187798794','7187798952',

'7187799293','7187820677','7187820716','7187820757','7187821174','7187821493','7187822103','7187822479','7187822492','7187822540','7187822702','7187823024',

'7187823204','7187823405','7187823581','7187823668','7187823989','7187824599','7187824778','7187825558','7187826351','7187826894','7187826983','7187827057',

'7187827815','7187827862','7187828313','7187829749','7187829884','7187830214','7187830643','7187838148','7187841041','7187842714','7187842877','7187860154',

'7187860348','7187861638','7187861863','7187861990','7187863444','7187864397','7187868894','7187870959','7187871492','7187880717','7187883321','7187884417',

'7187887402','7187889092','7187892980','7187895061','7187895240','7187895769','7187923459','7187924158','7187924204','7187926134','7187926760','7187929594',

'7187930707','7187933729','7187935623','7187935874','7187936547','7187939188','7187949210','7187960476','7187962195','7187968979','7187970769','7187971374',

'7187975362','7187983651','7187983994','7187984130','7187984495','7187989773','7188030981','7188031494','7188031811','7188032538','7188032905','7188033271',

'7188033606','7188033753','7188038130','7188050721','7188051083','7188051246','7188051305','7188051359','7188051875','7188054625','7188055706','7188057438',

'7188057519','7188150474','7188159197','7188161327','7188165502','7188167467','7188169894','7188180836','7188200638','7188211316','7188211496','7188211845',

'7188211878','7188211913','7188212220','7188212592','7188213779','7188214285','7188214610','7188214615','7188215109','7188215236','7188215646','7188216180',

'7188216483','7188216485','7188218034','7188218915','7188219076','7188220092','7188222805','7188225839','7188226323','7188230537','7188234855','7188236617',

'7188240437','7188241859','7188245305','7188260628','7188262346','7188263856','7188270971','7188271302','7188272895','7188273061','7188273870','7188274792',

'7188275066','7188275605','7188275716','7188276649','7188277330','7188277350','7188278145','7188278293','7188278323','7188278492','7188280950','7188285516',

'7188285973','7188288197','7188292735','7188293942','7188297135','7188298399','7188299337','7188300715','7188320402','7188321706','7188321916','7188321926',

'7188322075','7188322262','7188323006','7188323319','7188323354','7188324683','7188324870','7188325182','7188326340','7188329048','7188331311','7188331714',

'7188331730','7188331742','7188332505','7188333556','7188333610','7188333736','7188334762','7188336049','7188336374','7188338980','7188346173','7188351089',

'7188352162','7188352666','7188353017','7188354168','7188360337','7188360435','7188360917','7188361221','7188361278','7188361587','7188364811','7188367087',

'7188367642','7188368733','7188369328','7188369418','7188370515','7188371222','7188371429','7188371963','7188372318','7188372461','7188373207','7188374629',

'7188375153','7188375708','7188375788','7188378334','7188378631','7188378678','7188379737','7188379850','7188420088','7188420108','7188420487','7188421012',

'7188421940','7188422392','7188423238','7188423841','7188424418','7188425904','7188426274','7188427455','7188428037','7188428341','7188433641','7188433675',

'7188435308','7188435869','7188436064','7188436720','7188439150','7188451248','7188452660','7188452896','7188453751','7188455377','7188457478','7188458214',

'7188460012','7188460153','7188460335','7188460478','7188460793','7188462324','7188462862','7188463861','7188463905','7188464068','7188464123','7188464589',

'7188464839','7188464912','7188464983','7188465257','7188465355','7188465383','7188465390','7188465479','7188467469','7188467561','7188467943','7188470216',

'7188470403','7188470427','7188471608','7188472683','7188472734','7188472842','7188473326','7188474837','7188475347','7188477441','7188478426','7188478513',

'7188478664','7188478881','7188479756','7188479790','7188479921','7188479966','7188480420','7188481534','7188483836','7188491672','7188491721','7188492080',

'7188492801','7188492812','7188492904','7188494752','7188495512','7188497501','7188499599','7188500283','7188501571','7188502036','7188502046','7188502162','7188502915','7188503409','7188503490','7188506568','7188507608','7188508397','7188510494','7188512384','7188512517','7188512752','7188512775','7188513642','7188513659','7188513966','7188514081','7188514201','7188514567','7188514652','7188514756','7188516488','7188517267','7188520478','7188522314','7188523971','7188524628','7188525619','7188526316','7188527171','7188527903','7188528148','7188530195','7188530207','7188530519','7188531095','7188531825','7188532481','7188533620','7188533809','7188534256','7188534346','7188535269','7188535693','7188535846','7188536243','7188536264','7188536374','7188536626','7188536787','7188537175','7188537215','7188538059','7188538090','7188538206','7188538298','7188538749','7188538787','7188538804','7188538870','7188539238','7188539773','7188540693','7188540809','7188540910','7188540972','7188541032','7188541275','7188541328','7188541434','7188542399','7188543016','7188543290','7188543545','7188543699','7188543961','7188544302','7188544428','7188544567','7188544745','7188546417','7188547687','7188548831','7188549408','7188552037','7188555438','7188560453','7188561870','7188563263','7188566710','7188568218','7188568343','7188568547','7188571368','7188590157','7188592193','7188592547','7188592974','7188593932','7188595748','7188597452','7188597627','7188599351','7188599774','7188600033','7188600721','7188600815','7188601038','7188601292','7188604393','7188604940','7188606172','7188606349','7188606724','7188606919','7188611747','7188611982','7188613718','7188614261','7188618091','7188618488','7188618711','7188619607','7188630365','7188630429','7188630857','7188632954','7188635415','7188636764','7188637062','7188637288','7188637596','7188637637','7188638440','7188639551','7188680267','7188710727','7188710817','7188711820','7188712965','7188713314','7188713874','7188716101','7188716296','7188717109','7188717438','7188718310','7188753424','7188754140','7188757634','7188760654','7188765210','7188765337','7188810912','7188813925','7188818175','7188824340','7188826326','7188841344','7188842157','7188845525','7188860274','7188860707','7188861957','7188862130','7188862730','7188863048','7188865992','7188869528','7188880096','7188889015','7188911470','7188911724','7188912513','7188913924','7188914378','7188914489','7188914526','7188915048','7188915266','7188916510','7188917352','7188918005','7188918473','7188918491','7188919282','7188919420','7188919452','7188920591','7188922806','7188922867','7188925345','7188929381','7188930361','7188930765','7188930769','7188930781','7188931739','7188934587','7188936794','7188936844','7188936920','7188937595','7188937987','7188939204','7188940023','7188940245','7188940583','7188941210','7188942021','7188942576','7188942739','7188944359','7188944923','7188945419','7188945542','7188947098','7188962071','7188964074','7188965090','7188965674','7188968370','7188971265','7188973159','7188973325','7188973396','7188973472','7188973791','7188973798','7188974660','7188974948','7188976532','7188979451','7188980374','7188980469','7188980983','7188981086','7188981279','7188982197','7188982843','7188983040','7188983432','7188983705','7188983752','7188985279','7188985767','7188986009','7188986040','7188986610','7188987079','7188987865','7188988085','7188988198','7188988573','7188988864','7188989665','7188990103','7188990119','7188990156','7188990751','7188991615','7188992085','7188993414','7188994676','7188995413','7188996403','7188997089','7188997463','7188998036','7188998062','7188999089','7188999457','7188999616','7189010817','7189010869','7189010950','7189011842','7189012184','7189013604','7189015201','7189015670','7189040568','7189180251','7189190849','7189191573','7189191811','7189192171','7189192762','7189193202','7189193619','7189195840','7189198817','7189211632','7189213149','7189215906','7189216319','7189221817','7189311472','7189312569','7189314304','7189314578','7189314783','7189315109','7189320273','7189320839','7189321812','7189321813','7189321823','7189323181','7189323583','7189323947','7189324076','7189324711','7189325322','7189325424','7189325629','7189327421','7189329017','7189330115','7189330291','7189333740','7189336140','7189336406','7189337682','7189338394','7189339274','7189339377','7189340348','7189340996','7189341326','7189341345','7189341549','7189342218','7189342516','7189342833','7189342979','7189343378','7189344788','7189346184','7189346884','7189347947','7189348049','7189348614','7189371547','7189371984','7189372573','7189374692','7189376179','7189390029','7189390987','7189391505','7189393244','7189393318','7189395509','7189395651','7189396250','7189396727','7189400327','7189400688','7189401746','7189402512','7189404481','7189404650','7189412860','7189414356','7189414503','7189417093','7189417241','7189422285','7189422525','7189422650','7189430175','7189430196','7189430199','7189430288','7189430314','7189430371','7189430372','7189435996','7189436410','7189437530','7189450538','7189451102','7189451257','7189451832','7189453608','7189453710','7189455023','7189455172','7189455865','7189456189','7189457131','7189457501','7189457510','7189459661','7189459708','7189460560','7189461476','7189462124','7189462189','7189462805','7189463256','7189463486','7189463656','7189464256','7189464494','7189464791','7189466104','7189467038','7189467307','7189468229','7189470546','7189470582','7189470585','7189470597','7189470605','7189470607','7189470613','7189470615','7189470628','7189470642','7189470644','7189470658','7189470669','7189470675','7189470682','7189470698','7189470710','7189470721','7189470727','7189470728','7189470730','7189470732','7189470733','7189470749','7189470751','7189470752','7189470757','7189470758','7189470760','7189470762','7189470767','7189470780','7189470783','7189470786','7189470794','7189470796','7189470797','7189470807','7189470826','7189470828','7189470830','7189470834','7189470838','7189470840','7189470841','7189470843','7189470845','7189470873','7189470876','7189470884','7189470887','7189470889','7189470907','7189470916','7189470925','7189470926','7189470928','7189470932','7189470936','7189470939','7189470957','7189470963','7189470964','7189470969','7189471052','7189471057','7189471058','7189471059','7189471082','7189471098','7189471105','7189471138','7189471145','7189471151','7189471154','7189471244','7189471251','7189471255','7189471261','7189471262','7189471275','7189471278','7189471282','7189471292','7189471328','7189471362','7189471363','7189471364','7189471365','7189471386','7189471392','7189471393','7189471422','7189471433','7189471461','7189471464','7189471491','7189471494','7189471570','7189471606','7189471765','7189485548','7189486706','7189492489','7189494656','7189511069','7189512276','7189512277','7189517838','7189518087','7189518760','7189518884','7189518984','7189519303','7189519836','7189530975','7189560093','7189560231','7189560238','7189560320','7189560503','7189561005','7189561047','7189561048','7189561272','7189561348','7189561556','7189561794','7189562168','7189563981','7189564479','7189564486','7189564528','7189565627','7189565739','7189565788','7189565843','7189566347','7189566542','7189567186','7189567558','7189568321','7189568330','7189568564','7189569490','7189610502','7189612956','7189619150','7189630168','7189630788','7189631570','7189632013','7189632074','7189632976','7189633192','7189633751','7189633822','7189634004','7189639794','7189650315','7189651103','7189651884','7189652083','7189661111','7189662418','7189665849','7189667528','7189687057','7189688964','7189689587','7189690185','7189690885','7189692709','7189693115','7189693601','7189699311','7189721139','7189721219','7189721390','7189721450','7189721459','7189722897','7189722954','7189722960','7189723002','7189723146','7189723182','7189723779','7189723819','7189723829','7189724498','7189724815','7189725504','7189726549','7189727009','7189727643','7189728304','7189792450','7189793350','7189794053','7189794743','7189798380','7189800198','7189800622','7189800792','7189805913','7189811154','7189812789','7189813418','7189815888','7189820166','7189825750','7189827859','7189828035','7189829217','7189831741','7189838329','7189838380','7189841062','7189842845','7189846914','7189848552','7189870577','7189874293','7189875789','7189876222','7189879213','7189879681','7189911639','7189912579','7189913073','7189913094','7189913873','7189915321','7189915984','7189916941','7189918551','7189919151','7189920641','7189920735','7189921121','7189921509','7189921734','7189921834','7189922143','7189923580','7189924081','7189925319','7189925891','7189925905','7189928280','7189929503','7189930243','7189930975','7189933109','7189934464','7189934547','7189934572','7189935734','7189937486','7189944713','7189946524','7189960318','7189960545','7189960549','7189960625','7189960729','7189961235','7189961586','7189962444','7189962713','7189963977','7189964152','7189964676','7189965292','7189965389','7189966035','7189966784','7189967140','7189968101','7189968183','7189968213','7189968741','7189970170','7189971087','7189976952','7189977314','7189979058','7189979624','7189981124','7189982008','7189982855','7189982959','7189986214','7189988389','7189988460','8452259089','8452468943','8452791399','8452925045','8453521785','8453522864','8453523813','8453524211','8453524484','8453527924','8453528034','8453528172','8453562773','8453565029','8453567444','8453580273','8453580760','8453585911','8453712440','8453712620','8453716119','8454250613','8454251421','8454254079','8454254784','8454254854','8454261269','8454262599','8454266457','8454267459','8454291796','8454340773','8454341805','8454344789','8454542436','8454625953','8454837658','8455284094','8455343415','8455616294','8455651283','8455666970','8455690205','8455697032','8455730144','8456213863','8456265009','8456265515','8456271377','8456473742','8456474340','8457271173','8457277745','8457532472','8457866080','8457867348','8457886780','8457916472','8457918483','8457940405','8458964868','8459477094','9142321846','9142351023','9142355342','9142356043','9142378590','9142411651','9142412108','9142412336','9142412908','9142414382','9142421759','9142500167','9142500271','9142500272','9142713418','9142767666','9142767667','9143321390','9143324572','9143324785','9143328172','9143371090','9143472664','9143750640','9143750681','9143752606','9143752878','9143752980','9143755930','9143756064','9143759018','9143761319','9143761966','9143763954','9143765804','9143765899','9143767153','9143769526','9143789171','9143817430','9144222671','9144233363','9144233917','9144234068','9144234593','9144234721','9144234761','9144237450','9144237869','9144725032','9144725297','9144763768','9144764828','9144769317','9144769580','9144769844','9144780514','9144783532','9144785083','9144787557','9144791298','9145240559','9145280867','9145286518','9145762553','9145763182','9145763890','9145763975','9145764004','9145765023','9145765828','9145768225','9145769186','9145919586','9146311708','9146322303','9146324887','9146328092','9146329447','9146331515','9146333927','9146360297','9146541848','9146632612','9146643301','9146650671','9146657046','9146678126','9146684242','9146684762','9146830825','9146841842','9146847041','9146869786','9146901033','9146909817','9146931130','9146931542','9146982426','9146987134','9147090115','9147120857','9147349202','9147362948','9147367630','9147369786','9147371431','9147378154','9147379064','9147382217','9147388319','9147391652','9147398481','9147472024','9147473258','9147477573','9147612357','9147620328','9147623299','9147631377','9147632100','9147693136','9147773590','9147885645','9148332108','9149210097','9149229331','9149237593','9149238364','9149340694','9149342309','9149349245','9149375812','9149379796','9149390280','9149391344','9149399174','9149399237','9149417746','9149447059','9149464715','9149485639','9149492004','9149493137','9149615666','9149629652','9149632051','9149636257','9149650453','9149650712','9149652521','9149653445','9149663621','9149680803','9149682051','9149682130','9149682392','9149684868','9149687023','9149690418','9149690661','9149693021','9149693852','9149695716','9173268901','9174922134','9174929446','9175211440','9175212118','9175212147','9175217228','9175217252','NEW1007818','NEW1009805','NEW1009942','NEW1009946','NEW1010040','NEW1010090') or ordUidCustomer = 277173336)

I have seen similar performance problems in 2005 when using huge lists of IN values in the where clause. I am not able to change the SQL or the length of the list because it is generated by a report writer and I have no control over what the user selects.

Try 2005 SP2 (on a test machine) and see if that helps.
|||

Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.

Dan

|||SQL2005 do have performance problem for IN and NOT IN. Here is another thread talking about this issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

You can use left join or right join to implement same behavior with IN and NOT IN. That will improve your query performance.
|||

>>Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.<<

This is what I was thinking. I have a query like this but it passes it as a delimited an I run a split function on it like this:

Code Snippet

declare @.listIds varchar(max)
set @.listIds = '1,2,3,4,5,6,7,8,9'

;with digits as(
select 1 as i union all select 2 as i union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 0),

sequence as (
SELECT distinct D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i) + (100000*D6.i) as i
FROM digits AS D1, digits AS D2,
digits AS D3,digits as D4, digits as D5, digits as D6
),
list as (
SELECT substring(',' + @.listIds + ',', i + 1,
charindex(',', ',' + @.listIds + ',', i + 1) - i - 1) AS responseActivityWorkId
FROM sequence
WHERE i <= len(',' + @.listIds + ',') - 1
AND substring(',' + @.listIds + ',', i, 1) = ',')
select *
from list

The select * from list would just be replaced by your query with a join to this list CTE, and remove the IN (big old list). Then format the list as 'value,value,value' and go for it. I support 999999 as the max length of the parameter, but just adding a D7 (following the pattern) and you could go even higher. (I would like to do your data, but the rotation around the screen would take me a long time to reformat

If you have CTE performance issues, just put the results of the list query into a temp table, add an index and try that as a join or IN clause. Then it should be as fast as you can get then.

If you would like to read more about the subject of arrays and lists in SQL Server, please try the excellent article: http://www.sommarskog.se/arrays-in-sql-2005.html

|||

they got around this by using temp tables. i tried this on some new 64 bit servers we just bought with 20GB RAM and same result. 100% CPU spike but execution time is around 30 seconds instead of 10 minutes

Quick Question - Need some advice on troubleshooting SSIS Errors

Hi Everyone:

I have a SSIS Package which in brief moves data from one SQL data store to another. On my local machine, the package executes fines, and does what it is supposed to do, by moving data from Point A to Point B. I have both Point A(DB) and Point B(DB) on my local machine. But when I deploy this package over to ASSEMBLY which is basically another environment, I get weird intermittent errors. What should I do? Can someone give me some tips on how to do good error logging in SSIS? I am currently writing to Windows Event Log, and I seem to look at some errors that are coming thru. What else can I do on SSIS Package side, to improve error reporting and handling, so troubleshooting is faster and more effective. I just need some stable advice on how to setup my package to do error logging where troubleshooting issues in different environments is more effective. Also I need to know, what could be causing these issues? Thanks.

MA

Try turning on package logging. Set it to log to SQL Server. It will then be in the chosen databases' sysdtslog90 table.

Right click on the control flow background to get at package logging.|||

There are plenty of resources out there....

SSIS Logging

http://www.databasejournal.com/features/mssql/article.php/3562406

http://msdn2.microsoft.com/en-us/library/ms167456.aspx

http://www.microsoft.com/downloads/details.aspx?FamilyID=526e1fce-7ad5-4a54-b62c-13ffcd114a73&DisplayLang=en

Custom Logging:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

|||

Do i have to specify this table in the configuration column when setting up the log provider to write to SQL Server tables? Where do i find sysdtslog90 table? Thanks.

|||

MA2005 wrote:

Do i have to specify this table in the configuration column when setting up the log provider to write to SQL Server tables? Where do i find sysdtslog90 table? Thanks.

When you set up logging, you have to chose a database/connection. In that database you'll find the sysdtslog90 table.|||

Here is the error I keep getting, which I am not getting in my Local Machine Environment. Hmmmm... any ideas?

Executing the query "exec dbo.uspSaveMain_CEAImport ?, ?, ? OUTPUT" failed with the following error: "Grantor does not have GRANT permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set

correctly, or connection not established correctly.

|||It looks like your user lacks some DB permission in the env where you are pointing to.