q

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

Pull Merge constantly have all the same conflicts

Hello.

Let me describe first my replication setup:

- SQL Server 2005 SP1 (SP2 coming soon)

- Approximately 35 remote users (Salesrep laptop) using Pull Subscriptions

- Merge (Bi-Directional) (8 articles - tables only)

- Merge (Uni-Directional) (5 articles - tables only)

- Transactional (5 articles - tables only)

Users receive data based on their territory #, therefore they receive their customers sets of data. It happens that customer change from one territory to another but not frequently. When it happens, so far so good, the data is redirected to the new salesrep using the model we configured (Territory table with SUSER_NAME() to filter the data).

Ok, here's my problem. Since a while, I can see in the replication monitor that some users seems to log the same conflict again and again (Merge process). I mean, checking the history for many subscribers, there is always the same number in the "Conflict" colums.

As an example:

- Merge completed after processing 18 data change(s) (4 insert(s), 14 update(s), 0 delete(s), 31 conflict(s))

- Merge completed after processing 27 data change(s) (10 insert(s), 17 update(s), 0 delete(s), 31 conflict(s))

- Merge completed after processing 20 data change(s) (5 insert(s), 15 update(s), 0 delete(s), 31 conflict(s))

and so on...(Those are only 3 historical entries for a single subscriptions but there are many like that, always with the same count of conflict - vary per user). It appears to me that the same conflicts come over and over.

The thing is that if I decide to reinitialize a subscription, conflicts will disappear, therefore I know that it is not a process on the server that keeps changing the data; anyway, even if it was, changes would be applied on the subscription because the server always win in my setup.

Any idea what should I do with this? Any help would be greatly appreciated.

Thanks.

Can you use the conflict viewer to see what these conflicts are to see if they make sense to you?|||

Woops, I feel like an idiot now

Thanks for opening my mind!

Publisher SP1 Subscriber SP2 ?

I have a clustering between Server A and Server B. There is a clustered SQL Server installed.

Server A is a primary node(oltp production server) and Server B is a secondary node.

i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.

Currently both servers have sp1.

Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)

So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?

Any help would be appriciated.

Thanks

No, the correct order it to update the distributor first, then the publisher and finally the subscibers. It sounds like you are doing things correctly.

I am a little confused by your clustering topology - I take it you have a multi-instance (active active) topology?|||
Thanks so much for the reply.

Actually i have active passive clustering. Server A is primary and Server B is secondary. If something goes wrong with A, it fails over to B but not a vice-versa. Server A is my production oltp server.

On server B i also installed a named instance (standalone instance not the clustered instance) which i am using for all the reporting purposes.

For replication, I am replicating server A database articles to the named instance on Server B. And this named instance is also acting as its distributor.

So

Server A (Default Instance & Clustered):- Publisher

Server B\Instance Name (standalone instance): Subscriber as well as the distributor

So if I apply SP2 on the named instance of Server B, i will be upgrading distributor as well as Subscriber (both at the same time.) And after some days i am planning to apply SP2 on the cluster which will upgrade my production oltp in the cluster.

I hope this will clear your doubts.

Please let me know if there are any issues with what i am doing as i explained above.

Thanks

|||I am unaware with any issues surrounding this. It should work.|||Thank you so much.

Publisher SP1 Subscriber SP2 ?

I have a clustering between Server A and Server B. There is a clustered SQL Server installed.

Server A is a primary node(oltp production server) and Server B is a secondary node.

i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.

Currently both servers have sp1.

Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)

So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?

Any help would be appriciated.

Thanks

No, the correct order it to update the distributor first, then the publisher and finally the subscibers. It sounds like you are doing things correctly.

I am a little confused by your clustering topology - I take it you have a multi-instance (active active) topology?|||
Thanks so much for the reply.

Actually i have active passive clustering. Server A is primary and Server B is secondary. If something goes wrong with A, it fails over to B but not a vice-versa. Server A is my production oltp server.

On server B i also installed a named instance (standalone instance not the clustered instance) which i am using for all the reporting purposes.

For replication, I am replicating server A database articles to the named instance on Server B. And this named instance is also acting as its distributor.

So

Server A (Default Instance & Clustered):- Publisher

Server B\Instance Name (standalone instance): Subscriber as well as the distributor

So if I apply SP2 on the named instance of Server B, i will be upgrading distributor as well as Subscriber (both at the same time.) And after some days i am planning to apply SP2 on the cluster which will upgrade my production oltp in the cluster.

I hope this will clear your doubts.

Please let me know if there are any issues with what i am doing as i explained above.

Thanks

|||I am unaware with any issues surrounding this. It should work.|||Thank you so much.

Publisher Issues

Right i have two sql 2005 standard edition boxes within a Windows 2003 R2 active directory environment. All updates sp1 etc.

I have an mssql service account setup in AD which all the mssql services on both servers startup with.
I went to setup replication between the two servers for one db.
Firstly i setup distribution no problems no errors.

Then i went to setup the publication, all went well no errors in management studio gui but in the application event logs i got the following:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 14151
Date: 05/10/2006
Time: 12:37:43
User: DNETWORK\mssqlservice
Computer: SGC
Description:
Replication-Replication Transaction-Log Reader Subsystem: agent SGC-MerakDB-2 failed. Executed as user: dnetwork\sqlrep. A required privilege is not held by the client. The step failed.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 47 37 00 00 12 00 00 00 G7......
0008: 04 00 00 00 53 00 47 00 ....S.G.
0010: 43 00 00 00 0d 00 00 00 C.......
0018: 64 00 69 00 73 00 74 00 d.i.s.t.
0020: 72 00 69 00 62 00 75 00 r.i.b.u.
0028: 74 00 69 00 6f 00 6e 00 t.i.o.n.
0030: 00 00

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 14151
Date: 05/10/2006
Time: 12:37:51
User: DNETWORK\mssqlservice
Computer: SGC
Description:
Replication-Replication Snapshot Subsystem: agent SGC-MerakDB-Merak Mail DB-2 failed. Executed as user: dnetwork\sqlrep. A required privilege is not held by the client. The step failed.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 47 37 00 00 12 00 00 00 G7......
0008: 04 00 00 00 53 00 47 00 ....S.G.
0010: 43 00 00 00 0d 00 00 00 C.......
0018: 64 00 69 00 73 00 74 00 d.i.s.t.
0020: 72 00 69 00 62 00 75 00 r.i.b.u.
0028: 74 00 69 00 6f 00 6e 00 t.i.o.n.
0030: 00 00

I've tried all sorts to get this to work, the sqlrep user is db_owner for the distro db, ive tried the sqlrep user as domain admin to see if it was a system priv issue, no luck :(

Anyone have any ideas?

Is the account dnetwork\sqlrep the same as SQL Server service account? You can try to add dnetwork\sqlrep to windows user group SQLServer2005SQLAgentUser$<MachineName>$MSSQLSERVER, SQLServer2005MSSQLUser$<MachineName>$MSSQLSERVER, then restart SQL Server service, or reboot the machine to see if the problem goes away. Also can you let us know if you have changed SQL Server service account after installation? If so, it has to be done through SQL Server configuration manager.

Thanks,

Zhiqiang Feng

|||

I tried what you suggested, no luck,

theres a service account for mssql which i named mssqlservice :)
That was set during setup and never changed.

sqlrep was created for replication only, i had already added it to the groups you suggested with no luck. The same error etc.

So i uninstalled then reinstalled mssql from scratch no problems this time.

I believe it was down to a active directory issue when we had to run gpofix it may have altered something down the line. The issue was effecting both servers so i assume it was caused by the active directory issue.

Bit strange though but its sorted now, well so far so good :)

PSSDIAG

Is there a problem with PSSDiag on a Windows 2003 SP1 server running SQL
Server 2000 SP4 ? It seems to hang.
The tool works perfectly on a Windows XP SP1 with SQL Server 2000 SP4.
I wouldn't use that tool during business hours. I did and it crashed my
server, desipte MS assuring that it would be fine.
"Sylvain Nadeau" wrote:

> Is there a problem with PSSDiag on a Windows 2003 SP1 server running SQL
> Server 2000 SP4 ? It seems to hang.
> The tool works perfectly on a Windows XP SP1 with SQL Server 2000 SP4.

PSSDIAG

I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
The collectif of the trace seems to go well.
When I stop the trace the programm seems to hang after the last message that
says that it's exiting.
I dont't have the problem on a Windows XP SP1.
Is there an issue for windows 2003 sp1 ?
Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
diagnostic ?
1- Do not install PSSDiag in c:\program files or any other directory that
have blank in its name
2- There are two requirements for the PSSDIAG service startup account: 1) it
must be a member of the local administrators group (though not necessarily
the local Administrator), and 2) it must be a member of the SQL Server
sysadmin server role. The default PSSDIAG service startup account is
LocalSystem, and by default it satisfies both of these requirements.
However, if you have removed the local Administrators group from the SQL
sysadmin role, you will need to choose a new PSSDIAG service startup account
that is both an NT administrator and a SQL sysadmin.
3- The .trc file is actually written by the SQL Server process. For this
reason, the SQL Server service startup account must be able to write to the
output directory.
"Sylvain Nadeau" wrote:

> I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
> The collectif of the trace seems to go well.
> When I stop the trace the programm seems to hang after the last message that
> says that it's exiting.
> I dont't have the problem on a Windows XP SP1.
> Is there an issue for windows 2003 sp1 ?
> Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
> diagnostic ?

PSSDIAG

I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
The collectif of the trace seems to go well.
When I stop the trace the programm seems to hang after the last message that
says that it's exiting.
I dont't have the problem on a Windows XP SP1.
Is there an issue for windows 2003 sp1 ?
Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
diagnostic ?1- Do not install PSSDiag in c:\program files or any other directory that
have blank in its name
2- There are two requirements for the PSSDIAG service startup account: 1) it
must be a member of the local administrators group (though not necessarily
the local Administrator), and 2) it must be a member of the SQL Server
sysadmin server role. The default PSSDIAG service startup account is
LocalSystem, and by default it satisfies both of these requirements.
However, if you have removed the local Administrators group from the SQL
sysadmin role, you will need to choose a new PSSDIAG service startup account
that is both an NT administrator and a SQL sysadmin.
3- The .trc file is actually written by the SQL Server process. For this
reason, the SQL Server service startup account must be able to write to the
output directory.
"Sylvain Nadeau" wrote:

> I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4
.
> The collectif of the trace seems to go well.
> When I stop the trace the programm seems to hang after the last message th
at
> says that it's exiting.
> I dont't have the problem on a Windows XP SP1.
> Is there an issue for windows 2003 sp1 ?
> Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
> diagnostic ?

PSSDIAG

Is there a problem with PSSDiag on a Windows 2003 SP1 server running SQL
Server 2000 SP4 ? It seems to hang.
The tool works perfectly on a Windows XP SP1 with SQL Server 2000 SP4.I wouldn't use that tool during business hours. I did and it crashed my
server, desipte MS assuring that it would be fine.
"Sylvain Nadeau" wrote:

> Is there a problem with PSSDiag on a Windows 2003 SP1 server running SQL
> Server 2000 SP4 ? It seems to hang.
> The tool works perfectly on a Windows XP SP1 with SQL Server 2000 SP4.

PSSDIAG

I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
The collectif of the trace seems to go well.
When I stop the trace the programm seems to hang after the last message that
says that it's exiting.
I dont't have the problem on a Windows XP SP1.
Is there an issue for windows 2003 sp1 ?
Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
diagnostic ?1- Do not install PSSDiag in c:\program files or any other directory that
have blank in its name
2- There are two requirements for the PSSDIAG service startup account: 1) it
must be a member of the local administrators group (though not necessarily
the local Administrator), and 2) it must be a member of the SQL Server
sysadmin server role. The default PSSDIAG service startup account is
LocalSystem, and by default it satisfies both of these requirements.
However, if you have removed the local Administrators group from the SQL
sysadmin role, you will need to choose a new PSSDIAG service startup account
that is both an NT administrator and a SQL sysadmin.
3- The .trc file is actually written by the SQL Server process. For this
reason, the SQL Server service startup account must be able to write to the
output directory.
"Sylvain Nadeau" wrote:
> I try to run PSSDIAG on a Windows 2003 sp1 box running SQL Server 2000 SP4.
> The collectif of the trace seems to go well.
> When I stop the trace the programm seems to hang after the last message that
> says that it's exiting.
> I dont't have the problem on a Windows XP SP1.
> Is there an issue for windows 2003 sp1 ?
> Also SQLDiag doesn't run with SQL 2000 SP4, is there another tool for a
> diagnostic ?

PSSDIAG

Is there a problem with PSSDiag on a Windows 2003 SP1 server running SQL
Server 2000 SP4 ? It seems to hang.
The tool works perfectly on a Windows XP SP1 with SQL Server 2000 SP4.I wouldn't use that tool during business hours. I did and it crashed my
server, desipte MS assuring that it would be fine.
"Sylvain Nadeau" wrote:
> Is there a problem with PSSDiag on a Windows 2003 SP1 server running SQL
> Server 2000 SP4 ? It seems to hang.
> The tool works perfectly on a Windows XP SP1 with SQL Server 2000 SP4.